时间戳

首页 > 解决方案 > 如何将此 SQL 查询转换为 LINQ

sql - 如何将此 SQL 查询转换为 LINQ

问题描述

public class Inventory
{
        public int Id { get; set; }
        public int ProductId { get; set; }
        public int LocationId { get; set; }
        public int Quantity { get; set; }
        public decimal PurchasePrice { get; set; }
        public decimal ResellerPrice { get; set; }
        public decimal RetailPrice { get; set; }
        public byte FundSource { get; set; } 
        public string Note { get; set; }
        public DateTime DateAdded { get; set; }
        public DateTime DateUpdated { get; set; }

        public Product Product { get; set; }
        public Location Location { get; set; }

}

public class InventoryEvent
{
        public int Id { get; set; }
        public int ProductId { get; set; }
        public int LocationId { get; set; }
        public int Quantity { get; set; }
        public decimal? Price { get; set; }
        public decimal? Total { get; set; }
        public byte EventType { get; set; }
        public byte? PaymentMethod { get; set; }
        public DateTime DateAdded { get; set; }
        public DateTime DateUpdated { get; set; }

        public virtual Product Product { get; set; }
        public virtual Location Location { get; set; }
}

我怎样才能在 LINQ 中做到这一点，

select A.Id, sum(A.Quantity) as totalQuantity, 
  (totalQuantity - 
    (select sum(B.Quantity) 
     from InventoryEvent B 
     where B.ProductId = A.ProductId and B.LocationId = A.LocationId 
     group by B.ProductId, B.LocationId)
  ) as Available 
from Inventory A 
group by A.ProductId, A.LocationId

我想在按ProductId和分组后显示库存 A 中的列LocationId，另外还有 2 列；sum(A.Quantity)和(sum(A.Quantity) - sum(B.Quantity))一个同时的 eagerloadA.Product和A.Location

我试过这个

var inventories = AppContext.Inventories.Include(i => i.Product)
            .Include(i => i.Location)
            .GroupBy(i => new { i.LocationId, i.ProductId });

但我不知道如何前进，甚至不确定它是否正确。

标签： sqllinq

解决方案

如果要选择 SUM，则不能显示单个对象的 Id。
您可以显示 ProductId 和 LocationId（您要分组的列）。
如果您仅引用 ProductId 和 LocationId，我不确定是否需要包含 Product 或 Location。

尝试这样的事情（未经测试）：

var inventories = AppContext.Inventories.GroupBy(x => new { x.ProductId, x.LocationId }).Select(g => new
                {
                    g.Key.ProductId,
                    g.Key.LocationId,
                    totalQuantity = g.Sum(y => y.Quantity),
                    Available = g.Sum(y => y.Quantity) - AppContext.InventoryEvents.Where(e => e.ProductId == g.Key.ProductId && e.LocationId == g.Key.LocationId).Sum(x => x.Quantity)
                });

推荐阅读