来自不同表的两列的总和，如果辅助表没有任何数据，它应该返回结果

Question

我有两个表：Tb_PurchaseProductDetail（主键：id）、tb_productstockupdate（外键：PurchaseProductId（与 Tb_PurchaseProductDetail 表主键有关系））。现在我想要关系中 Tb_PurchaseProductDetail 表的两列值和 tb_productstockupdate 表的一列的总和 ()。如果 tb_productstockupdate 表中存在任何单行，但如果 tb_productstockupdate 表中没有可用数据，则下面的查询会给我正确的数据，而不是从主表返回数据。

我希望如果辅助表中不存在数据，至少它应该基于主表返回数据。

s.tb_productstockupdate.Sum(d => d.StockDifference))

上面的代码应该返回 0 而不是 null。

下面是我正在使用的代码。我不知道我错过了什么。

connectionObj.Tb_PurchaseProductDetail.Where(s => s.ProductId == prodcutId && ((s.Quantity + s.DiscountQuantity + s.tb_productstockupdate.Sum(d => d.StockDifference)) > s.SoldPackQuantity)).Select(s => new SelectListItem()

            {

                Text = s.BatchNumber + " [(" + (s.Quantity + s.DiscountQuantity + s.tb_productstockupdate.Sum(d => d.StockDifference) - s.SoldPackQuantity) + ") & " + s.ExpiryDate + "]",

                Value = s.Id.ToString()

            }).ToList();

下面的代码解决了我的问题。我使用 Any() 检查辅助表是否有数据，并相应地放置我的逻辑。

connectionObj.Tb_PurchaseProductDetail.Where(s => s.ProductId == prodcutId && ((s.Quantity + s.DiscountQuantity + (s.tb_productstockupdate.Any() ? s.tb_productstockupdate.Select(d => d.StockDifference).Sum() : 0)) > s.SoldPackQuantity)).Select(s => new SelectListItem()

            {

                Text = s.BatchNumber + " [(" + (s.Quantity + s.DiscountQuantity + (s.tb_productstockupdate.Any()? s.tb_productstockupdate.Sum(d => d.StockDifference) : 0) - s.SoldPackQuantity) + ") & " + s.ExpiryDate + "]",

                Value = s.Id.ToString()

            }).ToList();

Answer 1

所以你有PurchaseProducts和ProductStocks，并且它们之间存在一对多关系：每个PurchaseProduct都有零个或多个ProductStocks，每个都ProductStock属于一个PurchaseProduct：外键PurchaseProductId指向的那个。每个PurchaseProduct人也至少有两个属性，假设为 A 和 B。每个ProductStock人至少有一个属性 C。要求：从每个 PurchaseProduct 及其所有 ProductStocks 中给我 A 和 B 及其所有 ProductStocks C 的总和如果您遵循实体框架代码优先约定，您将得到类似于下面的内容class PurchaseProduct{&nbsp; &nbsp; public int Id {get; set;}&nbsp; &nbsp; public double A {get; set;}&nbsp; &nbsp; public double B {get; set;}&nbsp; &nbsp; ...&nbsp; &nbsp; // every PurchaseProduct has zero or more ProductStocks (one-to-many)&nbsp; &nbsp; public virtual ICollection<ProductStock> ProductStocks {get; set;}}class ProductStock{&nbsp; &nbsp; public int Id {get; set;}&nbsp; &nbsp; public double C {get; set;}&nbsp; &nbsp; ...&nbsp; &nbsp; // every ProductStock belongs to exactly one PurchaseProduct, using foreign key&nbsp; &nbsp; public int PurchaseProductId {get; set;}&nbsp; &nbsp; public virtual PurchaseProduct PurchaseProduct {get; set;}}class MyDbContext : DbContext{&nbsp; &nbsp; &nbsp;public DbSet<PurchaseProduct> PurchaseProducts {get; set;}&nbsp; &nbsp; &nbsp;public DbSet<ProductStock> ProductStocks {get; set;}}这就是实体框架检测表、表中的列、表之间的关系以及主键和外键所需知道的全部内容。在实体框架中，表的列由非虚拟属性表示。虚拟属性表示表之间的关系（一对多，多对多，......）定义了您的类后，您的查询就很简单了。var result = myDbContext.PurchaseProducts&nbsp; &nbsp; .Where(purchaseProduct => ...)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// only if you don't want all PurchaseProducts&nbsp; &nbsp; // from every remaining PurchaseProduct calculate A + B + Sum(all C of its ProductStocks)&nbsp; &nbsp; .Select(purchaseProduct => purchaseProduct.A + purchaseProduct.B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + purchaseProduct.ProductStocks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(productStock => productStock.C)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Sum());实体框架知道 PurchaseProducts 和 ProductStocks 之间的关系，并且知道需要 groupJoin。请注意，每个 purchaseProduct 都有一个 A 和一个 B。您将始终至少获得 A+B。如果 purchaseProduct 根本没有 ProductStocks，则 purchaseProduct.ProductStocks 将是一个空集合。Select 和 Sum 将产生零值。有些人不信任实体框架，更喜欢自己加入群组：var result = myDbContext.PurchaseProducts.GroupJoin(&nbsp; &nbsp; myDbContext.ProductStocks,&nbsp; &nbsp; purchaseProduct => purchaseProduct.Id,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // from every PurchaseProduct take the Id&nbsp; &nbsp; productStock => productStock.PurchaseProductId, // from every ProductStock take the foreign key&nbsp; &nbsp; // ResultSelector: from every PurchaseProducts, with all its matching ProductStocks&nbsp; &nbsp; // calculate A + B + Sum of all C of the productStocks&nbsp; &nbsp; (purchaseProduct, productStocksOfThisPurchaseProduct) =>&nbsp; &nbsp; purchaseProduct.A + purchaseProduct.B +&nbsp; &nbsp; productStocksOfThisPurchaseProduct.Select(productStock => productStock.C).Sum());非常简单！

Answer 2

下面的代码解决了我的问题。我使用 Any() 检查辅助表是否有数据，并相应地使用了三元运算符。connectionObj.Tb_PurchaseProductDetail.Where(s => s.ProductId == prodcutId && ((s.Quantity + s.DiscountQuantity + (s.tb_productstockupdate.Any() ? s.tb_productstockupdate.Select(d => d.StockDifference).Sum() : 0)) > s.SoldPackQuantity)).Select(s => new SelectListItem()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Text = s.BatchNumber + " [(" + (s.Quantity + s.DiscountQuantity + (s.tb_productstockupdate.Any()? s.tb_productstockupdate.Sum(d => d.StockDifference) : 0) - s.SoldPackQuantity) + ") & " + s.ExpiryDate + "]",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Value = s.Id.ToString()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).ToList();