Entity Framework: LINQ to Entities only supports casting Entity Data Model primitive types

Question

I wrote a method to allow for an Expression to be passed in for the orderby clause, but I ran into this problem.

Unable to cast the type 'System.DateTime' to type 'System.IComparable'. LINQ to Entities only supports casting Entity Data Model primitive types.

Basically the expression is this:

Expression<Func<K, IComparable>> orderBy

And is used like this:

SomeEntities.SomeTable
.Where
(
   whereClause
)
.Select
(
   selectClause
)
.OrderBy(orderBy)

The idea is so that I can use a dictionary to hold string matches to expressions like:

_possibleSortForForumItem.Add("CreateDate", item => item.CreateDate);

Then I have a method that takes in the sort string and returns the expression if it matches a key in the dictionary, if not returns some default. (The idea being a way to control what it can be ordered by) Now this works for String properties, but so far not for datetime or integer as I get the error message above.

Now far as I (loosely) understand the problem is that Entity Framework needs it to be a Primary/EDM type because it has to convert the C# DateTime into something the database can handle.

Is there a way to convert the datetime to a primitive type so that this will still work?

Solution

The method for getting the order by method: (Take in a query and return it in "ordered form")

private static Func<IQueryable<ForumViewItem>, IOrderedQueryable<ForumViewItem>> GetMethodForSort(String sortBy)
{
  if (_methodForSort == null)
  {
    _methodForSort = new Dictionary<String, Func<IQueryable<ForumViewItem>, IOrderedQueryable<ForumViewItem>>>();
    _methodForSort.Add(SortForumViewItemCreatedOn, item => item.OrderBy(innerItem => innerItem.CreatedOn));
    ...
  }

  Func<IQueryable<ForumViewItem>, IOrderedQueryable<ForumViewItem>> orderMethod;

  if(String.IsNullOrEmpty(sortBy) || !_methodForSort.ContainsKey(sortBy))
  {
    orderMethod = _methodForSort["ForumName"];
  }
  else
  {
    orderMethod = _methodForSort[sortBy];
  }

  return orderMethod;
}

The method signature for the generic query method:

IList<K> GetListForGrid<T, K>(this ObjectQuery<T> query, ... Func<IQueryable<K>, IOrderedQueryable<K>> orderBy, ...)

And the use of the passed in method:

initialQuery = query
  .Where
  (
    somethingEqualsSomething
  )
  .Select
  (
    selectClause
  );

var orderedQuery = orderBy(initialQuery);

returnValue = orderedQuery
  .Skip(numberToShow * realPage)
  .Take(numberToShow)
  .ToList();

Answer 1

I know this is old, but I was looking to accomplish the exact same thing as the OP and didn't want to use the Func<IQueryable<T>, IOrderedQueryable<T>> in my dictionary. Mostly because I would have to implement both an OrderBy and OrderByDescending delegate.

I ended up creating an extension method for IQueryable called ObjectSort which will simply check to see what the return type of the expression should be and then create a new lambda using that type so that LINQ to Entities won't freak out.

I'm not sure if this is a good solution or not but the example below does work for DateTime and int so hopefully it can give you some ideas if you're looking to accomplish something similar!

public static IOrderedQueryable<T> ObjectSort<T>(this IQueryable<T> entities, Expression<Func<T, object>> expression, SortOrder order = SortOrder.Ascending)
{
    var unaryExpression = expression.Body as UnaryExpression;
    if (unaryExpression != null)
    {
        var propertyExpression = (MemberExpression)unaryExpression.Operand;
        var parameters = expression.Parameters;

        if (propertyExpression.Type == typeof(DateTime))
        {
            var newExpression = Expression.Lambda<Func<T, DateTime>>(propertyExpression, parameters);
            return order == SortOrder.Ascending ? entities.OrderBy(newExpression) : entities.OrderByDescending(newExpression);
        }

        if (propertyExpression.Type == typeof(int))
        {
            var newExpression = Expression.Lambda<Func<T, int>>(propertyExpression, parameters);
            return order == SortOrder.Ascending ? entities.OrderBy(newExpression) : entities.OrderByDescending(newExpression);
        }

        throw new NotSupportedException("Object type resolution not implemented for this type");
    }
    return entities.OrderBy(expression);
}