views:

795

answers:

4

I have this code (ok, I don't, but something similar :p)

    var dogs = Dogs.Select(ø => new Row
    {
            Name = ø.Name,
            WeightOfNiceCats = ø.Owner
              .Cats
              .Where(æ => !æ.Annoying)
              .Sum(æ => æ.Weight),
    });

Here I go through all dogs and sum up the weight (into a non-nullable decimal) of all not-annoying cats which has the same owner as the dog. Of course, pretty much all cats are annoying, so I get this error:

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

None of the fields or foreign keys used can be null. So the error happens when the Where clause returns no cats, which it often does. But how can I solve this? I want it to return 0 when that happens. Tried with a DefaultIfEmpty() after the Where clause, but then I get this error:

Object reference not set to an instance of an object.

Which I guess is understandable. I tried to add a ?? after the Sum, but then it wont compile because of this error:

Operator '??' cannot be applied to operands of type 'decimal' and 'decimal'

Which also makes sense of course. So what can I do? Would be nice if the Sum thing just returned 0 when there was nothing to sum. Or a SumOrZero statement of some sort. Would it be difficult to make a SumOrZero method that worked with Linq2SQL?

+2  A: 

The trick you've already given (.Sum(æ => (decmal?) æ.Weight) ?? 0.0M) is about the best I can think of for this scenario when it executes as a query at the database. A bit annoying, but there are worse things...

Unlike LINQ-to-Objects, you can't just add your own extension method, as it needs to be mapped by the underlying provider.

Marc Gravell
exactly. that was what I feared, hehe.
Svish
+2  A: 

In LINQ to Objects it would be easy. With LINQ to SQL it will be harder. You could write your own extension method which called Queryable.Sum with an expression built up from the normal one, with a cast to the nullable type. I suspect you'd need to do the ?? 0m in the calling code though. In other words, you could do:

.SumOrNull(æ => æ.Weight) ?? 0M,

Where the signature for .SumOrNull would be:

public static decimal? SumOrNull<TSource, decimal>(this IQueryable<TSource>,
    Func<TSource,decimal> projection)

You could basically write that for all the value types supported in Queryable. You could write it generically, and call the appropriate method in Queryable using reflection, but that would be icky too.

I think you're best off with what you've got, to be honest.

Jon Skeet
Then I think I'll just leave it as I have..
Svish
A: 

You want to use DefaultIfEmpty, which will return an IEnumberable with a single element of 0, and that will be semantically the same as you require.

As you have a nullable decimal, you will probably have to use the 2nd version of the method taking 2 parameters.

leppie
Quite the opposite; the decimal is non-nullable. The *workaround* to the LINQ-to-SQL glitch is to make it nullable for computation.
Marc Gravell
Then it is even easier :)
leppie
DefaultIfEmpty caused the "Object reference not set to an instance of an object."
Svish
Well figure out where your logic is wrong. Or better, post the broken code so we can see where you are going wrong. NullReferenceException is your fault 99% of the time.
leppie
um, what are you talking about? I have? Both the code, and the different error messages. Even the one you are talking about. Have you even read my question?
Svish
You are using it wrong... You need to pass a non-null enumerable to DefaultIfEmpty, which you are not doing (hence the null exception). Not that hard, is it?
leppie
+2  A: 

This is what I ended up with for now:

.Sum(æ => (decmal?) æ.Weight) ?? 0.0M,

This works like a charm.

I would still prefer to have a SumOrZero I could use, which would work exactly like the regular Sum except it would never return null for any reason. Like the others have noted, this would be pretty easy to make for IEnumerable but a bit more icky to create for IQuearyable so it would work with Linq2SQL, etc. So I will just leave it at that for now. Although if someone is bored one day and do write a SumOrZero for IQueryables which works with Linq2SQL for all the numeric types, please do let me know :D

Svish