Using expressions in the "Select" part of LINQ to entities queries

Question

I'd like to be able to reuse the "select" portions of my LINQ to Entities queries. For example, I can take the following...

projectQuery.Select(p => new ProjectModel
    ProjectName = p.ProjectName,
    ProjectNumber = p.ProjectNumber);

and replace it with an expression...

projectQuery.Select(ProjectModel.FullSelector);

where FullSelector looks like this:

public static System.Linq.Expressions.Expression<Func<Project, ProjectModel>> FullSelector = project => new ProjectModel
{
    ProjectName = p.ProjectName,
    ProjectNumber = p.ProjectNumber
};

This works great, and the query sent to the database only selects the fields that are used by the FullSelector. Plus I can reuse the FullSelector every time I need to query for Project entities.

Now for the tricky part. When doing queries that contain navigational properties, the nested selector expressions do not work.

public static System.Linq.Expressions.Expression<Func<Project, ProjectModel>> FullSelector = project => new ProjectModel
{
    ProjectName = p.ProjectName,
    ProjectNumber = p.ProjectNumber
    Addresses = p.Addresses.Select(AddressModel.FullSelector);
};

This does not work. The inner Select gives the compile time error "The type arguments cannot be inferred from the usage. Try specifying the type arguments explicitly."

The following example compiles but crashes when the query is executed saying "Internal .NET Framework Data Provider error 1025.":

public static System.Linq.Expressions.Expression<Func<Project, ProjectModel>> FullSelector = project => new ProjectModel
{
    ProjectName = p.ProjectName,
    ProjectNumber = p.ProjectNumber
    Addresses = p.Addresses.Select(AddressModel.FullSelector.Compile());
};

The next example compiles but throws the runtime error "LINQ to Entities does not recognize the method 'EPIC.WebAPI.Models.AddressModel Invoke(EPIC.Domain.Entities.Address)' method, and this method cannot be translated into a store expression."

public static System.Linq.Expressions.Expression<Func<Project, ProjectModel>> FullSelector = project => new ProjectModel
{
    ProjectName = p.ProjectName,
    ProjectNumber = p.ProjectNumber
    Addresses = p.Addresses.Select(a => AddressModel.PartialSelector.Compile().Invoke(a));
};

Does anyone know how to get the inner select to work? I understand why the last example doesn't work, but are the first two close to working?

Thanks!

Answer 1

So, first off, why doesn't your code work. The first snippet:

p.Addresses.Select(AddressModel.FullSelector);

This doesn't work because the navigational properties do not implement IQueryable, they implement ICollection. ICollection of course doesn't have a Select method that accepts an Expression parameter.

The second snippet:

p.Addresses.Select(AddressModel.FullSelector.Compile());

This doesn't work because FullSelector is being compiled. Since it's being compiled, the query provider can't look into the body of the method and translate the code into SQL code.

The third snippet has the exact same problem as the second. Wrapping it in a lambda doesn't change that fact.

---

So, now that we know why your code doesn't work, what to do now?

This is going to be a bit mind boggling, and I'm not a huge fan of the design of this method, but here we go. We'll write a method that accepts an expression representing a function with one argument, then it'll accept another that accepts some unrelated type, then a function of the same type as the delegate in our first parameter, and then returns an unrelated type.

The implementation of this method can simply replace all instances of the delegate parameter used with the expression that we have, and then wrap it all up in a new lambda:

public static Expression<Func<T1, T2>> Use<T1, T2, T3, T4>(
    this Expression<Func<T3, T4>> expression,
    Expression<Func<T1, Func<T3, T4>, T2>> other)
{
    return Expression.Lambda<Func<T1, T2>>(
        other.Body.Replace(other.Parameters[1], expression),
        other.Parameters[0]);
}
//another overload if there are two selectors
public static Expression<Func<T1, T2>> Use<T1, T2, T3, T4, T5, T6>(
    this Expression<Func<T3, T4>> firstExpression,
    Expression<Func<T5, T6>> secondExpression,
    Expression<Func<T1, Func<T3, T4>, Func<T5, T6>, T2>> other)
{
    return Expression.Lambda<Func<T1, T2>>(
        other.Body.Replace(other.Parameters[1], firstExpression)
            .Replace(other.Parameters[2], secondExpression),
        other.Parameters[0]);
}

The idea is kinda mind boggling, but the code is actually quite short. It relies on this method to replace all instances of one expression with another:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

Now to call it we can call Use on our address selector and then write a method that accepts both our normal parameter as well as the delegate for the address selector:

public static Expression<Func<Project, ProjectModel>> FullSelector =
    AddressModel.FullSelector.Use((Project project,
        Func<Address, AddressModel> selector) => new ProjectModel
        {
            ProjectName = project.ProjectName,
            ProjectNumber = project.ProjectNumber,
            Addresses = project.Addresses.Select(selector),
        });

And now this will work exactly as desired.