Category: LINQ

Get one list of Task return objects

Using the Task Async/Await pattern for grabbing data can be a real performance enhancement. When you thread off the calls, it’s pretty normal to want Task return objects to be in one single usable collection. The example I can give is a method that needs to gather up several different categories of lookup items. These calls all return a collection of the same type.

When you await the tasks, you generally have a few options:

Await each item individually

            List<Task<List<LuItem>>> _allLus = new();
            List<LuItem> _return = new();

            _allLus.Add(LookupsSvc.GetLuItemsByCatShortNameAsync("URLTYPES"));
            _allLus.Add(LookupsSvc.GetLuItemsByCatShortNameAsync("RVFUELTYPES"));
            _allLus.Add(LookupsSvc.GetLuItemsByCatShortNameAsync("GENERATORFUELTYPES"));

            Task<List<LuItem>>.WaitAll(_allLus.ToArray());

            List<LuItem> _task1 = await _allLus[0];
            List<LuItem> _task2 = await _allLus[1];
            List<LuItem> _task3 = await _allLus[2];

            _return.AddRange(_task1);
            _return.AddRange(_task2);
            _return.AddRange(_task3);

            return _return;

Not sure how you feel, but this is horrible. I’m sure I’ve done something like this in the past, but I’d prefer not to think about it.

Use WhenAll to retrieve them in an Array

The Task.WhenAll, when declared with a type, will return an array of the return type. So in this case, it would return an Array of List<LuItem>. We can then do a simple LINQ query to push them all into one collection.

            List<Task<List<LuItem>>> _allLus = new();
            List<LuItem> _return = new();

            _allLus.Add(LookupsSvc.GetLuItemsByCatShortNameAsync("URLTYPES"));
            _allLus.Add(LookupsSvc.GetLuItemsByCatShortNameAsync("RVFUELTYPES"));
            _allLus.Add(LookupsSvc.GetLuItemsByCatShortNameAsync("GENERATORFUELTYPES"));

            List<LuItem>[] _await = await Task<List<LuItem>>.WhenAll(_allLus);
            _await.ToList().ForEach(lus => _return.AddRange(lus));

            return _return;

In this example, we await the Task with WhenAll, which has a return type, as opposed to WaitAll which does not. As stated earlier, this example will return a collection as Task<List<LuItem>[]>. So we’re most of the way there. We use the ToList().ForEach LINQ query to transform the Array of Lists into a single list called _return.\

Sum of a list of values in a collection

Summing a collection that is within a collection without using nested foreach loops can be easily done with LINQ

It’s hard to think of a good name for this post. But if you have a collection and each item has a collection of values that you need to get a sum on, you can do that easily with LINQ.

Say you have a List<CartItem> in a shopping cart. Each item has a list of DecimalCost, possibly the user has ordered different sizes or colors and they each have an associated cost.

decimal _sum;
_return.CartItems.ForEach(c => _sum = c.DecimalCost.Sum());

Above we’re basically setting up an inline ForEach loop and then summing on the DecimalCost field which is actually a List<decimal>.

LINQ Safely Remove Items from a Collection

I’m sure we’ve all experienced the great idea of looping through a collection and trying to remove an item from a collection that doesn’t need to be there. You’ll get the infamous “Collection was modified; enumeration operation may not execute”. You can create a new collection and add the ones you want to that one, but that’s extra overhead.

Collection was modified; enumeration operation may not execute

This is a method that you can use that is outside of a foreach loop:

entity.TheBody.Elements.RemoveAll(a => string.IsNullOrWhiteSpace(a.Key));

In this example, I have an email named “entity” with a “TheBody” property that has a collection of Elements. The Elements have two properties, “Key” and “Value”, basically like a Dictionary entry. Creating a new list of elements means a new List<EmailElement> and then a .Clear and .AddRange, which kills more CPU cycles and milliseconds.

However, executing the above line will remove all the items from the collection that meet the criteria in the lambda.

Update Property Values in Collection using LINQ

There are many times that I wanted to be able to quickly update the property values in a collection without needing to create a foreach loop. Sometimes it’s because I needed to do it within a larger query, other times, just because it’s a relatively simple update and like being able to do it in one line of code.

Take for instance this example. I have a list of objects and I want to add a counter value to each. I’m doing this because they collection is sorted, but later processing is threaded, so they come out of that method unsorted again. I wanted a way to quickly get them sorted again so I didn’t have to pass around the sortColumn and sortOrder properties.

List<Contacts> _contacts = GetContactsPagedAsync(1, 25, "LastName", "desc");

int _counter = 1;
_contacts.Select(c => { c.SortOrdinal = _counter++; return c; });

The above code gets the collection and then updates the SortOrdinal property with a counter value.

You can get a little more complex pretty easily, take this:

List<Contacts> _contacts = GetContactsPagedAsync(1, 25);

