I would like to implement the following logic against Entity Frameworks.
var items = from item in myContext
select new {
Value1 = TweakValue(item.Value1),
Value2 = TweakValue(item.Value2)
};
protected int TweakValue(int value)
{
// Custom processing here
return value;
}
This won't work because of the call to TweakValue()
in the select
clause. I understand that the query is converted to SQL, and that the problem is that TweakValue()
cannot be converted to SQL. My question is what is the most economical way to implement this. Do I need a second loop to convert the values?
I'm still trying to get comfortable with LINQ expressions.
The simplest way is probably to just "move" the execution to the client to perform the transformation. In this case you'd just use:
var items = myContext.Select(item => new { item.Value1, item.Value2 })
.AsEnumerable()
.Select(item => new {
Value1 = TweakValue(item.Value1),
Value2 = TweakValue(item.Value2)
});
Note that you don't have to reuse the names for Value1
and Value2
- it's just easiest to do so.
If you really want to use query expressions:
var query = from item in myContext
select new { item.Value1, item.Value2 };
var items = from item in query.AsEnumerable()
select new {
Value1 = TweakValue(item.Value1),
Value2 = TweakValue(item.Value2)
};
If you want to perform filtering first, you can get that to occur in the database by putting the filtering, ordering etc before the call to AsEnumerable()
. For example:
var query = from item in myContext
where item.Foo == bar
orderby item.Something
select new { item.Value1, item.Value2 };
var items = from item in query.AsEnumerable()
select new {
Value1 = TweakValue(item.Value1),
Value2 = TweakValue(item.Value2)
};
You don't need a loop, just another projection:
var items = myContext.Select(i => new {
Value1 = item.Value1,
Value2 = item.Value2
})
.AsEnumerable()
.Select(i => new {
Value1 = TweakValue(item.Value1),
Value2 = TweakValue(item.Value2)
});
Edit: Depending on what TweakValue
actually does, you can push the whole thing to the server. Riffing on your current example:
public Expression<Func<Item, ItemProjection>> TweakValue()
{
return item => new ItemProjection
{
Value1 = item.Value1,
Value2 = item.Value2 + 0 // or something else L2E can understand...
};
}
Now use it like:
var exp = TweakValue();
var items = myContext.Select(exp);
Note I'm storing exp
in a variable so that L2E doesn't try to directly invoke TweakValue
in the query, which would fail.
Naturally, this only works if TweakValue
does stuff that L2E can do.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With