Entity Framework exception from Sum over an empty set

The sum of an empty set is zero. This is a well-known mathematical truth that the .NET Framework understands.

int sum = Enumerable.Empty<int>().Sum();
Assert.AreEqual(0, sum);

Entity Framework 1.0, however, doesn’t work like that. If you try to sum an empty set, it will throw an exception.

OrderEntities entities = new OrderEntities();
int totalQuantity = entities.OrderLine
    .Where(orderLine => false)
    .Sum(orderLine => orderLine.Quantity);
Assert.AreEqual(0, totalQuantity);

System.InvalidOperationException: The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Here’s my solution

Let Sum return a nullable integer. Then, if it is null, coerce it back to zero.

OrderEntities entities = new OrderEntities();
int totalQuantity = entities.OrderLine
    .Where(orderLine => false)
    .Sum(orderLine => (int?)orderLine.Quantity) ?? 0;
Assert.AreEqual(0, totalQuantity);

You know that Quantity cannot be null. I know that Quantity cannot be null. But we have to trick Entity Framework into thinking that it could be null. By casting the integer to int?, you select the Sum overload that allows for a null. Since Entity Framework doesn’t do the right thing with that null, then we’ll do it ourselves.

4 Responses to “Entity Framework exception from Sum over an empty set”

  1. Sam Says:

    Yeah, it's really annoying, same problem with LINQ to SQL. In the MSDN doco they state that this was deliberately done to align with T-SQL / SQL Server, which returns NULL for aggregates over an empty set. I presume they may have followed the same reasoning for Entity Framework. It deliberately goes against the LINQ to Objects implementation though.

    Definitely one of the more annoying leaks to come out of these two technologies - surely the framework could do this for us? The runtime knows what type we want back so if Sum() gets a value type and the underlying provider returns null, wouldn't it simply be a case of saying 'if (result == null) return default(T)' somewhere, instead of making the client code jump through nullable hoops?

  2. Jarod Ferguson Says:

    Thanks, exactly what I was looking for. Nice Work.

  3. Matt Says:

    Thanks for your post. This was just what I was looking for.

  4. David Gress Says:

    I could find any Linq to Entities sysntax in VB for this case. The soultion we came up with was to wrap the query in a try...catch.. end try and catch the InvalidOperationException and set the variable to zero.

Leave a Reply

You must be logged in to post a comment.