SQL到Linq:按id分组,但可以';t和值

本文关键字:但可以 和值 分组 Linq id SQL | 更新日期: 2023-09-27 18:25:26

我正在搜索与此查询等效的内容:

SELECT Prd.barcode , SumQUA = SUM(AType.unit*MIA.quantity) 
FROM [dbo].[MerchantInventoryActivity] AS MIA
JOIN [dbo].[MerchantInventoryActivityType] AS AType ON (MIA.typeId = AType.id)
JOIN [dbo].[Product] AS Prd ON (MIA.productId = Prd.id)
WHERE MIA.invoiceId =  '123'   
GROUP BY Prd.barcode  

我在SumQUA=SUM(AType.Unity*MIA.quantity)上卡住了。这是我到目前为止得到的:

(from mia in Entities.MerchantInventoryActivity
join Prd in Entities.Product on mia.productId equals Prd.id
join AType in Entities.MerchantInventoryActivityType on mia.typeId equals AType.id
where mia.invoiceId == invoiceId
group Prd by Prd.barcode into g    
select new 
{
   Barcode = g.Key,
   SumQUA = // ??
}).ToList();

谢谢!

SQL到Linq:按id分组,但可以';t和值

您可以先投影到匿名类型,然后分组:

(from mia in Entities.MerchantInventoryActivity
    join Prd in Entities.Product on mia.productId equals Prd.id
    join AType in Entities.MerchantInventoryActivityType on mia.typeId equals AType.id
    where mia.invoiceId == invoiceId
    select new { Prd.barcode, AType.unit, MIA.quantity } into temp
    group temp by temp.barcode into g
    select new
    {
        Barcode = g.Key,
        SumQUA = g.Sum(c => c.unit * c.quantity)
    });

在求和时添加空值检查

(from mia in Entities.MerchantInventoryActivity
        join Prd in Entities.Product on mia.productId equals Prd.id
        join AType in Entities.MerchantInventoryActivityType on mia.typeId equals AType.id
        where mia.invoiceId == invoiceId
        select new { Prd.barcode, AType.unit, MIA.quantity } into temp
        group temp by temp.barcode into g
        select new
        {
            Barcode = g.Key,
            SumQUA = g.Sum(c => c.unit??0 * c.quantity??0)
        });