_contacts.Select(c => { c.FullName = c.FirstName + " " + c.LastName ; return c; });

You can easily call a method from within your code as well, but just keep in mind that this runs synchronously. If the method is simple, we could rewrite the above like:

List<Contacts> _contacts = await GetContactsPagedAsync(1, 25);

_contacts.Select(c => { c.FullName = GetFullName(c); return c; });

Happy LINQing!

Get Records in one table with a Foreign Key to a related one to many table

There are times when you need to get records in one table with a foreign key to a related one to many table. This is a difficult need to describe, so I’ll give you the exact business scenario.

I have designed and used a Process Tracking system for many years. It currently has two basic components in the database:

  1. A FileProcess table that tracks a file (name, date, paths, app that processed it, etc.)
  2. A StatusLog table that I punch in records as this file goes through the process of being imported, validated, etc.

Often, I have multiple applications that process a batch of records from a file. I designed a stored procedure that would allow me to check for any file, by a particular application, that was in a particular status, but not past that status.

So here’s the scenario, we have a process that I have assigned the following status log values:

10 – File Parsed
20 – File Imported
30 – Data Validated
40 – Data Archived

Ok, so one application parses the file and imports it, let’s say it’s an SQL SSIS package just for fun. So it punches two status records in while it’s working, a 10 and a 20.

So now I have another validation application that checks every few minutes for something to do. I want it to be able to find any file that is in a status of 20, but NOT higher than that. So then I know it’s ready to be validated.

In order to do this, I have the following LINQ to SQL query that seems to do the job for me. I hope looking at this code will help you with whatever similar type of issue you’re trying to solve:

public async Task<List<FileProcess>> GetFileProcessesForAStatusByAppIdAsync(int AppId, int StatusId)
        {
            try
            {
                var _entityrows = (from st in _appLogContext.StatusLogs
                                   join fp in _appLogContext.FileProcess.Include(a => a.App) on st.FileProcessId equals fp.Id
                                   where st.AppId == AppId
                                    && st.StatusId == StatusId
                                    && st.StatusId == (_appLogContext.StatusLogs.Where(f => f.FileProcessId == fp.Id).OrderByDescending(p => p.StatusId).FirstOrDefault().StatusId)
                                   select fp).AsNoTracking();


                return await _entityrows.ToListAsync();

            }
            catch (Exception)
            {
                throw;
            }
        }

For those of you that are database jockeys, here’s the SQL code that this replaces:

     @AppId AS INT = NULL,
     @StatusId AS INT = NULL

    SELECT 
        [Id],
        [AppId],
        [FileName],
        [DateProcessed],
        [Inbound]
    FROM
        [FileProcess]
    WHERE
        Id IN (
    SELECT
        s.FileProcessId
    FROM
        (SELECT DISTINCT MAX(StatusId) 
            OVER(PARTITION BY FileProcessId) 
            AS ProperRow, FileProcessId, AppId
            FROM StatusLogs) AS s
    WHERE 
        s.ProperRow = @StatusId 
        AND AppId = @AppId
        )

Paging with Entity Framework (Advanced)

This example is only called “Advanced” because it addresses multiple tables for paging. This will often be the case, but you can view a simple example that addresses one table here: Paging with Entity Framework (Simple)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

        /// <summary>
        /// Complicated Paged results from multiple tables in EF Query
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        /// <param name="PageNumber"></param>
        /// <param name="PageSize"></param>
        /// <param name="SearchTerm"></param>
        /// <param name="SearchFilter"></param>
        /// <param name="SortExpression"></param>
        /// <param name="SortOrder"></param>
        /// <param name="ActiveOnly"></param>
        /// <returns></returns>
        public PagedResult<Models.SalesProduct> GetSalesDetailsByEF(int PageNumber,
                                                                    int PageSize,
                                                                    string SearchTerm,
                                                                    string SearchFilter,
                                                                    string SortExpression,
                                                                    string SortOrder,
                                                                    bool ActiveOnly)
        {
            try
            {
                int _skipRows = (PageNumber - 1) * PageSize; // if this is not the first call, need move forward
                int _totalCount = 0; // placeholder for the total amount of records

                using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
                {
                    // Using var because this is returning an anonymous type
                    var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
                                       join sdetail in _efEntities.SalesOrderDetails on sheader.SalesOrderID equals sdetail.SalesOrderID
                                       join product in _efEntities.Products on sdetail.ProductID equals product.ProductID
                                       select new Models.SalesProduct()
                                       {
                                           OrderQuantity = sdetail.OrderQty,
                                           ProductId = sdetail.ProductID,
                                           ProductName = product.Name,
                                           ProductNumber = product.ProductNumber,
                                           ShipDate = sheader.ShipDate ?? DateTime.MinValue,
                                           UnitPrice = sdetail.UnitPrice,
                                           OnlineOrderFlag = sheader.OnlineOrderFlag
                                       });

                    if (ActiveOnly)
                    {
                        // Showing how to use ActiveOnly without a boolean flag as an example
                        _entityrows = _entityrows.Where(er => er.ShipDate.Year > 2013);
                    }

                    if (!string.IsNullOrWhiteSpace(SearchFilter))
                    {
                        // This can be customized for each implementation
                        bool _onlineOrder;
                        bool.TryParse(SearchFilter, out _onlineOrder);
                        _entityrows = _entityrows.Where(f => f.OnlineOrderFlag == _onlineOrder);
                    }

                    if (!string.IsNullOrWhiteSpace(SearchTerm))
                    {
                        // This can be customized for each implementation
                        _entityrows = _entityrows.Where(f => f.ProductName.Contains(SearchTerm.Trim()));
                    }

                    // Getting count will execute a SELECT COUNT(*)
                    // Like to do this before adding sort criteria
                    _totalCount = _entityrows.Count();

                    if (!string.IsNullOrWhiteSpace(SortExpression))
                    {
                        bool IsSortDESC = false;
                        if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }

                        _entityrows = _entityrows.OrderBy<Models.SalesProduct>(SortExpression, IsSortDESC);
                    }

                    _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

                    return new PagedResult<Models.SalesProduct>(_entityrows.ToList(), _totalCount);


                }
            }
            catch (Exception)
            {
                throw;
            }
        }

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging

