[c#]从 SQL 到 Linq 中,联接总和和多个列组的毛病

发布时间: 2017/2/26 23:27:45
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我想查询下要转换成用于分页在 MVC 中的 linq。

SELECT tblInvoiceMaster.InvoiceNo,
         tblCompanyMaster.CompanyId,
         tblCompanyMaster.CompanyName,
         tblInvoiceMaster.InvoiceType,
         tblInvoiceMaster.InvoiceId,
         Sum(tblInvoiceItem.Quantity * tblProductMaster.Rate)    AS TotalPrice,
         CONVERT(VARCHAR(11), tblInvoiceMaster.InvoiceDate, 113) AS InvoiceDate
  FROM   tblCompanyMaster
         INNER JOIN tblInvoiceMaster
                 ON tblCompanyMaster.CompanyId = tblInvoiceMaster.CompanyId
         INNER JOIN tblInvoiceItem
                 ON tblInvoiceMaster.InvoiceId = tblInvoiceItem.InvoiceId
         INNER JOIN tblProductMaster
                 ON tblProductMaster.ProductId = tblInvoiceItem.ProductId
  GROUP  BY tblInvoiceMaster.InvoiceNo,
            tblCompanyMaster.CompanyId,
            tblCompanyMaster.CompanyName,
            tblInvoiceMaster.InvoiceDate,
            tblInvoiceMaster.InvoiceId,
            tblInvoiceMaster.InvoiceType

等效的 Linq 查询︰

                        (from company in DbContext.tblCompanyMasters
                        join invoice in DbContext.tblInvoiceMasters
                            on company.CompanyId equals invoice.CompanyId
                        join item in DbContext.tblInvoiceItems
                            on invoice.InvoiceId equals item.InvoiceId
                        join product in DbContext.tblProductMasters
                            on item.ProductId equals product.ProductId
                        //here I was having issue for group by columns from multiple tables
                        group new { company, invoice, item, product } by new
                        {
                            invoice.InvoiceNo,
                            invoice.InvoiceDate,
                            invoice.InvoiceId,
                            invoice.InvoiceType,
                            company.CompanyId,
                            company.CompanyName,

                        } into g
                        select new
                        {
                            g.Key.CompanyId,
                            g.Key.CompanyName,
                            g.Key.InvoiceNo,
                            g.Key.InvoiceType,
                            g.Key.InvoiceId,
                            g.Key.InvoiceDate,
                            Sum = g.Sum(o => o.item.Quantity * o.product.Rate)
                        }).ToList();

我被困在多个列中 GROUP BY 是从不同的表和做 SUM 同时执行乘法运算,例如︰

SUM(tblInvoiceItem.Quantity * tblProductMaster.Rate)

解决方法 1:

group子句返回一个序列的 IGrouping<TKey, TElement> 对象,其中包含零个或更多组的关键值匹配的项目。每个 IGrouping<TKey, TElement> 对象具有 Key 属性的存储位置的键值︰

//...  
group new { company, invoice } by new
{
    invoice.InvoiceNo,
    invoice.InvoiceDate,
    invoice.InvoiceId,
    invoice.InvoiceType,
    company.CompanyId,
    company.CompanyName,
} into g
select new
{
   g.Key.CompanyId,
   g.Key.CompanyName,
   g.Key.InvoiceNo,
   g.Key.InvoiceType,
   g.Key.InvoiceId,
   Sum = g.Sum(o => o.invoice.Quantity * o.company.Rate)
};
赞助商