I recently joined a project in which a method Sortconditionally passed expression to lambda in a LINQ query to determine which property should be sorted. The problem was that the lambda expression was passed to Func<TEntity, Object>, not to Expression<Func<TEntity, Object>>, so the sorting took place in memory, not in the database (because the overload OrderBythat takes IEnumerable). This is the version in SortWithDelegate(see below).
When I use Expression<Func<TEntity, Object>>(see below SortWithExpression), then when the string property is passed in the parameter OrderBy, the ordering is done correctly in the database. However, when I try to sort by integer (or datetime) using Expression<Func<TEntity, Object>>, I get the following error:
Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.
To avoid this, I need to wrap the integer or datetime field to sort in an anonymous type: orderByFunc = sl => new {sl.ParentUnit.Id};. I understand that I need to do this, since the return type Funcis equal Object. However, I don’t understand why I need to do this when working with the LINQ to Entities provider, but not with the LINQ to Objects provider?
void Main()
{
var _context = new MyContext();
string sortProperty = "Id";
bool sortAscending = false;
IQueryable<Qualification> qualifications = _context.Qualifications.Include(q => q.ParentUnit);
qualifications = SortWithExpression(sortProperty, sortAscending, qualifications);
qualifications.Dump();
}
private static IQueryable<Qualification> SortWithDelegate(string orderBy, bool sortAscending, IQueryable<Qualification> qualificationsQuery)
{
Func<Qualification, Object> orderByFunc;
switch (orderBy)
{
case "Name":
orderByFunc = sl => sl.Name;
break;
case "ParentUnit":
orderByFunc = sl => sl.ParentUnit.Name;
break;
case "Id":
orderByFunc = sl => sl.ParentUnit.Id;
break;
case "Created":
orderByFunc = sl => sl.Created;
break;
default:
orderByFunc = sl => sl.Name;
break;
}
qualificationsQuery = sortAscending
? qualificationsQuery.OrderBy(orderByFunc).AsQueryable()
: qualificationsQuery.OrderByDescending(orderByFunc).AsQueryable();
return qualificationsQuery;
}
private static IQueryable<Qualification> SortWithExpression(string orderBy, bool sortAscending, IQueryable<Qualification> qualificationsQuery)
{
Expression<Func<Qualification, Object>> orderByFunc;
switch (orderBy)
{
case "Name":
orderByFunc = sl => sl.Name;
break;
case "ParentUnit":
orderByFunc = sl => sl.ParentUnit.Name;
break;
case "Id":
orderByFunc = sl => new {sl.ParentUnit.Id};
break;
case "Created":
orderByFunc = sl => new {sl.Created};
break;
default:
orderByFunc = sl => sl.Name;
break;
}
qualificationsQuery = sortAscending
? qualificationsQuery.OrderBy(orderByFunc)
: qualificationsQuery.OrderByDescending(orderByFunc);
return qualificationsQuery;
}
Added
, . int datetime, IQueryable<T>, , , , :
public static IQueryable<TSource> OrderBy<TSource, TResult>(this IQueryable<TSource> query, Expression<Func<TSource, TResult>> func, bool sortAscending)
{
return sortAscending ?
query.OrderBy(func) :
query.OrderByDescending(func);
}
private static IQueryable<Qualification> Sort(string orderBy, bool sortAscending, IQueryable<Qualification> qualificationsQuery)
{
switch (orderBy)
{
case "Name":
return qualificationsQuery.OrderBy(sl => sl.Name, sortAscending);
case "ParentUnit":
return qualificationsQuery.OrderBy(s1 => s1.ParentUnit.Name, sortAscending);
default:
return qualificationsQuery.OrderBy(sl => sl.Name, sortAscending);
}
}