CODEWITHSUNDEEP
c#entity-frameworklinq
Avrohom Yisroel
Avrohom Yisroel

Reputation: 9440

How do I rewrite this Linq query to avoid using FirstOrDefault?

I have a Quotes table, which has an associated Revisions table. A basic business rule is that a quote must have at least one revision, but may have many. I have a query that starts like this...

var revisions = ctx.Quotes
  .Select(q => q.Revisions.OrderByDescending(r => r.RevisionNumber).FirstOrDefault())
  // Do things with the revisions here...

The purpose is to get the latest revision of each quote, and then select some information from them.

This works fine, except that we had a rogue quote in the database that didn't have any revisions. Somewhere deep down in the code below what's shown above, I got an exception...

The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

This took an age to debug, as we didn't realise it was caused by the rogue quote. Ideally, the second line of the query would have used First() instead of FirstOrDefault(), which would have thrown an exception right there, showing the source of the problem immediately. However, Entity Framework doesn't allow you to use First() or Single() mid-query, which is why we used FirstOrDefault().

Without rewriting the query completely, ie querying the Revisions table first and navigating back up to the Quote (which would be a pain for other reasons), is there a simple way to guard against this? In this case, I fixed it by changing the first line to...

var revisions = ctx.Quotes.Where(q => q.Revisions.Any())

...but this is a specific fix for this case, and was only apparent after we eventually found the issue. Ideally, I would like a solution that would be generally applicable.

Upvotes: 3

Views: 1298

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

To get an inner join semantics, IMO the general replacement of Select / OrderBy / FirstOrDefault is SelectMany / OrderBy / Take(1):

var revisions = ctx.Quotes
    .SelectMany(q => q.Revisions.OrderByDescending(r => r.RevisionNumber).Take(1))
    // Do things with the revisions here...

Upvotes: 7

Related Questions