GroupBy在EFCore中具有多對多關系

產品：

 ------ --------- -------------- 
|  id  |  name   | status       |
 ------ --------- -------------- 
| 1    |  foo    | in stock     |
| 2    |  bar    | in stock     |
| 3    |  baz    | out of stock |
 ------ --------- -------------- 

專案：

 ------ --------- -------- 
|  id  |  name   |  Cost  |
 ------ --------- -------- 
| 1    |  item1  | 10     |
| 2    |  item2  | 20     |
| 3    |  item3  | 5      |
 ------ --------- -------- 

中間表：

 -------------- ------------ 
|  product_id  |  items_id  |
 -------------- ------------ 
|  1           | 1          |
|  1           | 3          |
|  2           | 2          |
 -------------- ------------ 

多對多配置：

modelBuilder.Entity<Product>()
    .HasMany<Item>(x => x.Items)
    .WithMany(y => y.Products)
    .UsingEntity(p => p.ToTable("ProductItems"));

結果應該是：

{
    'TotalItems': 3,
    'Items': [
       { 'Status': 'in stock', 'Cost': 30 },
       { 'Status': 'out of stock', 'Cost': 5 }
    ]
}

特定產品的專案總數：

context.Products
      .Where(x => x.Name.Equals("foo"))
      .SelectMany(x => x.Items)
      .Count();

現在我想根據產品狀態對專案進行分組。就像是：

context.Products
      .Where(x => x.Name.Equals("foo"))
      .SelectMany(x => x.Items)
      .GroupBy(Status, (g,x) => new {
          Status = ...
          Cost = ...
      })
      .ToList()

任何想法如何實作所需的輸出？

uj5u.com熱心網友回復：

具有中間物體的解決方案

物體：

public class MyContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<ProductItem> ProductsItems => Set<ProductItem>();
    public DbSet<Item> Items => Set<Item>();        

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<ProductItem>()
            .HasKey(pi => new { pi.ProductId, pi.ItemId });
        modelBuilder.Entity<Product>()
            .HasMany(p => p.Items)
            .WithOne(pi => pi.Product)
            .HasForeignKey(pi => pi.ProductId);
        modelBuilder.Entity<Item>()
            .HasMany(i => i.Products)
            .WithOne(pi => pi.Item)
            .HasForeignKey(pi => pi.ItemId);
    }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Status { get; set; }
    public List<ProductItem> Items { get; set; }
}

public class ProductItem
{
    public int ProductId { get; set; }
    public Product Product { get; set; }
    public int ItemId { get; set; }
    public Item Item { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Cost { get; set; }
    public List<ProductItem> Products { get; set; }
}

詢問 ：

var totalProducts = context.Products.Count();
var totalItems = context.Items.Count();
var items = context.Products
    .Join(context.ProductsItems, p => p.Id, pi => pi.ProductId, (p, pi) => new { p.Status, pi.ItemId })
    .Join(context.Items, si => si.ItemId, i => i.Id, (si, i) => new { si.Status, i.Cost })
    .GroupBy(p => p.Status, (s, pis) => new {
    Status = s,
    Cost = pis.Sum(pi => pi.Cost)
}).ToList();

SQL 生成：

SELECT COUNT(*)
FROM [Products] AS [p]

SELECT COUNT(*)
FROM [Items] AS [i]

SELECT [p].[Status], COALESCE(SUM([i].[Cost]), 0.0) AS [Cost]
FROM [Products] AS [p]
INNER JOIN [ProductsItems] AS [p0] ON [p].[Id] = [p0].[ProductId]
INNER JOIN [Items] AS [i] ON [p0].[ItemId] = [i].[Id]
GROUP BY [p].[Status]

沒有中間物體的解決方案

物體：

public class MyContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Item> Items => Set<Item>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Product>()
            .HasMany<Item>(x => x.Items)
            .WithMany(y => y.Products)
            .UsingEntity(p => p.ToTable("ProductItems"));
    }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Status { get; set; }
    public List<Item> Items { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Cost { get; set; }
    public List<Product> Products { get; set; }
}

詢問 ：

var totalProducts = context.Products.Count();
var totalItems = context.Items.Count();
var items = context.Products
    .SelectMany(p => p.Items.Select(i => new { p.Status, i.Cost }))
    .GroupBy(p => p.Status, (s, ps) => new {
        Status = s,
        Cost = ps.Sum(i => i.Cost)
    })
    .ToList();

SQL 生成：

SELECT COUNT(*)
FROM [Products] AS [p]

SELECT COUNT(*)
FROM [Items] AS [i]

SELECT [t].[Status], COALESCE(SUM([t].[Cost]), 0.0) AS [Cost]
FROM [Products] AS [p]
CROSS APPLY (
  SELECT [p].[Status], [i].[Cost]
  FROM [ProductItems] AS [p0]
  INNER JOIN [Items] AS [i] ON [p0].[ItemsId] = [i].[Id]
  WHERE [p].[Id] = [p0].[ProductsId]
) AS [t]
GROUP BY [t].[Status]

uj5u.com熱心網友回復：

您可以像下面一樣創建兩個 dto 并使用 groupby 來做到這一點

public class ItemDTO
{
    public int Status { get; set; }
    public decimal Cost { get; set; }
}

public class productDTO
{
    public int TotalProducts { get; set; }
    public int TotalItems { get; set; }
    public List<ItemDTO> Items { get; set; }
}

然后用這個

var grouped = products.GroupBy(x => x.Status).Select(x =>new
        {
            key = x.Key,
            value = x.ToList()
        });
var res = new productDTO
        {
            TotalProducts = products.Count,
            TotalItems = products.Sum(x => x.Items?.Count)??0,
            Items = grouped.Select(x => new ItemDTO
            {
                Cost = x.value.Sum(q => q.Items?.Sum(i => i.Cost)??0),
                Status = x.key
            }).ToList()
        };

標籤：C＃ 林克 实体框架核心

上一篇：C#Linq查詢加入兩個串列并將結果分配給新串列

下一篇：如何在c#中檢查linq中的屬性值字串