9

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.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

2 Answers2

13

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)
            };
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
5

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.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • Note that you can use projection initializers to make the first Select call slightly simpler; the C# compiler will default the property names in the anonymous type from the properties used to initialize them. – Jon Skeet Jun 21 '11 at 18:39
  • @Jon, agreed, provided that they are just simple property references. Since probably 50% of my EF queries are more complex, I don't tend to do that much as it creates a maintenance issue down the road -- the first time a given query requires more than a reference to properties of a single object, I have to rewrite the whole thing to use explicitly-named properties. – Craig Stuntz Jun 21 '11 at 18:44
  • @Craig: Why? You only need to rewrite the bits which need to do different things, after all. – Jon Skeet Jun 21 '11 at 19:18
  • @Jon: Because most of my L2E queries with projections eventually end up being non-trivial. IOW, "the bits which need to do different things" is most of the query. – Craig Stuntz Jun 21 '11 at 19:26
  • @Craig: Well, that's not quite what you said... you gave the impression that you rewrote the *whole* query as soon as *any* part of it needed to change, which you certainly don't need to do. Why not use it where you can, and just don't where you don't need to? You claim it creates a maintenance issue, but I can't see why... it's not like there are compatibility issues. – Jon Skeet Jun 21 '11 at 19:30
  • @Jon: I agree there are no compatibility issues. But it's, for me, a bit like how I always use `{}` with an `if`: Not strictly necessary for a single statement in the body, but since I know that most `if`s will eventually have > 1 statement, even when they don't start out that way, I do it by default. I accept that other people make different choices, but I don't think the "more concise" syntax actually saves any time, so I choose not to use it, mostly. – Craig Stuntz Jun 21 '11 at 19:39
  • @Craig: It's a reasonable decision not to use it - but claiming it creates a maintenance issue is somewhat different :) (I do the same with `if` statements, because that *does* create a maintenance issue - I've had to clean up bugs before now where there were two statements indented but not within an `if` block. I've never seen the equivalent due to projection initializers.) – Jon Skeet Jun 21 '11 at 19:41