LINQ to Entities only supports casting in the Entity Framework. basic kinds in the Entity Data Model

entity-framework primitive-types

Question

I attempted to utilize a function I had written to accept an Expression as input for the orderby clause, but I encountered this issue.

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

Basically, the phrase is as follows:

Expression<Func<K, IComparable>> orderBy

And is used in this way:

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

The purpose is to enable me to retain string matches to expressions like:

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

Then, I have a method that accepts the sort string and, if it matches a key in the dictionary, returns the expression; else, it produces a default value. (The concept being a means to limit who may order it) Currently, this works for String properties, but not yet for datetime or numeric properties since I see the error message shown above.

As far as I can tell, the issue is that Entity Framework requires it to be a Primary/EDM type in order to translate the C# DateTime into a format that the database can read.

Is it possible to change the datetime into a primitive type and yet have this function?

Solution

The following is 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 general query method's method signature is as follows:

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

Using the method that was passed:

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

var orderedQuery = orderBy(initialQuery);

returnValue = orderedQuery
  .Skip(numberToShow * realPage)
  .Take(numberToShow)
  .ToList();
1
24
7/19/2009 10:16:03 PM

Accepted Answer

This is challenging with the Entity Framework, and I'm not sure you can accomplish your goals with only one return value type (IComparable, object, etc). You may think about changing your design to create a name-to-Func<IQueryable<K>, IOrderedQueryable<K>> values:

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

Applying it next as follows:

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

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

if (_possibleSortForForumItem.TryGetValue(orderColumnName, out assignOrderBy))
{
    orderedQuery = assignOrderBy(query);
}
13
7/17/2009 10:54:51 PM

Popular Answer

I'm aware that this post is outdated, but I wanted to achieve the same goal as the OP and didn't want to utilize theFunc<IQueryable<T>, IOrderedQueryable<T>> my dictionary says. mostly because I would need to utilize both anOrderBy and OrderByDescending delegate.

In the end, I developed an IQueryable extension method I've namedObjectSort This will only determine what the expression's return type should be before creating a new lambda that uses that type so that LINQ to Entities won't get confused.

The example below does work for, but I'm not sure whether this is a suitable approach.DateTime and int So perhaps it might inspire you if you're trying to achieve a similar goal!

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);
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow