Entity Framework and lambda expression tree (deep collapse)

Question

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

I get a message about a possible NullReferenceException that is correct.

So i do

 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 the statement calls twice and can be slow, so I'm trying to do

 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

A lambda expression with an operator body cannot be converted to an expression tree.

What can I do? Ss the first example is correct even if I call p.Categories.OrderBy(q => q.Name).FirstOrDefault().

I think this may be slow. I have 200k rows in the database.

Answer 1

I get a message about a possible correct NullReferenceException.

It is not clear which system generates this message. Resharper?

In any case, in this case, if it is really LINQ for Entities, the warning is false. LINQ to Entities in many cases performs automatic "deep zero collapse", and this is one such case.

In the original request:

 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 the article does not have a category associated with it. Instead, the ordering value will be taken as null for such articles (this means that articles in which there are no categories at all will be displayed first), and this looks exactly the way you want. Therefore, no additional effort is required on your part!

Note that with other LINQ providers (such as LINQ to Objects), the behavior can be completely different, and .FirstOrDefault().XXX indeed a risky expression.

On the other hand, prematurely optimize. If you already have a working solution, compare it. If this is too slow, find out why - in this case the keys are in the generated SQL. LINQ to Entities query optimizer can often be smarter than you think. :)

Answer 2

The second example will actually produce a much more complex SQL query, but it depends on the database if that query is really slower. In any case, you can just change your first request a bit, and it should work as expected without this 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 problem in your query is to select Name after calling FirstOrDefault , so if you project the result before calling FirstOrDefault , it should not raise a warning, but an additional sub-screen will be added as a result of SQL.

Btw. @ The answer to the question is correct.

Answer 3

This seems to be a duplicate of the answer: "A lambda expression with an operator body cannot be converted to an expression tree." Apparently you cannot use the code block indicated by curly braces.