sql to LINQ with left join group by and sum() throws exception

Question

I have a query which I converted to LINQ expression. But throwing Null exception.

Objective: Payment Type can have three values (0,1,2).I need to query total sum amount for all currency for all three PaymentType. Also if the record for specific currency doesn't exist for either of three values of paymentType. Amount should display it as zero.

Table Structure:

      Currency varchar(10),
      PaymentType(int),
      amount(decimal)

Query:

     select aa.Currency,aa.PaymentType,sum(bb.amount) from (
     select * from (select distinct paymenttype from payment) a cross join                        
     (select distinct currency from payment) c
     ) aa left join payment bb on aa.PaymentType = bb.PaymentType and    
     aa.Currency = bb.Currency group by aa.PaymentType,aa.Currency
     order by aa.Currency

Linq Expression:

       var temp = _db.Payments.Select(c => c.PaymentType) .Distinct() 
      .SelectMany(c => _db.Payments.Select(a => a.Currency).Distinct(), 
      (PaymentType, Currency) => new { PaymentType, Currency }).ToList();


       var data = from p in temp join c in _db.Payments on new {                
       p.PaymentType, p.Currency } equals new { c.PaymentType, c.Currency }  
       into j1 from j2 in j1.DefaultIfEmpty() group j2 by new { 
       p.PaymentType, p.Currency } into grouped select new { paymenttype = 
       grouped.Key.PaymentType, currency = grouped.Key.Currency, amount = 
       grouped.Sum(t => (decimal?)t.Amount ?? 0) };

But It is giving me "Object Reference" error due to NULL exception in SelectMany statement while declaring new.

         t => (decimal?)t.Amount ?? 0

Can someone help where i am doing wrong.

Answer 1

I have rewritten the code a bit with some sample data and reproduced the error as described.

var Payments = new [] {new {PaymentType = 0, Currency = "EUR", Amount = 10}, 
                              new {PaymentType = 0, Currency = "CHF", Amount = 70}, 
                              new {PaymentType = 2, Currency = "CHF", Amount = 80}, 
                              new {PaymentType = 1, Currency = "GBP", Amount = 90}, 
                              new {PaymentType = 1, Currency = "EUR", Amount = 100}};


        var temp = Payments.Select(c => c.PaymentType).Distinct()
                           .SelectMany(c => Payments.Select(a => a.Currency).Distinct(), 
                                                            (pt, cur) => new { PaymentType = pt, Currency = cur })
                           .ToList();


        var data = from p in temp
                   join c in Payments on new { p.PaymentType, p.Currency } equals new { c.PaymentType, c.Currency } into j1
                   from j2 in j1.DefaultIfEmpty()
                   group j2 by new
                   {
                       p.PaymentType,
                       p.Currency
                   } into grouped
                   select grouped;

        foreach (var g in data.ToList())
        {

            Console.WriteLine("{0},{1},{2}", g.Key.PaymentType, g.Key.Currency, g.Sum(t => t.Amount));
        }


        Console.ReadLine();

So the problem was that t.amount is not null: t itself is null. So all you need to adjust is:

grouped.Sum(t => t == null ? 0 : t.Amount)