GroupBy with relationships in EFCore-CodePudding

Products:

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

Items:

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

Result should be:

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

I started out with:

context.Products
    .GroupBy(x => x.Status, (s, p) => new {
        Status = s,
        TotalProducts = p.Count
    });

How can I also inlude the count of items and group the items aswell? I think this would require to join items with products. However I am not sure how to achieve that in efcore.

The entity classes are:

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

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Cost { get; set; }

    public int ProductId { get; set; }
    public Product Product { get; set; }
}

CodePudding user response：

var totalProducts = context.Products.Count();
var totalItems = context.Items.Count();
var items = context.Products
    .Join(context.Items, p => p.Id, i => i.ProductId, (p, i) => new { p.Status, Cost = 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 [Items] AS [i] ON [p].[Id] = [i].[ProductId]
GROUP BY [p].[Status]

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