C# method return versus variable in a lambda

Question

So I was fighting some LINQ (entity framework) lambda which I ultimately fixed by storing the result in a variable. Something like:

IQueryable<object> DoStuff() { return /* some linq query */; }

void Main()
{
    var result = DoStuff();
    // (_database is DbContext) == true

    // Works fine
    _database.Table.Where(x => result.Contains(x.Id));

    // Throws "LINQ to Entities does not recognize the method '[...] DoStuff()' method, and this method cannot be translated into a store expression."
    _database.Table.Where(x => DoStuff().Contains(x.Id));
}

I was able to solve my problem (by accident) by just storing the query in a separate variable but I am curious as to why that works? Is this a deferred execution thing? Before this, I always thought that the two where exchangeable (method vs intermediate variable usage) for reference types but obviously they are not.

What were you fighting? What was wrong with the original code? — Yuval Itzchakov
– Yuval Itzchakov, Commented Oct 12, 2015 at 7:42
@YuvalItzchakov I started with the line that threw the exception and couldn't understand why. I think it took me two hours of fiddling and the help of a colleague to reach the solution by accident. — Nebula
– Nebula, Commented Oct 12, 2015 at 8:25

Rico Suter · Accepted Answer · 2015-10-12 07:42:44Z

9

In LINQ to SQL, EF, etc., the lambda in the Where() method is transformed into an SQL query before it is executed. If you use own methods, this transformation fails, because the method DoStuff() is not known in SQL. If you get the result "outside" the LINQ lambda, then the SQL query is created with a static value and thus works...

answered Oct 12, 2015 at 7:42

Rico Suter

11.9k6 gold badges69 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Timothy Groote Over a year ago

Am i seeing this right? is this answer scoring higher than Jon's? :\ (not that this is a bad answer, far from it. just amazed, that's all)

Yuval Itzchakov Over a year ago

@TimothyGroote Why are you so surprised?

Nebula Over a year ago

Good answer! Very to the point. I was looking for some more thorough technical background on this issue so I'm afraid Jon's answer has my preference. There's worse people to lose a vote to I guess ;-)

Jon Skeet · Accepted Answer · 2015-10-12 07:49:05Z

Sure - it's a matter of understanding what happens to the lambda expression.

For LINQ to SQL, EF etc, it's converted into an expression tree - basically data that represents the code in the lambda expression. The LINQ provider then has to convert that data into SQL. How can you turn a call to DoStuff() into SQL? You can't, because DoStuff() doesn't exist within the database, and the provider doesn't know what the method does to try to emulate it within SQL itself. The very limited set of method calls that do work are effectively hard-coded into the LINQ provider, along with the translations into SQL.

In theory, a particular smart LINQ provider could spot the call to DoQuery, analyze the IL within it to work out what it does, and check whether it understands everything within it, and convert it to SQL. I don't know any LINQ provider which does this, however, and I'd be quite surprised to see one which can do it reliably including further method calls within DoStuff. It would be a mammoth amount of work, and you wouldn't want it to happen at execution time anyway. (It would be awesome to see as a proof of concept, of course...)

Now, even with LINQ to Objects you'd have different behaviour between your two pieces of code. In the working code, DoStuff() is executed once, then the result used for a comparison against each element in the sequence. In the failing code, you'd be calling DoStuff() once per element in the sequence. It would still work, but you could end up executing queries multiple times unnecessarily. Note that here the transformation of the lambda expression is different - the compiler will use a delegate conversion instead of an expression tree conversion.

In your particular case, there may be even more going on - because your method returns IQueryable<object>, it's highly likely that what's returned from the method won't even have executed as a query yet - but assuming it's a LINQ query against the same database with the same provider, the LINQ provider will "understand" the query returned by DoStuff() and work out one SQL query including both the query part in DoStufF() and the use of it in Main. Have a look at the logs of what SQL is being executed to validate this.

Great answer (how could I expect anything else...)! Exactly the amount of technical detail I was searching for. What I take away from this is that I should always store "linq method" results in a separate variable before using them in another lambda. Now if you'll excuse me, I'll be reading up on expression trees.

Collectives™ on Stack Overflow

C# method return versus variable in a lambda

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related