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.