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.
Thank you, Sergey. this is the first true generic Sort/Select I have come across. I just had to convert it to VB and add it to my Generic Repository. In which layer do you keep your OrderBy and Where clauses?
@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.
Thank You!!!