C# – Null or empty object when LINQ to Entities query returns nothing

clinq

Say I have a LINQ query like this:

application = CreditDatabase
                        .Applications
                        .Select(Mapper.Map<Application>) 
                        .Where(c => c.uID == urID)
                        .DefaultIfEmpty().First();

It returns null if the LINQ query returns an empty result set. Is this "correct". Is it better to return an empty object? If so then how can I do that? This link says it is not possible to return a non-null reference type: https://msdn.microsoft.com/en-us/library/bb360179(v=vs.110).aspx

Best Answer

Database queries return result sets. An empty set is a reasonable answer; it means you don't have any of the things searched for.

I would argue that your code is going to far in canonicalization of the result.

Your code (somewhere shortly after this line) is going to check to see if the application you just searched for exists or not, so you are not preventing the subsequent conditional logic (not shown in your question) in your code by doing .FirstOrDefault().

You can — and I argue should — instead: check for the empty set (i.e. use the size of the result set, maybe with .Any()) to see if the application exists. That is substantially cleaner than converting the empty set to default and then taking the first and finally later checking that for null to see if the application of interest exists.

Rather than going this far:

application = CreditDatabase
                    .Applications
                    .Select(Mapper.Map<Application>) 
                    .Where(c => c.uID == urID)
                    .DefaultIfEmpty().First();
if ( application == null ) {
   // handle missing application, maybe add it...
} else {
    // use the found application
}

Omit the default handling; instead:

search = CreditDatabase
                    .Applications
                    .Select(Mapper.Map<Application>) 
                    .Where(c => c.uID == urID);
if ( ! search.Any () ) {
    // handle missing application, maybe add it...
} else {
    application = search.Single ();
    // use the found application
}

Addendum: In the above application = search.Single (), I have used .Single() instead of .First() since excepting if the non-empty result set has more than one match makes sense here: it is a lookup by what we presume is an exact match of unique primary key (otherwise as @JacquesB points out, since the query isn't ordered, we don't really know what we're keeping and what we're throwing away with .First()).

Related Solutions

Entity Framework – Nesting Linq-to-Objects Query within Linq-to-Entities

Presumably the data provider looks at each sub-expression and if it can be computed it just does a compile and execute while it's constructing the query. This would be no different than saying:

where c.ContactID == (4 + 5)

That's because numbers in your example is an int[] type.

Interestingly this is only evaluated once before the query is generated, so it's only calculated once. If you did something like this:

where c.ContactID == myObject.NextID()

...then it evaluates myObject.NextID() during the construction of the query, so it only calls it once. I have witnessed, in Linq2NHibernate, that not only does it only call it once during the creating of the query, but the data provider has built-in query caching, and it won't re-evaluate it the next time you execute the same query. YMMV.

Also remember it's lazy evaluated, so if you never execute the query, the subexpression is never compiled and executed. Also, if you modify numbers after your statement but before executing the foreach then the query will be built using your modified value.

Edit:

Here's an article called Walkthrough: Creating an IQueryable LINQ Provider. Check the section on "Adding the Expression Evaluator". Specifically the Nominator class:

/// <summary> 
/// Performs bottom-up analysis to determine which nodes can possibly 
/// be part of an evaluated sub-tree. 
/// </summary>

Basically it's searching the expression tree for anything that can be evaluated, and compiling and executing it, then replacing that sub-expression with a constant expression.

Here's the function that determines if an expression can be evaluated:

private static bool CanBeEvaluatedLocally(Expression expression)
{
    return expression.NodeType != ExpressionType.Parameter;
}

So as long as no sub-expression contains a parameter of the expression, then it can be evaluated.

Edit 2:

Based on your edit, it does look like it's putting the values from the array into a table in SQL and selecting MAX from that. I honestly have no idea why it would bother doing that when none of the elements of the sub-expression numbers.Max() are parameters. I certainly know that you can write an expression like numbers.Contains(c.ID) and that will actually create an IN clause such as c.ID IN (@p0, @p1, @p2, @p3, @p4) where @p0 = 1, @p1 = 2, etc.

So, to answer your question: "is it possible that Linq2Entities provider indeed doesn't execute non-Linq and Linq-to-Object methods, but instead creates equivalent SQL statements for some of them"

Yes, it's possible.

Why LINQ to Entity Sum Returns Null for Empty Result Set

The problem isn't that EF or LINQ2SQL return null for an empty set, it's that SQL returns null. Along those lines, what would you expect EF or LINQ2SQL to return when attempting to sum on a null value?

Generally, a null value is not a candidate for mathematical functions. Null usually means something along the lines of "unknown value" and is not the same as zero (the default value of an int or decimal in .NET). You can't perform math on it.

As such, it would be improper for the framework to decide to always substitute a default value for a null value and proceed to give you a misleadingly precise sum. Instead, the framework properly complains that the sum operation is impossible (because of the null value) and it is left to the caller to decide what to do in the case of a null value, using the techniques you have described and linked to in your question.

Best Answer

Related Solutions

Entity Framework – Nesting Linq-to-Objects Query within Linq-to-Entities

Why LINQ to Entity Sum Returns Null for Empty Result Set

Related Topic