EF + LINQ: How do I define and utilize a variable (maybe a Func variable) to define part of a Select statement?

Question

Before try to use a variable (this has sql server do the calculation):

IQueryable<MyEntity> query = _dbSet; // IQueryable<TEntity>
var results = query.Select(m => new MyViewModel
{
    MyCalculation = m.Column1 * m.Column2
}).ToList();

What I want to do (dynamically create part of my select statement from a Func variable or some other kind of variable to allow this):

IQueryable<MyEntity> query = _dbSet; // IQueryable<TEntity>
Func<MyEntity, decimal> funcVariableAttempt = m => m.Column1 * m.Column2;
var results = query.Select(m => new MyViewModel
{
    MyCalculation = funcVariableAttempt.Invoke(m) // My foolish attempt does not work.
}).ToList();

The error I get when I try what I want (aka my foolish attempt):

LINQ to Entities does not recognize the method 'System.Decimal Invoke(MyProject.Repository.Models.MyEntity)' method, and this method cannot be translated into a store expression.

How do I define and utilize a variable (maybe a Func variable) to define part of a Select statement?

Answer 1

It's a completely valid question that I stumbeled across earlier and found a solution that works well for me.

The problem with your Func<MyEntity, decimal> is that it is a delegate and that the O/R mapper has to have access to the internal expression (the multiplication of two properties in your case). But this information is compiled into the delegate and hidden forever.

If you however start off with a Expression<Func<MyEntity, decimal>> customCalculation, things look more promising as you have the internal logic as an expression tree. Problem with this is: you cannot invoke an expression. customCalculation(m) doesn't compile.

The compiler would let you write

m => new MyViewModel { MyCalculation = customCalculation.Compile()(m) }

, but this wouldn't be understood by most O/R mappers.

But you see that it at least somehow contains the customCalculation lambda expression and also how it relates to its surrounding expressions.

Getting from here to the expression tree as in your original working version involves some expression manipulation:

We have to replace customCalculation.Compile()(m) with the body of the lambda that is to be Compile()d, but with the lambda's parameter(s) replaced with the respective expression(s) of the delegate invocation. So if customCalculation were x => x.Column1 * x.Column2, customCalculation.Compile()(m) would have to be replaced with m.Column1 * m.Column2

Doing so is not trivial, since the lambda itself has to be dug out of a field inside an instance of a compiler generated closure class.

I've posted my implementation of this expression manipulator in another similar question . Hope that helps.

With that, you should be able to:

var customCalculation = (Expression<Func<MyEntity, decimal>>)(x => x.Column1 * x.Column2);
var selector = Express.Prepare((Expression<Func<MyEntity, MyViewModel>>)(m => new MyViewModel { MyCalculation = customCalculation.Compile()(m) }));
var result = query.Select(selector).ToList();

Answer 2

As you already know, your funcVariableAttempt makes no sense to your database, so you have to call your method in the linq-to-object context. i.e. for instance first fetch the data as an Enumerable, then call your method:

var results = query.Select(m => new {
Column1= col1,
Column2= col2
}).AsEnumerable()
    .Select(m => new MyViewModel
{
    MyCalculation = Foo(m.Column1, m.Column2)
});

*Note: Code is not tested.

Answer 3

You should call ToList() first and perform the Select() on the result in memory.

var results = query.ToList() 
 .Select(m => new MyViewModel { 
    MyCalculation = Foo(m.Column1, m.Column2)
 });

You're trying to perform the Select as part of the query. You should just use a regular function for the mapping calculation. Lambda functions are useful with LINQ but in this case they're not needed.