How to group data by two fields in C# Linq?

Question

I have the following models:

    public class User
    {
       public long Id { get; set; }
       public string? Name { get; set; }
       public string? Surname { get; set; }
       public string? PhoneNumber { get; set; }
       public IEnumerable<Sale>? Sales { get; set; }
    }

    public class Product
    {
       [Key]
       public int Id { get; set; }
       public string Name { get; set; }
       public decimal Price { get; set; }
       public IEnumerable<Sale>? Sales { get; set; }
    }
    
    public class Sale
    {
       public int Id { get; set; }
       public User? User { get; set; }
       public List<SaleItem> SaleItems { get; set; }
       public DateTime CreatedDt { get; set; }
    }

    public class SaleItem
    {
        public int Id { get; set; }
        public Sale? Sale { get; set; }
        public Product? Product { get; set; }
        public int Count { get; set; }
    }

Need to get the count and price grouped by customer and product.

I tried to solve the problem in the following way:

var list = await context.SaleItems
    .Include(x => x.Product)
    .Include(x => x.Sale).ThenInclude(x => x.User)
    .Select(x => new
    {
        UserId = x.Sale.User.Id,
        UserName = x.Sale.User.Name,
        ProductId = x.Product.Id,
        ProductName = x.Product.Name,
        TotalCount = x.Count,
        TotalPrice = x.Product.Price * x.Count
    })
    .GroupBy(x => new { x.UserId, x.ProductId })
    .SelectMany(x => x)
    .ToListAsync();

But it doesn't work. Thanks!

_"But it doesn't work"_ - **how** is it not working? Do you get a runtime error? a compile error? Unexpected results? — Dai, Jul 21 '22 at 08:05
Why are you calling `.SelectMany( x => x )`? That defeats the point of `GroupBy`. — Dai, Jul 21 '22 at 08:07
EF Core 6.0.7 I get a runtime error: The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. — Poppy Field, Jul 21 '22 at 08:13

Svyatoslav Danyliv · Accepted Answer · 2022-07-21T08:54:41.653

3

SelectMany is wrong operator here. Also you can remove Includes, they are not needed.

var list = await context.SaleItems
    .Select(x => new
    {
        UserId = x.Sale.User.Id,
        UserName = x.Sale.User.Name,
        ProductId = x.Product.Id,
        ProductName = x.Product.Name,
        TotalCount = x.Count,
        TotalPrice = x.Product.Price * x.Count
    })
    .GroupBy(x => new { x.UserId, x.ProductId })
    .Select(g => new 
    {
        g.Key.UserId, 
        g.Key.ProductId,

        Count = g.Sum(x => x.TotalCount),
        TotalPrice = g.Sum(x => x.TotalPrice)
    })
    .ToListAsync();

edited Jul 21 '22 at 08:54

answered Jul 21 '22 at 08:17

Svyatoslav Danyliv

21,911
3
16
32

in last "Select" i replace Count = g.Count() to Count = g.Sum(x => x.TotalCount) and it works for me. Thank u so much. U save my day – Poppy Field Jul 21 '22 at 08:52

score 0 · Answer 2 · answered Jul 21 '22 at 08:12

0

SelectMany() flattens the queries that return list of lists.

Use .Select(x => x) instead of .SelectMany(x => x).

You can check Difference Between Select and SelectMany for more detailed explanation.

answered Jul 21 '22 at 08:12

shehanpathi

312
4
15

How to group data by two fields in C# Linq?

2 Answers2