Why LINQ to Entity Sum Returns Null for Empty Result Set

linq

There are quite a few questions on Stack Overflow about the Linq to Entity / Linq to SQL Sum extension method, about how it returns null when the result set is empty: 1, 2, 3, 4, 5, 6, 7, and many more, as well as a blog post discussing the issue here. I feel it is an inconsistency in the Linq implementation.

I am assuming at this point that it is not a bug, but is more or less working as designed. I understand that there are workarounds (for example, casting the field to a nullable type, so you can coalesce with ??), and I also understand that for the underlying SQL, a NULL result is expected for an empty result set.

But because the result of the Sum extension for non-nullable types is also non-nullable, why does the Linq to SQL / Linq to Entity Sum behave this way?

Best Answer

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.

Related Solutions

The most effective order to learn SQL Server, LINQ, and Entity Framework

You are correct - you should learn SQL Server either before or along with the other skills. I've seen plenty of entity-framework queries blow up because SQL Server couldn't handle the SQL that was passed to it.

I believe we should use the right tool for any job. Often, SQL Server is the best tool to perform a particular task.

It's true that "he who has a hammer treats everything like a nail". If you only learn LINQ-to-Entities, you'll occasionally use LINQ-to-Entities when you should have used a stored proc on SQL Server.

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

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()).