GroupBy with many-to-many relationship in EFCore

Question

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?

Answer 1

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