'Why 'Lambda' is not supported in my LINQ to Entities syntax [duplicate]

I get an error when I try to run the following query in LINQ to Entities:

public IEnumerable TestOne2()
{
    var query = this.Context.CmnAddressCities
        .Join(this.Context.CmnAddressStates, 
              p => p.StateID, q => q.StateID, 
              (p, q) => SelectSearchColumns)
        .ToList();

     return query;
}

public Expression<Func<CmnAddressCity,CmnAddressState, dynamic>>
    SelectSearchColumns = (e,r) => new
        {
            CityID = e.CityID,
            CityName = e.CityName,
            StateName=r.StateName,
        };

Error Message:

The LINQ expression node type 'Lambda' is not supported in LINQ to Entities.

I would like to know why this error arise and how to solve this.

c++sharplinqlinq-to-entitiesiqueryable


Solution 1:[1]

The following should work:

var query = this.Context.CmnAddressCities
                .Join(this.Context.CmnAddressStates, 
                      p => p.StateID, 
                      q => q.StateID, 
                      SelectSearchColumns)
                .ToList();

See the second to last line for the difference. Your version creates an expression that returns an expression. The EF provider can't translate that to SQL, what is the equivalence of an expression in SQL? That doesn't exist.
You simply have to pass your expression as the parameter itself.

Solution 2:[2]

This expression

(p, q) => SelectSearchColumns

is an expression that returns an expression. Try changing it to SelectSearchColumns on its own.

Solution 3:[3]

In the expression (p, q) => SelectSearchColumns you are passing SelectSearchColumns to the Join method instead of calling it. The query provider then fails to translate it into an SQL statement, because it is passed as a data, rather than being interpreted as a call.

You need either

.Join(this.Context.CmnAddressStates, 
     p => p.StateID, 
     q => q.StateID, 
     (p, q) => SelectSearchColumns(p, q))

or the short version

.Join(this.Context.CmnAddressStates, 
     p => p.StateID, 
     q => q.StateID, 
     SelectSearchColumns)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Daniel Hilgarth
Solution 2 Tim Rogers
Solution 3 Honza Brestan

Related Questions