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.