Correcting Sorting Issues in Entity Framework Repository

I posted earlier on how to implement repository pattern using Entity Framework Code First, while removing a dependency on IQueryable interface.  I identified an issue that still existed in that code related to sorting.  Sorting is a tricky part because SortBy method on IQueryable needs to have two generic parameters: item type and property that the query is being sorted on.  I could not come up in that version of a generic way to do so.

I am posting a solution to the problem.  I have added new interface I am going to use for sorting:

using System;

using System.Linq;

 

namespace EFCodeFirstExtensions

{

  public interface IOrderByClause<T>

   where T : class, new()

  {

    IOrderedQueryable<T> ApplySort(IQueryable<T> query, bool firstSort);

  }

}

 

The reason I have second parameter to Apply method is that I have to use OrderBy or ThenOrderBy methods depending on where or not I already sorted a query once.  I am also moving the actual sort implementation to the method in order to support a simple implementation in the code that uses repository.  Here is my SortBy classes that implements interface above:

using System;

using System.Linq.Expressions;

using System.Reflection;

using System.Linq;

 

namespace EFCodeFirstExtensions

{

  public class OrderByClause<T, TProperty> : EFCodeFirstExtensions.IOrderByClause<T>

    where T : class, new()

  {

    private OrderByClause()

    {

 

    }

 

    public OrderByClause(

      Expression<Func<T, TProperty>> orderBy,

      SortDirection sortDirection = SortDirection.Ascending)

    {

      OrderBy = orderBy;

      SortDirection = sortDirection;

    }

 

    /// <summary>

    /// Order by expression

    /// </summary>

    private Expression<Func<T, TProperty>> OrderBy { get; set; }

 

    /// <summary>

    /// Sort direction

    /// </summary>

    private SortDirection SortDirection { get; set; }

 

    public IOrderedQueryable<T> ApplySort(IQueryable<T> query, bool firstSort)

    {

      if (SortDirection == EFCodeFirstExtensions.SortDirection.Ascending)

      {

        if (firstSort)

        {

          return query.OrderBy(OrderBy);

        }

        else

        {

          return ((IOrderedQueryable<T>)query).ThenBy(OrderBy);

        }

      }

      else

      {

        if (firstSort)

        {

          return query.OrderByDescending(OrderBy);

        }

        else

        {

          return ((IOrderedQueryable<T>)query).ThenByDescending(OrderBy);

        }

      }

 

    }

 

  }

}

 

As you can see above, my SortBy class is now responsible for applying the sort.  Now I just have to update my repository class to delegate to SortyBy:

    /// <summary>

    /// Select data from database using a where clause

    /// </summary>

    /// <typeparam name="TItem">Type of data to select</typeparam>

    /// <param name="whereClause">Where clause / function</param>

    /// <param name="orderBy">Order by clause</param>

    /// <param name="skip">Paging implementation = number of records to skip</param>

    /// <param name="top">Paging implementation = number of records to return</param>

    /// <param name="include">Navigation properties to include</param>

    /// <returns>Items selected</returns>

    public IEnumerable<TItem> Select<TItem>(

      Expression<Func<TItem, bool>> whereClause = null,

      IOrderByClause<TItem>[] orderBy = null,

      int skip = 0,

      int top = 0,

      string[] include = null)

       where TItem : class, new()

    {

 

      IQueryable<TItem> data = context.Set<TItem>();

      // handle where

      if (whereClause != null)

      {

        data = data.Where(whereClause);

      }

 

      //handle order by

      if (orderBy != null)

      {

        bool isFirstSort = true;

        orderBy.ToList().ForEach(one =>

        {

          data = one.ApplySort(data, isFirstSort);

          isFirstSort = false;

        });

      }

 

      // handle paging

      if (skip > 0)

      {

        data = data.Skip(skip);

      }

      if (top > 0)

      {

        data = data.Take(top);

      }

 

      //handle includes

      if (include != null)

      {

        include.ToList().ForEach(one => data = data.Include(one));

      }

 

      foreach (var item in data)

      {

        yield return item;

      }

    }

 

The code is very simple.  I just have to keep track of whether or not I am on the first sort by clause or not.

Here is the program that uses this method:

      using (var repo = new TestRepo())

      {

        var locs = repo.Select<Location>(one => one.LocationID == 2, new IOrderByClause<Location>[] {

          new OrderByClause<Location, int>(one=>one.LocationID, SortDirection.Descending),

          new OrderByClause<Location, string>(one=>one.LocationName, SortDirection.Ascending)}, 1, 1, new[] { "Attendees" });

 

 

        var thing = locs.ToList();

 

      }

 

As you can see, now I am not relying on IQueryable, yet I am supporting all major functions of queries against database, such as filtering, sorting, paging and including related data.

You can download complete project here.

3 Comments

  1. @Michel
    Personally, I would always keep the logic in DAL. Generic select is good, but as I mentioned before I am not sure I like the concept myself that much. I think the danger is that your query logic can be spread all the way throughout your application.
    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *