Sum in Linq to Sql not returning a value

Question

I'm looking for a query to return the sum of all payments against an invoice ID - however, if there are no payments noted, the query below returns null. Is there any way of it returning 0 instead of null? I've tried adding ?? 0 at the end, but get the message Operator ?? cannot be applied to operands of type decimal and int

AmountAllocated is of type Decimal:

public decimal AmountAllocated { get; set; }

Thanks, Mark

The following returns null for Sum when there are no payment rows found:

 var invoiceVM = from i in db.Invoices
         where i.UserName==userName
         select new NewInvoiceViewModel
         {
           InvoiceId = i.InvoiceId,
           SumPayments = 
              db.PaymentInvoices.Where(pi => pi.InvoiceId == i.InvoiceId)
                                .Select(pi => pi.AmountAllocated).Sum()
                        };

The following results in the Operator ?? cannot be applied to operands of type decimal and int error:

  var invoiceVM = from i in db.Invoices
         where i.UserName==userName
         select new NewInvoiceViewModel
         {
           InvoiceId = i.InvoiceId,
           SumPayments = 
             db.PaymentInvoices.Where(pi => pi.InvoiceId == i.InvoiceId)
                                .Sum(pi => pi.AmountAllocated) ?? 0

                        };

If there have been payments made, then AmountAllocated correctly returns the sum of those payments - just it returns null if there are no payment rows found.

You can see from the screenshot below, the first record has a payment, and shows correct as 10 (decimal) - the second record has no payments, and shows as null (decimal).

screenshot

to use `??` operator your operand must be `Nullable`, for example `decimal?` or `int?` — RinatG, May 24 '13 at 14:39
I've deleted my answer for the moment as it feels like there's too much unknown information. a) where did you try using `??` (inside or outside `Sum`)? b) what's the type of `AmountAllocated`? c) when you say "the query below returns null" do you mean that `SumPayments` is null for some rows, or that there are no results? d) Is `AmountAllocated` ever null? — Jon Skeet, May 24 '13 at 14:43
Hi - I've updated my question to show more clearly what I'm looking for. Thanks, Mark — Mark, May 24 '13 at 14:55
`Sum()` returns a non nullable `int` (or `decimal`), so it will return a `0` in stead of `null`. — Henk Mollema, May 24 '13 at 15:11
Hi - it doesn't though - I've updated my question to show a screenshot where it returns a value for the first record, second record shows null. Thanks, Mark — Mark, May 24 '13 at 15:21
I am not sure but `.Sum(pi => pi.AmountAllocated ?? 0)` instead of `.Sum(pi => pi.AmountAllocated) ?? 0` should work. — shakib, May 24 '13 at 18:31
This might help you: http://stackoverflow.com/questions/696431/linq-query-with-nullable-sum — Henk Mollema, May 25 '13 at 10:05

score 1 · Answer 1 · answered May 24 '13 at 18:31

You could try testing to see if there are any rows to be returned.

{
       InvoiceId = i.InvoiceId,
       SumPayments = 
          db.PaymentInvoices.Any(pi => pi.InvoiceId == i.InvoiceId)
          ? db.PaymentInvoices.Where(pi => pi.InvoiceId == i.InvoiceId)
                              .Select(pi => pi.AmountAllocated).Sum()
          : 0
};

Another option is to use SumPayments.GetValueOrDefault(), which will return the value or 0.

Nick Albrecht · Answer 2 · 2013-05-24T17:49:34.373

0

I believe it's because in SQL all datatypes are nullable but in C# that's not true. Try forcing the enumeration of your payment invoices before you sum them by using .ToList()

SumPayments = db.PaymentInvoices.Where(pi => pi.InvoiceId == i.InvoiceId)
                                .Select(pi => pi.AmountAllocated)
                                .ToList()
                                .Sum()

UPDATE: Try to write the query as a join from Invoices to PaymentInvoices and group by the InvoiceId instead. I think the issue you're running into to is because you're trying to perform the aggregate sum as a sub-query.

edited May 24 '13 at 17:49

answered May 24 '13 at 15:10

Nick Albrecht

16,607
10
66
101

Thanks for the suggestion - I added your code exactly as above, but got the error (at runtime, not compile time): {"LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.Decimal] ToList[Decimal](System.Collections.Generic.IEnumerable`1[System.Decimal])' method, and this method cannot be translated into a store expression."} System.Exception {System.NotSupportedException} – Mark May 24 '13 at 15:18
Oh I see. It's because the Sum is a sub-select. Maybe if you re-write the query to join the Invoice table to the PaymentInvoices and group by InvoiceId instead of the sub-query. – Nick Albrecht May 24 '13 at 17:47

Sum in Linq to Sql not returning a value

2 Answers2