Linq To Entities: Queryable.Sum returns Null on an empty list

20 12 2012

In this article I will show you that when using Queryable.Sum with Entity Framework and Linq to Entities to sum up a list of decimal values (or other value type) like in the following snippet you could get the exception listed below if the list is empty:

	using (var context = new IncomeContext())
		var initializer = new DropCreateDatabaseAlways<IncomeContext>();
		var sum = context.Incomes.Sum(income => income.Amount);
catch (Exception exception)

If you run this code on an empty table you will get the following exception:

System.InvalidOperationException: The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.
   at System.Data.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
   at lambda_method(Closure , Shaper )
   at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
   at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Sum[TSource](IQueryable`1 source, Expression`1 selector)

In my example I suppose that you already used Entity Framework Code First.
I explicitly invoke a DatabaseInitializer to be sure to create a freshly new database.
I previously created a DbContext with a single Dbset of Incomes entities:

public class IncomeContext : DbContext
	public DbSet<Income> Incomes { get; set; }

I created an Income entity:

public class Income
    public long Id { get; set; }

    public decimal Amount { get; set; }

The Linq query we coded at the beginning of the post is translated into the following SQL query and such SQL query will return null if no t-uple is found matching the filtering criteria.

[GroupBy1].[A1] AS [C1]
	SUM([Extent1].[Amount]) AS [A1]
	FROM [dbo].[Incomes] AS [Extent1]
)  AS [GroupBy1]

If we want to avoid the exception we need to cast to decimal? the return value of the lambda expression.
In that way the overload resolution will end up with the Sum overload returning a decimal?.
At this point you can work with the result as you want. With a coalesce operator for example:

	using (var context = new PersonContext())
		var initializer = new DropCreateDatabaseAlways<PersonContext>();
		var sum = context.Incomes
			.Sum(income =>  (decimal?)income.Amount) ?? 0;
catch (Exception exception)



3 responses

21 12 2012
Maurizio Tammacco

I don’t like and I don’t agree with your solution. If your query may return null for that field is much better to change the type of your domain class property to correctly reflect the business rule, and thus you avoid the cast

22 12 2012

Hi Maurizio,
the case here is when for your domain class is not reasonable at all to have a nullable amount (decimal).
Summing decimals in C# cannot return a null value.
The problem is with the Linq To Entities provider that does not handle the fact that the SQL sum operator returns null for empty sequences.
The solution I propose is one that I found somewhere else, I cannot remember where but googling today I found a couple of big voices proposing the same solution:

31 03 2013

It works for me, thank…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: