Using Function in Select Clause of Entity Framework Query

Question

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.

Answer 1

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)
            };

Answer 2

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.