locked
Sum() on empty table throws InvalidOperationException

    Question

  • Hi,

     

    when trying to sum a decimal column (NOT NULL) in LINQ to Sql:

     

    bool sumZero = _dataAccess.GetTable<TestTable>().Sum(p => p.TestDecimalColumn) == 0;

     

    when the table is empty i´m getting this exception:

     

    InvalidOperationException

    The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.

     

    I´m expecting Sum() on an empty table should return 0 and not null as the Exception shows...

     

    or is this behaviour by design?

     

    regards

    eiro

    Tuesday, August 28, 2007 1:33 PM

Answers

  • In SQL, Sum() aggregate operator returns null for an empty set. So this is as designed.

     

    Since users often rely on LINQ to SQL producing the same results as an equivalent SQL query, we decided not to convert the null to zero as in case of LINQ to Objects. There are a number of cases where we considered forcing .NET/C#/VB semantics for consistency with LINQ to Objects vs retaining SQL semantics for consistency with SQL. In most cases, we favored the latter for consistency and more enforceable and performant implementation,

     

    Thanks,

    Dinesh

    Tuesday, August 28, 2007 10:39 PM

All replies

  • In SQL, Sum() aggregate operator returns null for an empty set. So this is as designed.

     

    Since users often rely on LINQ to SQL producing the same results as an equivalent SQL query, we decided not to convert the null to zero as in case of LINQ to Objects. There are a number of cases where we considered forcing .NET/C#/VB semantics for consistency with LINQ to Objects vs retaining SQL semantics for consistency with SQL. In most cases, we favored the latter for consistency and more enforceable and performant implementation,

     

    Thanks,

    Dinesh

    Tuesday, August 28, 2007 10:39 PM
  • But eventhough I assign the .Sum(p=>p.Amount) to a Nullable or even an Object type and var, it complains in runtime about beeing null. p.Amount is of type System.Double, but why should that be a problem here?

     

    Code Snippet

    The null value cannot be assigned to a member with type System.Double which is a non-nullable value type.

     

     

    Code Snippet

    SELECT SUM([t0].[Amount]) AS [value]
    FROM [dbo].[Transactions] AS [t0]
    WHERE ([t0].[OwnerId] = @p0) AND ([t0].[TransactionTypeId] = @p1) AND ([t0].[PaymentTypeId] = @p2) AND ([t0].[TransactionDate] >= @p3) AND ([t0].[TransactionDate] <= @p4)

     

     

    Code Snippet
                Nullable<double> sum = rows.
                    Where(t => t.PaymentTypeId == 1).
                    Where(t => t.TransactionTypeId == 3).
                    Where(t => t.OwnerId == 2).
                    Sum(t => t.Amount);

     

     

     

    Thursday, September 06, 2007 11:50 AM
  • JonasE:

    You can work around the issue by casting to the appropriate nullable type (although the resulting SQL could be a little nicer):

    Code Snippet

    double? sum = rows.
                    Where(t => t.PaymentTypeId == 1).
                    Where(t => t.TransactionTypeId == 3).
                    Where(t => t.OwnerId == 2).
                    Sum(t => (double?)t.Amount);

     

    Or alternatively you can implement your own extension methods that map to the nullable Sum overloads.

    Though I still think that Sum() should behave like the rest of the LINQ framework and not SQL. After all, LINQ should unify various data access technologies, but these for example lead to different results:

    Code Snippet

    IQueryable<MyEntity> source = ...

     

    var val1 = source.Sum(d=>d.Number);

    var val2 = (source as IEnumerable<MyEntity>).Sum(d=>d.Number);

     

     

    It's really easy to mix-up IQueryable and IEnumerable along the way (it only takes a mis-typed method), which in this case not only incurs a performance hit, but also different behavior that is only noticed at run-time, there's no compiler check, whereas the workaround above enforces the dev. to handle (or deliberately ignore) the case when the result is null.

     

    Regards,

    B.G.

    Friday, September 07, 2007 2:52 AM
  • It still is a problem, because by default Sum() will return a data type the same as the item you are doing the sum over.  So when you do sum(t=>t.Amount) and Amount is a decimal, Sum will return a non nullable decimal when using it as an anonymous type and throw an error.  If the case is that it returns null just like SQL then when using an anonymous type it should return decimal?  this is a problem.
    Thursday, May 06, 2010 1:37 AM