EF + LINQ: How to define and use a variable (possibly a Func variable) to define a part of a Select statement?
Before trying to use a variable (this is what SQL server has, 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 a portion of my select statement from a Func variable or some other 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 to do what I want (aka my stupid attempt):
LINQ to Entities does not recognize the "System.Decimal Invoke (MyProject.Repository.Models.MyEntity)" method, and this method cannot be translated into a store expression.
How do I define and use a variable (possibly a Func variable) to define a portion of a Select statement?
source to share
This is a perfectly correct question that I stumbled upon before and found a solution that works well for me.
The problem with yours Func<MyEntity, decimal>
is that it is a delegate and that the O / R mapper must have access to the inner expression (multiplying two properties in your case). But this information is compiled into a delegate and hidden forever.
If you start with Expression<Func<MyEntity, decimal>> customCalculation
, however, things look more promising since you have internal logic as an expression tree. The problem is this: you cannot call an expression. customCalculation(m)
does not compile.
The compiler will let you write
m => new MyViewModel { MyCalculation = customCalculation.Compile()(m) }
but this will not be understood by most O / R charts.
But you can see that it at least somehow contains the lambda expression customCalculation
, as well as how it relates to its surrounding expressions.
Moving from here to the expression tree, as in the original working version, involves some manipulation of the expression:
We have to replace the customCalculation.Compile()(m)
body of the lambda, which should be Compile()
d, but with the lambda (s) parameter replaced with the appropriate delegate invocation expression (s). Therefore, if customCalculation
it was x => x.Column1 * x.Column2
, customCalculation.Compile()(m)
it would have to be replaced bym.Column1 * m.Column2
Doing this is not trivial, as the lambda itself has to be dug out of the field inside the compiler-generated class instance.
I posted my implementation of this expression manipulator in another similar question . Hope it helps.
In doing so, 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();
source to share
As you already know, yours funcVariableAttempt
doesn't make sense for your database, so you need to call your method in the linq-to-object context. that is, for example, first get the data as Enumerable
and 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: the code is not verified.
source to share
You must first call ToList () and execute Select () on the result in memory.
var results = query.ToList()
.Select(m => new MyViewModel {
MyCalculation = Foo(m.Column1, m.Column2)
});
You are trying to execute the Select As Part Of Query command. You should just use a regular function to compute the mapping. Lambda functions are useful for LINQ, but in this case they are not needed.
source to share