Linq 查询涉及对两个未直接连接的表执行分组依据

本文关键字：连接 执行 查询 两个 Linq | 更新日期: 2023-09-27 18:31:30

在具有这些类的 EF 模型中：

 class BoxOutput
 {
     public long BoxId { get; set; }
     public virtual Box Box { get; set; }
     public long BoxId { get; set; }
     public long CategoryId { get; set; }
     public virtual Category Category { get; set; }
     public long? ColorId { get; set; }
     public virtual Category Color { get; set; }
     public decimal Weight { get; set; }
     // ...and other irrelevant properties
 }
 class BoxInput
 {
     public long BoxId { get; set; }
     public virtual Box Box { get; set; }
     public long BoxId { get; set; }
     public long CategoryId { get; set; }
     public virtual Category Category { get; set; }
     public long? ColorId { get; set; }
     public virtual Category Color { get; set; }
     public decimal Weight { get; set; }
     // ...and other irrelevant properties
 }


...我怎样才能对特定的框（例如。 boxId = 12 ），返回此？

  category.name    color.name      inputWeightsSum      outputWeightsSum
 ---------------------------------------------------------------------------
     c             null               0                    0
     c             red                0                    0
     c             blue               0                    0
     m             null               0                    0
     m             red                0                    0
     m             blue               0                    0
                           ....

我目前几乎实现了这个目标，但是"可选颜色"在做"笛卡尔乘积"时给我带来了麻烦：我没有显示空值......

这就是我所拥有的。

var boxesInputs = dbContext.BoxesInputs
.Where(c => c.BoxId == 12)
.GroupBy(c => new { c.CategoryId, c.ColorId })
.Select(g => new
{
    categoryId = g.Key.CategoryId,
    colorId = g.Key.ColorId,
    sumOfWeights = g.Sum(r => (decimal?)r.Weight) ?? 0,
}).ToList();
var boxesOutputs = dbContext.BoxesOutputs
.Where(c => c.BoxId == 12)
.GroupBy(c => new { c.CategoryId, c.ColorId })
.Select(g => new
{
    categoryId = g.Key.CategoryId,
    colorId = g.Key.ColorId,
    sumOfWeights = g.Sum(r => (decimal?)r.Weight) ?? 0,
}).ToList();
var categoriesAndColors = dbContext.Categories.AsEnumerable()
.SelectMany(category => dbContext.Colors.AsEnumerable()
.Select(color => new
{
    category = new
    {
        categoryId = category.CategoryId,
        name = category.Name,
    },
    color = new
    {
        colorId = color.ColorId,
        name = color.Name,
    },
    inputWeightsSum = boxesInputs.Where(r => r.categoryId == category.CategoryId && r.colorId == color.ColorId).Sum(r => (decimal?) r.sumOfWeights) ?? 0,
    outputWeightsSum = boxesOutputs.Where(r => r.categoryId == category.CategoryId && r.colorId == color.ColorId).Sum(r => (decimal?)r.sumOfWeights) ?? 0,
})).ToList();

在前面的代码中，前两个查询返回以下内容：

  category.name    color.name      inputWeightsSum
 -------------------------------------------------------
     c             null               0
     c             red                0
     c             blue               0
     m             null               0
     m             red                0
     m             blue               0 
                           ....
  category.name    color.name      outputWeightsSum
 -------------------------------------------------------
     c             null               0
     c             red                0
     c             blue               0
     m             null               0
     m             red                0
     m             blue               0 
                           ....

第三个加入了这两个。我想我需要改进该连接，因为我正在失去空值。

此外，此代码使用内存中代码，我希望它是一个单SQL查询（linq-to-entity，而不是linq-to-objects与linq-to-entity混合）。可能吗？给出BoxOutput和BoxInput是两个不同的表，它们没有直接连接。或者无论如何我最终都会得到 3 个查询？

Linq 查询涉及对两个未直接连接的表执行分组依据

从两个表中选择一个相同的匿名类型，将它们合并在一起，然后进行分组并选择。像这样：

dbContext.BoxesInputs
.Select(g => new TempClass
{
    CategoryId = g.CategoryId,
    ColorId = g.ColorId,
    InputWeight = r.Weight,
    OutputWeight = 0,
})
.Union(dbContext.BoxesOutputs
    .Select(g => new TempClass
    {
        CategoryId = g.CategoryId,
        ColorId = g.ColorId,
        InputWeight = 0,
        OutputWeight = r.Weight
    })
)
.GroupBy(c => new { c.CategoryId, c.ColorId })
.Select(g => new
{
    categoryId = g.Key.CategoryId,
    colorId = g.Key.ColorId,
    sumOfInputWeights = g.Sum(r => r.InputWeight),
    sumOfOutputWeights = g.Sum(r => r.OutputWeight),
}).ToList();
public class TempClass
{
    public int CategoryID { get; set; }
    public int ColorID { get; set; }
    public decimal InnerWeight { get; set; }
    public decimal OuterWeight { get; set; }
}
相关文章：