Entity Framework and lambda expression tree (deep null coalescing)

Question

var articles = context.Articles.Where(a => a.Id != articleId)
.OrderBy(p => p.Categories.OrderBy(q => q.Name).FirstOrDefault().Name).ToList();

i get message of possible NullReferenceException which is correct.

So I make

var  articles = context.Articles.Where(a => a.Id != articleId)
                               .OrderBy(p =>
                                   (p.Categories.OrderBy(q => q.Name).FirstOrDefault() != null
                                    ? p.Categories.OrderBy(q => q.Name).FirstOrDefault().Name
                                    : null))
                               .Skip(page * pageSize)
                                  .Take(pageSize)
                                  .ToList();

which works but statement is calling two times and can be slow so i try to make

var articles = context.Articles.Where(a => a.Id != articleId)
             .OrderBy(p =>
             {
                 var firstOrDefault = p.Categories.OrderBy(q => q.Name).FirstOrDefault();
                 return firstOrDefault != null ? firstOrDefault.Name : null;
             }).ToList();

but i get

lambda expression with a statement body can not be converted to an expression tree.

What can i do? Ss first example correct even if i call two times p.Categories.OrderBy(q => q.Name).FirstOrDefault().

I am thinking that this can be slow. I have 200k rows in database.

Is it essential for you to be using `lambda` to that extent? It might be easier to solve your solution and make the code more readable by re-factoring out some of the `lambda` expressions. — Samuel Slade, Dec 04 '11 at 11:18

Ani · Accepted Answer · 2011-12-04T11:59:43.157

i get message of possible NullReferenceException which is correct.

It isn't clear which system is producing this message. Resharper?

Anyway, in this case, if this really is LINQ to Entities, the warning is spurious. LINQ to Entities performs automatic 'deep null coalescing' in many cases, and this is one such case.

In your original query:

var articles = context.Articles
                      .Where(a => a.Id != articleId)
                      .OrderBy(p => p.Categories
                                     .OrderBy(q => q.Name)
                                     .FirstOrDefault()
                                     .Name)
                       .ToList();

...there will be no NullReferenceException if an article has no category associated with it. Instead, the ordering value will be taken as null for such articles (meaning articles with no categories at all will turn up first), which it appears is precisely what you want. So no extra effort is required from your side!

Do note that with other LINQ providers (such as LINQ to Objects), the behaviour can be quite different, and .FirstOrDefault().XXX is indeed a risky expression.

On another note, don't prematurely optimize. If you already have a working solution, benchmark it. If it's too slow, investigate why - in this case, the clues are in the generated SQL. The LINQ to Entities query optimizer can often be smarter than you think. :)

Yes, this is correct answer. Linq-to-entities will interpret the first query correctly. — Ladislav Mrnka, Dec 04 '11 at 11:58
Yes resharper give me this message. so message is irrelevant. why then resharper give that message. — senzacionale, Dec 04 '11 at 13:49
@senzacionale: Resharper probably (currently) isn't smart enough to take the idiosyncrasies of the LINQ provider into account. In the general case, the warning is correct - it just can't happen in this case. If required, you can disable the warning with a comment (it provides you with the option to do that). — Ani, Dec 04 '11 at 15:07

score 2 · Answer 2 · answered Dec 04 '11 at 11:55

Your second example will indeed produce much more complex SQL query but it is up to database if this query will really be slower. Anyway you can simply change your first query little bit and it should work as expected without that warning:

var articles = context.Articles
                      .Where(a => a.Id != articleId)
                      .OrderBy(p => p.Categories
                                     .OrderBy(q => q.Name)
                                     .Select(q => q.Name)
                                     .FirstOrDefault())
                      .ToList();

The issue in your query is selecting Name after you call FirstOrDefault so if you project the result prior to call FirstOrDefault it should not produce the warning but there will be additional subselect in resulting SQL.

Btw. @Ani's answer is correct.

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

This appears to be a duplicate answer: "A lambda expression with a statement body cannot be converted to an expression tree" It would appear that you can't use the block of code denoted by the curly braces.

edited May 23 '17 at 11:43

Community

1
1

answered Dec 04 '11 at 11:23

Samuel Slade

8,405
6
33
55

1

LINQ to Entities cannot convert a custom method-call to equivalent SQL. – Ani Dec 04 '11 at 11:28
Fair enough - I didn't realise it was using the Entity Framework. – Samuel Slade Dec 04 '11 at 11:31

Entity Framework and lambda expression tree (deep null coalescing)

3 Answers3