LINQ to Entities Does not Support Invoke in Entity Framework

Question

Below is the function where I have to use expressions but I think Entity Framework does not allow this. I have used this function in Linq-to-SQL and it's working. I have also tried using LinqKit because thats what I found in many answers but error remains there.

public static IList<SelectListItem> From<T>(IQueryable<T> Source, Expression<Func<T, object>> Value, Expression<Func<T, string>> Text)
{
    var q = from T item in Source
            select new SelectListItem()
            {
                Text = Text.Compile()((item)),
                Value = Value.Compile()((item)).ToString()
            };
    return q.ToList();
}

When I provide List<T> as a source by converting it to IQueryable it works.

Are you sure it is working in LinqToSQL? Have you taken a look at the SQL generated? — Aron, Feb 02 '16 at 10:17
However if you really do want to fix this, it is pretty easy. The steps are [here](http://stackoverflow.com/a/30136689/1808494). Only Step 3 will be different. — Aron, Feb 02 '16 at 10:19
@Aron LINQ to SQL is far more tolerant than EF about constructs which cannot be translated to SQL, and simply performs them on the client instead. It would not at all surprise me if it does "work" with that, and the SQL should show that it simply requests the whole `item`. — , Feb 02 '16 at 10:27
LINQ to SQL is doing exactly what you are but doesn't tell you - it loads everything in memory, then converts the query to LINQ to Objects. This can eradicate performance as it can load a *lot* of unwanted objects in memory. EF doesn't allow this and warns you immediatelly. — Panagiotis Kanavos, Feb 02 '16 at 10:29

Yacoub Massad · Answer 1 · 2016-02-02T10:32:24.327

You can solve your problem with LinqKit like this:

public static IList<SelectListItem> From<T>(
    IQueryable<T> Source,
    Expression<Func<T, object>> Value,
    Expression<Func<T, string>> Text)
{
    var q = from T item in Source.AsExpandable()
            select new SelectListItem()
            {
                Text = Text.Invoke(item),
                Value = Value.Invoke(item).ToString()
            };

    return q.ToList();
}

AsExpandable allows the expressions to be expanded before the query executes.

When I tested the above code on a customers table like this:

var result =
    From(
        context.Customers,
        x => x.CustomerId,
        x => x.Name);

this is the SQL that was executed on the SQL server:

SELECT 
    [Extent1].[CustomerId] AS [CustomerId], 
    [Extent1].[Name] AS [Name], 
     CAST( [Extent1].[CustomerId] AS nvarchar(max)) AS [C1]
    FROM [dbo].[Customers] AS [Extent1]

I Tried LInqKit already but its also not working the error in this case is "Unable to cast object of type 'System.Linq.Expressions.InstanceMethodCallExpressionN' to type 'System.Linq.Expressions.LambdaExpression'." — Zeeshan Zahoor, Feb 02 '16 at 11:06
Are you using the code in my answer exactly as is? I have tested this code locally and it works. Can you show the code that you currently have? — Yacoub Massad, Feb 02 '16 at 11:23

score 0 · Answer 2 · edited May 23 '17 at 12:23

You need to build the whole expression using System.Linq.Expressions.

Here is a function that does that (a modified version of my answer to the similar question Expression to convert IQueryable<t> int List<SelectListItem>):

public static class Utils
{
    public static IList<SelectListItem> ToSelectList<TSource, TValue>(this IQueryable<TSource> source, Expression<Func<TSource, TValue>> valueSelector, Expression<Func<TSource, string>> textSelector)
    {
        var itemValue = valueSelector.Body;
        if (itemValue.Type != typeof(string))
            itemValue = Expression.Call(itemValue, itemValue.Type.GetMethod("ToString", Type.EmptyTypes));
        var itemText = textSelector.Body.ReplaceParameter(textSelector.Parameters[0], valueSelector.Parameters[0]);
        var targetType = typeof(SelectListItem);
        var selector = Expression.Lambda<Func<TSource, SelectListItem>>(
            Expression.MemberInit(Expression.New(targetType),
                Expression.Bind(targetType.GetProperty("Value"), itemValue),
                Expression.Bind(targetType.GetProperty("Text"), itemText)
            ), valueSelector.Parameters);
        return source.Select(selector).ToList();
    }

    static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterExpressionReplacer { source = source, target = target }.Visit(expression);
    }

    class ParameterExpressionReplacer : ExpressionVisitor
    {
        public ParameterExpression source;
        public Expression target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == source ? target : base.VisitParameter(node);
        }
    }
}

Note that I've changed the name, added second generic argument to be able to pass unmodified value type, and made it an extension method, so it can be used like this

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

var result = db.Persons.ToSelectList(p => p.Id, p => p.Name);

LINQ to Entities Does not Support Invoke in Entity Framework

2 Answers2