sql到LINQ的左联接groupby和sum()抛出异常

Question

我有一个查询，我将其转换为LINQ表达式。但引发Null异常。

目标：付款类型可以有三个值（0,1,2）。我需要查询所有三种付款类型的所有货币的总金额。此外，如果paymentType的三个值中的任何一个都不存在特定货币的记录。金额应显示为零。

表格结构：

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

查询：

     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表达式：

       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) };

但由于在声明new时SelectMany语句中出现NULL异常，它给了我"对象引用"错误。

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

有人能帮我做错事吗。

Answer 1

我用一些样本数据重写了代码，并按照描述重现了错误。

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();

所以问题是t.amount不是null：t本身就是null。所以你只需要调整一下：

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