无法对多列和计数进行分组

本文关键字： | 更新日期: 2023-09-27 18:29:36

我想用Linq:编写这个简单的查询

select issuercode,securitycode,dataprocessingflag,COUNT(issuercode) as cnt 
from cmr_invhdr
where ProcessedLike <> 'STMNT ONLY'
group by issuercode,securitycode,dataprocessingflag
order by Issuercode

我尝试了以下代码，但收到了此错误（DbExpressionBinding需要一个具有集合ResultType的输入表达式。参数名称：输入）：

 var lstCMRInvHdrNips = (from r in e.CMR_INVHDR
                         where r.ProcessedLike != "STMNT ONLY"
                         select new { 
                                        r.IssuerCode, 
                                        r.SecurityCode, 
                                        CountofIssuerCode = r.IssuerCode.Count(),
                                        r.DataProcessingFlag 
                                    }
                         ).GroupBy(x => 
                                       new { 
                                               x.IssuerCode, 
                                               x.SecurityCode, 
                                               x.DataProcessingFlag, 
                                               x.CountofIssuerCode 
                                           }
                                   ).OrderBy(x => x.Key.IssuerCode).ToList();

无法对多列和计数进行分组

同时按此字段进行分组时对issuercode进行计数有意义吗？当被一个字段分组时，它的COUNT将始终为1。

也许您不应该按issuercode分组，并在单独的Select语句中在GroupBy之后计数：

var result = e.CMR_INVHDR
              .Where(r => r.ProcessedLike != "STMNT ONLY")
              .GroupBy(r => new { r.SecurityCode, r.DataProcessingFlag })
              .Select(r => new 
              { 
                  Value = r.Key, 
                  IssuerCodesCount = r.GroupBy(g => g.IssuerCode).Count() 
              })
              .ToList();