LINQ 查询跨多个集合对产品求和
本文关键字:求和 集合 查询 LINQ | 更新日期: 2023-09-27 18:36:46
我有两个数据集,如下所示:
+------------------------------------+
| Products |
+------------------------------------+
| Id | Name | Price |
+------------------------------------+
| 1 | apples | 1.00 |
| 2 | oranges | 2.00 |
| 3 | pomengrate | 3.00 |
+------------------------------------+
+-------------------------------+
| Sales |
+-------------------------------+
| CustId | ProductId | Quantity |
+-------------------------------+
| 1 | 1 | 5 |
| 1 | 2 | 4 |
| 1 | 3 | 2 |
| 2 | 1 | 8 |
| 2 | 3 | 7 |
+-------------------------------+
我想得到每个客户花费的金额,基本上最终得到这样的结果:
+----------------+
| CustId | Total |
+----------------+
| 1 | 19.00 |
| 2 | 29.00 |
+----------------+
我可以跨单个表执行求和,但 LINQ 中的 Sum 方法采用只有一个参数的 lambda:对求和值所在的表的引用。 这些值位于不同的表中。 我如何将这些相加?
var totals =
from sale in Sales
group sale by sale.CustId into custSales
select new
{
CustId = custSales.Key,
Total = (
from custSale in custSales
select custSale.Product.Price *
custSale.Quantity)
.Sum()
};
试一试。它给出了您正在寻找的结果:
var results = sales.Join(products,
sale => sale.ProductID,
product => product.ID,
(sale, product) => new { CustID = sale.CustID, Total = sale.Quantity * product.Price })
.GroupBy(r => r.CustID)
.Select(g => new { CustID = g.Key, Total = g.Sum(gt => gt.Total) });
为了完整起见,下面是查询语法版本(使用连接而不是子选择):
var totals =
from sale in sales
join prod in product on sale.ProductId equals prod.Id
let saleProds = new { sale.CustId, Total = prod.Price * sale.Quantity }
group saleProds by saleProds.CustId into custSale
select new { Customer = custSale.Key, Total = custSale.Sum(tr => tr.Total) };
关键部分是,您需要以某种方式将联接的集合结果(销售和生产)转换为单个实体,然后可以对其进行分组。