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

This post dates back to 2012 and it is migrated from my old blog.

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:

try
{
  using (var context = new IncomeContext())
  {
    var initializer = new DropCreateDatabaseAlways<IncomeContext>();
    initializer.InitializeDatabase(context);
              
    var sum = context.Incomes.Sum(income => income.Amount);
  }
}
catch (Exception exception)
{
  Console.WriteLine(exception.ToString());
}

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.

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
  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:

try
{
  using (var context = new PersonContext())
  {
    var initializer = new DropCreateDatabaseAlways<PersonContext>();
    initializer.InitializeDatabase(context);
              
    var sum = context.Incomes
      .Sum(income =>  (decimal?)income.Amount) ?? 0;
  }
}
catch (Exception exception)
{
  Console.WriteLine(exception.ToString());
}