Products:

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

Items:

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

Intermediate table:

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

Many to many configuration:

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

Result should be:

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

Total count of items for a specific product:

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

Now I want to group the items per product status. Something like:

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

Any ideas how to achieve the desired output?

CodePudding user response：

you can create two dto like below and use groupby to do that

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

then use this

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

CodePudding user response：

Entities:

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

Query :

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 Generated :

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]