How to implement ISNULL(SUM(ColumnName),0) in Linq2SQL

I had a Linq2Sql statement like this:

var q = from o in db.OrderItem
                  where o.UserId == UserId
                  select o.Amount;

And then I was trying to get the sum of the OrderItem.Amount column by doing this:

total = q.Sum();

This worked fine as long as there were rows for this UserId in the OrderItem table.  But if there are no rows in the table for this UserId I was getting this exception:

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

Linq2Sql was creating the following SQL query:

SELECT SUM([t0].[Amount]) AS [value]
FROM [dbo].[OrderItem] AS [t0]
WHERE [t0].[UserId] = @p0

which is fine but I really want this:

SELECT ISNULL(SUM([t0].[Amount]),0) AS [value]
FROM [dbo].[OrderItem] AS [t0]
WHERE [t0].[UserId] = @p0

Notice the ISNULL in the statement above that will return 0 if there are no rows found for this UserId.

Eventually I was able to track it down to var q being an IQueryable in the Linq2Sql statement above.  By using ToArray() like this:

var q = (from o in db.OrderItem
                  where o.UserId == UserId
                  select o.Amount).ToArray();
total = q.Sum();

var q is now an IEnumerable (because ToArray() returns an IEnumerable<Decimal>).  And instead of IQueryable.Sum() trying to apply an expression to the Null value,   IEnumerable.Sum() will just loop through all of the items in the array and sum the values.  In this case there are no items in the array so 0 is returned. 

I could have also explicitly declared q as an IEnumerable<Decimal>:

IEnumerable<Decimal> q = from o in db.OrderItem
                  where o.UserId == UserId
                  select o.Amount;
total = q.Sum();

Just to note, the SQL statement is still not using ISNULL() but I am getting the result I want from within C#.

 

UPDATE (2010-07-22):  Another solution from RichardD:

var q = from o in db.OrderItem 
          where o.UserId == UserId 
          select o.Amount;
Decimal total = q.Sum(a => (Decimal?)a) ?? 0M; 

OR 

IQueryable<Decimal?> q = from o in db.OrderItem
                  where o.UserId == UserId
                  select (Decimal?)o.Amount;
Decimal total = q.Sum() ?? 0; 

 

 

6 Comments

  • This is a known issue in LINQ to SQL: [1]

    "The Standard Query Operator aggregate operator Sum evaluates to zero for an empty sequence or a sequence that contains only nulls. In LINQ to SQL, the semantics of SQL are left unchanged. For this reason, Sum evaluates to null instead of to zero for an empty sequence or for a sequence that contains only nulls."

    Unfortunately, DefaultIfEmpty doesn't seem to work in L2S, so your best option is probably to cast to a nullable value:

    total = q.Sum(a => (decimal?)a) ?? 0M;

    [1] http://msdn.microsoft.com/en-us/library/bb386928.aspx

  • Hi RichardD,
    Casting to a nullable value does not work because the issue is not the summing the individual values but that there are no values to begin with. And you are correct, I tried q.DefaultIfEmpty().Sum() and it did not work. Using q.ToArray().Sum() or just casting q as IEnumerable was the only way I found to solve the problem.
    Thanks,
    -Jeff

  • You're sort of hurting yourself this way!
    .ToArray() will actually result in executing the query (before calling Sum) which will bring all the records (as IEnumberable as you mention) from the database and you can do Sum() or whatever on them after that. But instead of a single value returned from the database, you return all the numbers for Go-only-will-know how many rows instead.

  • Jeff,

    Casting to Nullable *does* work.

    If there are no values to sum, the return value is null. The return type of Sum() is decimal, which can't accept a null value, and throws the InvalidOperationException you saw.

    The return type of Sum<Nullable>() is Nullable, which can accept a null value.

    public static decimal? Sum(this IQueryable source);

    If you don't believe me, just try it!

  • Hi RichardD,
    Interesting... I thought I had tried it and it did not work but I tried again and it does work:
    var q = (from o in db.OrderItem
    where o.UserId == UserId
    select o.Amount);
    Decimal total = q.Sum(a => (Decimal?)a) ?? 0M;

    And then even being more explicit this also works:
    IQueryable q = (from o in db.OrderItem
    where o.UserId == UserId
    select (Decimal?)o.Amount);
    total = q.Sum() ?? 0;

    Thanks!
    -Jeff

  • var q = from o in db.OrderItem
    where o.UserId == UserId
    select o.Amount;
    decimal total =0;
    if(q.count() >0) total =q.sum();

Comments have been disabled for this content.