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

The Entity Framework makes this difficult and I'm not sure there's a way to do what you want to do with a single return value type (IComparable, object, etc). You might consider reworking your design into a dictionary of name-to-Func<IQueryable<K>, IOrderedQueryable<K>> values:

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

And then applying it like so:

var orderedQuery = query.OrderBy(item => item.DefaultOrderColumn);

Func<IQueryable<K>, IOrderedQueryable<K>> assignOrderBy = null;

if (_possibleSortForForumItem.TryGetValue(orderColumnName, out assignOrderBy))
{
    orderedQuery = assignOrderBy(query);
}