Business Object used for Search and Paging

When I’m implementing a paged query, I wanted a simple object that I could return a strongly typed list of values along with a total count from my query. The business object used for search and paging also has the return of a List<T> of objects along with a TotalCount property from my Repository. You’ll see this as the return object in many of my sample paging posts.

This is basically a DTO, it doesn’t have any specific functionality.

    /// <summary>
    /// Yasgar Technology Group, Inc.
    /// http://www.ytgi.com
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    [DataContract(Name = "PagedResult", Namespace = "", IsReference = true)]
    public class PagedResult<TEntity> : IPagedResult<TEntity>
    {

        #region Constructors

        /// <summary>
        /// Primary constructor
        /// </summary>
        /// <param name="items"></param>
        /// <param name="totalCount"></param>
        public PagedResult(IEnumerable<TEntity> items, int totalCount)
        {
            m_items = items;
            m_totalCount = totalCount;
        }

        #endregion // Constructors

        #region Fields

        private IEnumerable<TEntity> m_items;
        private int m_totalCount = 0;

        #endregion // Fields

        #region Properties

        /// <summary>
        /// IEnumerable list of items returned
        /// </summary>
        [DataMember]
        public IEnumerable<TEntity> Items
        {
            get { return m_items; }
            protected set { m_items = value; }
        }

        /// <summary>
        /// Total count of records from query
        /// </summary>
        [DataMember]
        public int TotalCount
        {
            get { return m_totalCount; }
            protected set { m_totalCount = value; }
        }

        #endregion // Properties

    }

This data transfer object (DTO) is used to house the results from a paging query that has the minimum amount of information needed to return from the database. It does not need to have search criteria etc, as it is assumed that the calling method already has all that data. This will help you have a standard return object that not only has the collection of objects, but the total count that would have been returned had there not been any filters.

Here is an example of this object in use:

Paging with Entity Framework (Simple)

Paging with Entity Framework (Simple)

Whenever you have a need to show a paged list of your items, it is a must that this is done in your repository in Entity Framework so that you’re not passing around more data than is necessary. There are too many examples of paging on the web which show paging at the UI level, especially terrible ones are showing doing a LINQ to SQL query with ToList() after it. Horrible!

DON’T EVER DO THIS!

var _result = dbContext.Customers.ToList().Skip(page).Take(pageSize);

The following example is only called “Simple” because it addresses one table for paging. This will often be the case, but you can view a more complicated query here: Paging with Entity Framework (Advanced)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

This LINQ code was pulled directly from the LookupsRep class in the YTG MVC Lookups and Paging Demo Download.

