18

I have a Linq collection of Things, where Thing has an Amount (decimal) property.

I'm trying to do an aggregate on this for a certain subset of Things:

var total = myThings.Sum(t => t.Amount);

and that works nicely. But then I added a condition that left me with no Things in the result:

var total = myThings.Where(t => t.OtherProperty == 123).Sum(t => t.Amount);

And instead of getting total = 0 or null, I get an error:

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

That is really nasty, because I didn't expect that behavior. I would have expected total to be zero, maybe null - but certainly not to throw an exception!

What am I doing wrong? What's the workaround/fix?

EDIT - example

Thanks to all for your comments. Here's some code, copied and pasted (not simplified). It's LinqToSql (perhaps that's why you couldn't reproduce my problem):

var claims = Claim.Where(cl => cl.ID < 0);
var count = claims.Count(); // count=0
var sum = claims.Sum(cl => cl.ClaimedAmount); // throws exception
Edward Brey
  • 40,302
  • 20
  • 199
  • 253
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • 1
    Wow - that *is* really nasty! If that is the way Linq is defined with empty result sets, it was a sad choice by the language designers - as it will require EVERY use of an aggregate to be wrapped in a test for an empty set. – MtnViewMark Mar 16 '10 at 15:11
  • 2
    It would help if you showed the types explicitly. I just tried `new decimal[] { 1 }.Where(i => i != 1).Sum()` in LINQPad and got 0, as expected. – Craig Stuntz Mar 16 '10 at 15:20
  • @Craig Stuntz - I tested using something a bit more complex (a "Location" object (string Map, int Top, int Left) and it still worked for me - just like you said: List myThings = new List(); myThings.Add(new Location() { Map = "A", Top = 10, Left = 10 }); var total = myThings.Where(t => t.Map == "B").Sum(t => t.Top); – Fenton Mar 16 '10 at 15:30
  • 1
    @Sohnee, try it yourself: `new [] { new { Foo = 1m } }.Where(i => i.Foo != 1).Sum(i => i.Foo)` -- returns 0 as expected. – Craig Stuntz Mar 16 '10 at 15:31
  • 1
    There's nothing wrong with the code that Shaul has posted. He needs to post some code that's actually broken so that we can diagnose the real problem. – LukeH Mar 16 '10 at 15:32
  • OK, I can see this is more complex - I did oversimplify my code. Let me see if I can narrow it down from here... – Shaul Behr Mar 16 '10 at 16:45
  • 1
    Update posted - it seems the problem is in LinqToSql – Shaul Behr Mar 16 '10 at 17:07
  • OK. Now I can reproduce it. I'll have a solution for you in a second. – Craig Stuntz Mar 16 '10 at 17:27

4 Answers4

24

I can reproduce your problem with the following LINQPad query against Northwind:

Employees.Where(e => e.EmployeeID == -999).Sum(e => e.EmployeeID)

There are two issues here:

  1. Sum() is overloaded
  2. LINQ to SQL follows SQL semantics, not C# semantics.

In SQL, SUM(no rows) returns null, not zero. However, the type inference for your query gives you decimal as the type parameter, instead of decimal?. The fix is to help type inference select the correct type, i.e.:

Employees.Where(e => e.EmployeeID == -999).Sum(e => (int?)e.EmployeeID)

Now the correct Sum() overload will be used.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • +1 - and see my comment to @Leom Burke that I think this was a design mistake on Microsoft's part. It's obvious that the desired type is "decimal?", so forcing me to declare it explicitly is really dumb. – Shaul Behr Mar 16 '10 at 17:40
  • It's not clear what L2S should do with the `decimal` overload for `Sum`, given that it follows SQL semantics. The C# compiler, OTOH, certainly *should not* use the `decimal?` overload because some random LINQ provider might not follow C# semantics. The compiler is doing the right thing, since you have not given it any type hints. L2S's response is at least arguable. – Craig Stuntz Mar 16 '10 at 17:52
  • + answer credit - was wavering between you and @Leom Burke, coz you both answered it right. But you have given a lot more background and explanation, so you get the credit. Thanks! :) – Shaul Behr Mar 16 '10 at 18:05
5

To get a non-nullable result, you need to cast the amount to a nullable type, and then handle the case of Sum returning null.

decimal total = myThings.Sum(t => (decimal?)t.Amount) ?? 0;

There's another question devoted to the (ir)rationale.

Community
  • 1
  • 1
Edward Brey
  • 40,302
  • 20
  • 199
  • 253
2

it throws an exception because the result of the combined sql query is null and this cant be assigned to the decimal var. If you did the following then your variable would be null (I assume ClaimedAmount is decimal):

var claims = Claim.Where(cl => cl.ID < 0);
var count = claims.Count(); // count=0
var sum = claims.Sum(cl => cl.ClaimedAmount as decimal?);

then you should get the functionality you desire.

You could also do ToList() at the point of the where statement and then the sum would return 0 but that would fall foul of what has been said elsewhere about LINQ aggregates.

Leom Burke
  • 8,143
  • 1
  • 35
  • 30
-1

If t has a property like a 'HasValue', then I would change the expression to:

var total = 
     myThings.Where(t => (t.HasValue) && (t.OtherProperty == 123)).Sum(t => t.Amount);
DotNetWala
  • 6,574
  • 1
  • 18
  • 10