Using FirstOrDefault () in a query

Question

Is there a way to use FirstOrDefault() inside a complex query, but not throw an exception if it returns null?

My request:

 contex.Table1.Where(t => t.Property == "Value").FirstOrDefault() .Object.Table2.Where(t => t.Property2 == "Value2").FirstOrDefault(); 

If the query in the first table (Table 1) does not return an object, the code throws an exception. Is there a way to make it return only null?

Answer 1

Try SelectMany on Table2 , without intermediate FirstOrDefault() :

 context.Table1.Where(t1 => t1.Property1 == "Value1") .SelectMany(t1 => t1.Table2.Where(t2 => t2.Property2 == "Value2")) .FirstOrDefault(); 

Alternatively, you can use SQL Profiler to validate the SQL sent by EF. I believe that the query built in your question will send two queries to the database; one for each FirstOrDefault() .

Answer 2

You can create your own helper function that accepts IEnumerable

 public static TSource CustomFirstOrDefault<TSource>(this IEnumerable<TSource> source) { return source.FirstOrDefault() ?? new List<TSource>(); } 

This will lead to an efficient return of the empty list, which when called when your code in the Object property can handle NULL values ​​will not bomb, because you will simply return a collection of elements 0 instead of zero.

Answer 3

Only the first query with Where is a database query. As soon as you apply a greedy statement like FirstOrDefault , the request will be executed. The second request is executed in memory. If Object.Table2 is a collection (which, apparently, is) and you don't have lazy loading, your code will crash because the collection is null . If you have lazy loading enabled, the second query is executed without the slightest effort to load the collection - the complete collection and filter are executed in memory.

Instead, the query should look like @adrift code, which will really be just one database query.