Using FirstOrDefault() in query

Question

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

My query:

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

If the query on the first table (Table1) doesn't return an object the code throws an exception. Is there a way to make it return just null?

Solution 1

Try a SelectMany on Table2, without the intermediate FirstOrDefault():

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

Also, you might want to use SQL Profiler to check the SQL that is being sent by EF. I believe the query as constructed in your question will result in two queries being sent to the database; one for each FirstOrDefault().

Solution 2

You could build your own helper function, that takes IEnumerable

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

This would effectively return an empty list, which, when called upon, providing your code in your Object property can handle nulls, won't bomb out, cause you'll just be returning a 0 item collection, instead of a null.

Solution 3

Only the first query with Where is a database query. As soon as you apply a "greedy" operator like FirstOrDefault the query gets executed. The second query is performed in memory. If Object.Table2 is a collection (which it apparently is) and you don't have lazy loading enabled your code will crash because the collection is null. If you have lazy loading enabled a second query is silently executed to load the collection - the complete collection and the filter is executed in memory.

You query should instead look like @adrift's code which would really be only one database query.