Tag: Query

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

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