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!

CodePudding user response：

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

CodePudding user response：

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.