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

Question

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.

Answer 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.