CODEWITHSUNDEEP
c#entity-frameworkasp.net-core
adel Ibrahim
adel Ibrahim

Reputation: 23

Group by and sum column using Entity Framework

I have three entities, first one is Product:

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public DateTime CratedDate { get; set; } = DateTime.Now;
    public ApplicationUser ApplicationUser { get; set; }
    public string ApplicationUserId { get; set; }
    public ProductCategory ProductCategory { get; set; }
    public Guid ProductCategoryId { get; set; }
    public string ProductType { get; set; }
}

Second entity is Users:

public class ApplicationUser : IdentityUser
{
    public string DisplayName { get; set; }
    public decimal Balance { get; set; }
    public virtual IEnumerable<Product> Products { get; set; }
    public virtual IEnumerable<Order> Orders { get; set; }
}

Third entity is Order:

public class Order
{
    public Guid Id { get; set; }
    public Product Product { get; set; }
    public Guid ProductId { get; set; }
    public ApplicationUser User { get; set; }
    public string UserId { get; set; }
    public DateTime OrderDate { get; set; }
}

I tried to get orders and group them by user and get the total price of products, using this code:

var orders = _db.Orders.GroupBy(x => x.User.UserName)
                       .Select(x => new
                                    {
                                        userName = x.Key, 
                                        toolsCount = x.Count(), 
                                        totalPrice = x.Sum(s => s.Product.Price)
                                    })
                       .ToList();

but I get this error:

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression:

KeySelector: a.UserName,
ElementSelector:EntityShaperExpression:
EntityType: Order
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False

.Sum(s => s.Product.Price)' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. )

Upvotes: 0

Views: 3124

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

I suggest you use the overload of GroupBy that takes a lambda that creates the output objects that are grouped:

.GroupBy(x =>x.User.UserName, x => x.Product.Price)

And change

totalPrice = x.Sum(s=>s.Product.Price)

To

totalPrice = x.Sum()

because X is now a list of prices (numbers), not a list of Order entities

At the time that EF is doing the joins to get from order->user->username for the key, it can also know you want to get from order->product->price and do the joins for that too, ultimately producing just a list of usernames and prices..

..later in the select part it finds out what to do with the list: group by name (it knew that part) and separately count and also sum the prices. That's easy to translate to SQL; the bit it was struggling with was, having pulled a list of orders and associated users, and built a query that grouped the orders up by username, it was then asked to go from orders through to products to get the price, but as product wasnt originally implicitly or explicitly included, it's a bit late in the set of steps to start bringing in the related necessary data

All in, I expect this modified form where EF can work out which joins you want sooner, will end up with a query like:

  SELECT u.Name AS ..., COALESCE(SUM(p.Price), 0.0) AS ...
  FROM Orders AS o
  INNER JOIN Products AS p ON o.ProductId = p.ProductId
  INNER JOIN Users AS u ON o.UserId = u.UserId
  GROUP BY u.Name

Without the "heads up", it doesn't know to do the Products join..

Upvotes: 4

Related Questions