/// <summary>
/// Simple Paged results from multiple tables in EF Query
/// Yasgar Technology Group, Inc. - www.ytgi.com
/// </summary>
/// <param name="Page"></param>
/// <param name="PageSize"></param>
/// <param name="SearchTerm"></param>
/// <param name="SearchFilter"></param>
/// <param name="SortColumn"></param>
/// <param name="SortOrder"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<PagedResult<LuCategories>> GetLuCategoriesByEFAsync(int Page,
            int PageSize,
            string SearchTerm,
            string SearchFilter,
            string SortColumn,
            string SortOrder,
            bool ActiveOnly)
{
    try
    {
        int _skipRows = (Page - 1) * PageSize; // if this is not the first call, need move forward
        int _totalCount = 0; // placeholder for the total amount of records

        // Using var because this is returning an anonymous type
        var _entityrows = (from item in LuContext.LuCategories.Include(a => a.LuItems)
                           select item);

        if (ActiveOnly)
        {
            // Showing how to use ActiveOnly without a boolean flag as an example
            _entityrows = _entityrows.Where(er => er.IsActive == true);
        }

        if (!string.IsNullOrWhiteSpace(SearchFilter))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.ShortName == SearchFilter);
        }

        if (!string.IsNullOrWhiteSpace(SearchTerm))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.Name.Contains(SearchTerm.Trim()));
        }

        // Getting count will execute a SELECT COUNT(*)
        // Like to do this before adding sort criteria
        _totalCount = _entityrows.Count();

        if (!string.IsNullOrWhiteSpace(SortColumn))
        {
            bool IsSortDESC = false;
            if (SortOrder.ToLower() == "desc") { IsSortDESC = true; }

            _entityrows = _entityrows.OrderBy(SortColumn, IsSortDESC);
        }

        _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

        return new PagedResult<Models.LuCategories>(await _entityrows.ToListAsync(), _totalCount);

    }
    catch (Exception)
    {
        throw;
    }
}

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging

Linq OrderBy Extension using a string

When writing more complicated Linq queries against an ORM, it is often desirable to be able to randomly pass in a field name as a string and sort order (direction) on the fly. This comes in especially handy for the paging project that you’ll also find on this Blog. I developed this Linq OrderBy Extension using a string and boolean value to determine if should sort ascending or descending.

Basically, with out of the box Linq, you can’t easily change the sort column dynamically. There is a Dynamic Linq Library (System.Linq.Dynamic) that you can install using a NuGet package, but that seemed overkill to me for this need. After looking that over, if you feel it’s a better solution for you, you won’t need to use this extension.

This extension method allows you to pass in the collection as IQueryable, so you can pass in Linq queries that haven’t been executed yet. Then it accepts your OrderByProperty and whether you want the query set to sort the results Descending as a boolean value.

You implement it like this:

using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
{
    var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
                       select sheader);

    if (!string.IsNullOrWhiteSpace(SortExpression))
    {
        bool IsSortDESC = false;
        if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }
  
        _entityrows = _entityrows.OrderBy(SortExpression, IsSortDESC);
    }

	return _entityrows.ToList();
	
}

Put the following Extension in your extensions class and make sure to put a using to that location at the top of the class where you’re going to use it.

    /// <summary>
    /// Implementation of OrderBy Extension Method for IQueryable Collections
    /// </summary>
    /// <typeparam name="TEntity">Generic Type Object</typeparam>
    /// <param name="p_Source">The collection to order</param>
    /// <param name="p_OrderByProperty">The property to order by</param>
    /// <param name="p_Descending">True to sort Descending</param>
    /// <returns></returns>
    public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> p_Source, string p_OrderByProperty, bool p_Descending)
    {
        try
        {
            string command = p_Descending ? "OrderByDescending" : "OrderBy";
            var type = typeof(TEntity);
            var property = type.GetProperty(p_OrderByProperty);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExpression = Expression.Lambda(propertyAccess, parameter);
            var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
                                          p_Source.Expression, Expression.Quote(orderByExpression));
            return p_Source.Provider.CreateQuery<TEntity>(resultExpression);
        }
        catch (ArgumentNullException)
        {
            throw new Exception("The OrderByProperty value of: '" + p_OrderByProperty + "', was empty or is not a proper column name!");
        }
        catch (Exception)
        {
            throw;
        }
    }

If you like this, take a look at some other extension methods that I use all the time:

Jack’s Top 10 String Extension Methods
Trim String to Particular Length

Display SQL for LINQ to Entities

If you’re fluent in SQL and want to display SQL for LINQ to Entities in your .NET application, there are ways to do it. It heavily depends on the version of Entity Framework you’re using

I was looking for a way to display the SQL behind a LINQ to entities query in the immediate window. Lots of sites have some code, except they didn’t seem to work for me ☹.

In Entity Framework version 6.0, you can use the following code. You have to make sure that you don’t have your LINQ query ending with anything that will cause the query to execute, such as .ToList().

((System.Linq.IQueryable) %queryvar%).ToString()

You can use this right in the immediate window. Here is another that I found on the web that didn’t work for me for whatever reason. I think ObjectQuery is not in that library any longer:

((System.Data.Objects.ObjectQuery) %queryvar%).ToTraceString()

If you don’t mind changing your code, there is a way to display the query in the Output windows (after it executes) with this code Added before your SaveChanges().

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

For Entity Framework Core, version 6.x, they have added a DebugView property in Visual Studio so that you can inspect the query at a stop point. If you want to get the query in code, you can use this:

string _myQuery = _entityrows.ToQueryString();

Keep on LINQing!