2

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?

Community
  • 1
Hannele
  • 135
  • 1
  • 7

1 Answers1

3

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.

Community
  • 1
Eric King
  • 10,876
  • 3
  • 41
  • 55
  • 1
    Then how come it doesn't explicitly return a nullable value? – Hannele Aug 20 '14 at 18:27
  • 5
    So then the question is why does SQL return null? An empty set is not "an unknown value," it's a set containing zero elements, and the proper sum of this is zero. This is well-understood, clearly-defined math. What's the rationale for the DBMS returning a mathematically incorrect result in this case? – Mason Wheeler Aug 20 '14 at 18:38
  • @MasonWheeler That's a good (and different) question. I don't know why SQL would return NULL in this case. I'm just assuming that's the case anyway, based on the posts linked to in the question, because I've not personally come across that situation any time in recent memory. I just know that EF and LINQ2SQL behave the way I would expect, and consistent with other .NET code. – Eric King Aug 20 '14 at 18:47
  • 2
    TSQL Aggregate Functions (like SUM()) also ignore NULL values in a set that contains them, while apparently returning NULL for an empty set. That seems odd to me, too. – Eric King Aug 20 '14 at 18:54
  • From a practical standpoint, it might be useful to distinguish between zero (being a perfectly valid result for some non-empty set) and null (indicating an actual empty set), even if 0 is a perfectly reasonable result for an empty set. – Robert Harvey Aug 20 '14 at 19:58
  • 1
    It's inconsistent with other .NET framework implementations - Linq to Object will return 0 if the list is empty. This has also been presented as a workaround in some places (casting to IEnumerable, then calling Sum), although it is inefficient if you only want the sum. – Hannele Aug 20 '14 at 20:03
  • Exactly, `Enumerable.Sum()` and `Enumerable.Sum()` return `0` for empty sequences, there is no reason for `Queryable.Sum` to be different. – NetMage Oct 09 '18 at 21:33
  • @NetMage That's just it, though. Queryable.Sum() **does return 0 on an _empty sequence_**. Exactly as would be expected. Try it yourself with something like `var numbers = new int[0].AsQueryable(); var sum = numbers.Sum();` But it does not (and should not) return 0 if the sequence is _null_. The problem isn't with Queryable, it's with SQL, which unexpectedly returns _null_ instead of an _empty sequence_. – Eric King Oct 10 '18 at 14:07
  • @EricKing I don't think `AsQueryable` is doing what you think it is. – NetMage Oct 10 '18 at 20:11
  • @NetMage Hmm... I think it [types an IEnumberable as IQueryable](https://docs.microsoft.com/en-us/dotnet/api/system.linq.queryable.asqueryable?view=netframework-4.7.2), allowing you to call the [System.Linq.Queryable.Sum](https://docs.microsoft.com/en-us/dotnet/api/system.linq.queryable.sum?view=netframework-4.7.2#System_Linq_Queryable_Sum_System_Linq_IQueryable_System_Int32__) extension method on the collection. What do you think it does? – Eric King Oct 10 '18 at 20:46
  • I think it [converts](https://referencesource.microsoft.com/#System.Core/System/Linq/IQueryable.cs,080010ec4b6beced) the `IEnumerable` to an `EnumerableQuery` then calls `Queryable.Sum` which calls [`EnumerableQuery.Provider.Execute`](https://referencesource.microsoft.com/#System.Core/System/Linq/SequenceQuery.cs,109) which calls [`EnumerableExecutor.Execute`](https://referencesource.microsoft.com/#System.Core/System/Linq/SequenceQuery.cs,6bf428aa1f6b9002) which uses [`EnumerableRewriter`](https://referencesource.microsoft.com/#System.Core/System/Linq/SequenceQuery.cs,66d9d0e16cf8e5c5) – NetMage Oct 10 '18 at 21:04
  • 1
    to convert the `Queryable.Sum` call to an `Enumerable.Sum` call and then calls it to do the sum. So `Enumerable.AsQueryable().Sum()` is just a long way to call `Enumerable.Sum()` when you aren't using an actual database provider based `IQueryable`. Which means calling `Queryable.Sum` on an empty sequence behaves differently based on `Provider`, which seems like a bad design decision. – NetMage Oct 10 '18 at 21:04
  • @NetMage Which is exactly why I say the unexpected behavior is NOT in `Queryable.Sum`, but in the provider. `Queryable` behaves consistently and exactly as you would expect it to work and is not the "breaking" part of this issue. The key to understanding why LINQ2SQL and EF work they way they do is to understand what the LINQ part is doing separate from what the Provider is doing. I don't see it as a bad design decision at all... there is really nothing Queryable can do any differently. It is at the mercy of the provider. – Eric King Oct 10 '18 at 22:09
  • If `Queryable.Sum` ended with `?? 0` then the provider wouldn't matter, the defined API would be that `Sum` never returns `null`. – NetMage Oct 10 '18 at 22:42