LINQ to Entities - Cannot use "System.DateTime" to enter "System.Object" in orderBy

Question

I try to order IQueryable from entities by date from the passed in Expression <Func <T, object → and I get the error: "Cannot use type" System.Nullable`1 "to enter" System.Object ". LINQ to Entities only supports listing of primitives Entity Data Model ". The object has a nullable datetime property on which I am trying to sort:

Example: (where e.Date is a null DateTime value)

Expression<Func<T,object>> sorter = (e) => e.Date; IOrderedQueryable<T> sortedData = data.OrderBy(sorter); 

Thanks in advance!

Answer 1

I wrote a simple class for organizing entities based on a lambda expression at runtime.

 using System; using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; using System.Reflection; namespace DataModeling { public class QueryOrderer<TEntity> where TEntity : class { private LambdaExpression defaultSortExpression; private Dictionary<string, LambdaExpression> orderFieldLookup; public QueryOrderer() { orderFieldLookup = new Dictionary<string, LambdaExpression>(); } public void AddOrderMapping<TProp>(string fieldName, Expression<Func<TEntity, TProp>> selector) { orderFieldLookup[fieldName] = selector; } public void SetDefaultSortExpression<TProp>(Expression<Func<TEntity, TProp>> selector) { defaultSortExpression = selector; } public IQueryable<TEntity> GetOrderedEntries(string field, bool isDescending, IQueryable<TEntity> entries) { return orderEntries(entries, field, isDescending); } private IQueryable<TEntity> orderEntries(IQueryable<TEntity> entries, string fieldName, bool isDescending) { dynamic lambda = getOrderByLambda(entries, fieldName); if (lambda == null) { return entries; } if (isDescending) { return Queryable.OrderByDescending(entries, lambda); } else { return Queryable.OrderBy(entries, lambda); } } private dynamic getOrderByLambda(IQueryable<TEntity> entries, string fieldName) { if (!String.IsNullOrWhiteSpace(fieldName) && orderFieldLookup.ContainsKey(fieldName)) { return orderFieldLookup[fieldName]; } else { return defaultSortExpression; } } } } 

You use this class by pre-setting all the fields:

 QueryOrderer<User> orderer = new QueryOrderer<User>(); orderer.SetDefaultSortExpression(u => u.FullName); orderer.AddOrderMapping("UserId", u => u.UserId); orderer.AddOrderMapping("Name", u => u.FullName); orderer.AddOrderMapping("Email", u => u.Email); orderer.AddOrderMapping("CreatedOn", u => u.CreatedOn); ... var users = orderer.GetOrderedEntries("CreatedOn", isDescending: false, context.Users); 

A good feature of this code is that it handles the displayed values ​​very well. For example, if you are trying to sort using a description rather than a key, you can use the external context when creating the sort expression:

 orderer.AddOrderMapping("UserType", u => context.UserTypes .Where(t => t.UserTypeId == u.UserTypeId) .Select(t => t.Description) .FirstOrDefault()); 

The Entity Framework is smart enough to just collapse a sub request directly into an external request.

Answer 2

Two problems here: first you use object in the sorter, you have to use DateTime . Secondly, each element should take place in order, so you must determine what should happen to elements where Date is null :

 Expression<Func<T, DateTime>> sorter = (e) => e.Date ?? DateTime.MaxValue; IOrderedQueryable<T> sortedData = data.OrderBy(sorter); 

Answer 3

Try restoring expression body

 private LambdaExpression CreateLambdaPropertyGetter(Expression<Func<TEntity, object>> expression) { Expression body; if (expression.Body is UnaryExpression && ((UnaryExpression)expression.Body).NodeType == ExpressionType.Convert) body = ((UnaryExpression)expression.Body).Operand; else body = expression.Body; var lambda = Expression.Lambda(body, expression.Parameters); return lambda; } 

Answer 4

Try using the Func delegate instead of Expression<Func>

 Func<T,object> sorter = (e) => e.Date; IOrderedEnumerable<T> sortedData = data.OrderBy(sorter);