CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-6
Christoph Fink
Christoph Fink

Reputation: 23113

OrderBy(Func<T, IComperable>) can't be translated to SQL

I implemented a dynamic sorting logic (see HERE) supplying Expression<Func<T, IComperable>> to the OrderBy method.

Now I have the problem, that EF can't convert the IComperable in the Func<T, IComperable> to its actualy type:

Unable to cast the type 'System.Int32' to type 'System.IComparable'. LINQ to Entities only supports casting EDM primitive or enumeration types.

at

System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ValidateAndAdjustCastTypes(TypeUsage toType, TypeUsage fromType, Type toClrType, Type fromClrType)

Is there a way to solve this?

The only way I found at the moment is "implementing" the Func<>'s as their real types, saving this type next to the Func<> and calling the OrderBy via reflection:

public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, Type orderType, object expression)
{
    return typeof(Queryable).GetMethods().First(m => m.Name == "OrderBy")
       .MakeGenericMethod(typeof(T), orderType)
       .Invoke(null, new object[] { source, expression }) as IQueryable<T>
}

But that seems very ugly (and slow?) to me and is not really as nice to use as the current (sadly only for LINQ to objects working) solution...

UPDATE:
This problem seems to only occure when returning int or bool in the Func<T, IComperable>, as it works fine with string...

Upvotes: 1

Views: 470

Answers (1)

Christoph Fink
Christoph Fink

Reputation: 23113

I solved it the following way for now:

var orderDelegates = new Dictionary<string, LambdaExpression>();

Expression<Func<Image, int>> id = i => i.Id;
orderDelegates.Add(ContentItem.ORDER_BY_ID, id);
Expression<Func<Image, IComperable>> title = i => i.Title;
orderDelegates.Add(ContentItem.ORDER_BY_Title, title);
//more items...

(I would like this to be a little bit shorter - see HERE)

and in my own OrderBy:

var first = orderDelegates[orderKey ?? defaultKey];
Type firstType = first.GetType().GetGenericArguments()[0].GetGenericArguments()[1];

IOrderedQueryable<T> firstOrder;
if (firstType == typeof(int))
    firstOrder = items.OrderBy<T, int>(first, direction);
else if (firstType == typeof(bool))
    firstOrder = items.OrderBy<T, bool>(first, direction);
else
    firstOrder = items.OrderBy<T, IComparable>(first, direction);

var second = orderDelegates[defaultKey];
Type secondType = second.GetType().GetGenericArguments()[0].GetGenericArguments()[1];
if (secondType == typeof(int))
    return firstOrder.ThenBy<T, int>(second, direction);
else if (secondType == typeof(bool))
    return firstOrder.ThenBy<T, bool>(second, direction);
else
    return firstOrder.ThenBy<T, IComparable>(second, direction);

and

public static IOrderedQueryable<T> OrderBy<T, K>(this IQueryable<T> items, LambdaExpression expression, OrderDirection? direction)
{
    if (direction == OrderDirection.Ascending || !direction.HasValue)
        return items.OrderBy(expression as Expression<Func<T, K>>);
    else
        return items.OrderByDescending(expression as Expression<Func<T, K>>);
}

public static IQueryable<T> ThenBy<T, K>(this IOrderedQueryable<T> items, LambdaExpression expression, OrderDirection? direction)
{
    if (direction == OrderDirection.Ascending || !direction.HasValue)
        return items.ThenBy(expression as Expression<Func<T, K>>);
    else
        return items.ThenByDescending(expression as Expression<Func<T, K>>);
}

Upvotes: 1

Related Questions