Apply ISNULL like SQL in LINQ

Question

I have the next LINQ where o.Value and p.Value are decimal types

from o in dbContext.Ammounts
where o.Value > (from p in dbContext.Payments select p).Sum(p => p.Value))

The inner LINQ from p in dbContext.Payments select p).Sum(p => p.Value) can be return a NULL value and I need to apply a ISNULL(linq_sentence, 0)

I try with this:

from o in dbContext.Ammounts
where o.Value > ((from p in dbContext.Payments select p).Sum(p => p.Value)) ?? 0m)

But I get this message error:

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

http://stackoverflow.com/questions/413084/equivalent-of-sql-isnull-in-linq Duplication !! — Superdrac, May 13 '14 at 14:05
The error message should be perfectly clear: you have a non-nullable `decimal` on the left side of the `??` operator, so you do not need the `??` there at all (or you need to change your mapping to tell LINQ2SQL that the column is nullable). — Sergey Kalinichenko, May 13 '14 at 14:05

score 0 · Accepted Answer · answered May 13 '14 at 14:13

When the collection is empty in Linq2SQL the aggregate returns null. If the value being aggregated is non nullable this will throw an exception. To solve the problem, cast the value being aggregated to a nullable type.

from o in dbContext.Ammounts
where o.Value > ((from p in dbContext.Payments select p)
                .Sum(p => (decimal?)p.Value)) ?? 0m);

More info: http://weblogs.asp.net/zeeshanhirani/archive/2008/07/15/applying-aggregates-to-empty-collections-causes-exception-in-linq-to-sql.aspx

Apply ISNULL like SQL in LINQ

1 Answers1