代码之家 › 专栏 › 技术社区 › Heero Yuy

如何将此SQL查询转换为LINQ

linq sql

Heero Yuy · 技术社区 · 6 年前

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; }
}

我怎么能在林肯做到这一点,

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 和 LocationId sum(A.Quantity) 和 (sum(A.Quantity) - sum(B.Quantity)) 同时急切地 A.Product 和 A.Location

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

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

1 回复 | 直到 6 年前

Pablo notPicasso 6 年前

如果要选择SUM,则不能显示单个对象的Id。
可以显示ProductId和LocationId(正在对其进行分组的列)。

尝试类似的操作(未测试):

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)
                });