CODEWITHSUNDEEP
c#linqlinq-to-entitiesentity-framework-6
coolblue2000
coolblue2000

Reputation: 4928

Group By Entity Framework

I have a two tables called "Orders" and "OrderItems" and they have the following columns

Orders

OrderId, OrderDate, OrderType,

OrderItems

ItemId, OrderId, Price,

I need to return the OrderId, OrderDate, OrderType and the Sum Of the Price. However I can't seem to find a good example of how to do this in linq to entities.

Can anyone help?

EDIT

Just to be clear, I need show one line for each order with the price being the sum of all the order items for that order.

Upvotes: 0

Views: 47

Answers (2)

M.S.
M.S.

Reputation: 4423

Let's say there are 3 orders IDs: 1,2,3 and there are 3 order items as mentioned below:

  • ID=1, OrderID=1 ,Price=12
  • ID=2, OrderID=1 ,Price=13

  • ID=3, OrderID=3 ,Price=14

    var carts = (from Cart o in Context.Orders
           join oi in Context.OrderItems on o.Id equals oi.OrderId into orderItems
           select new 
           {
               o.Id,
               o.OrderDate,
               o.OrderType,
               sum = orderItems.Sum(p=> p.Price)
           }).ToList();

The linq mentioned above will select sum as mentioned below

  • OrderID: 1, Sum:25
  • OrderID: 2, Sum:0
  • OrderID: 3, Sum:14

Upvotes: 1

Kien Chu
Kien Chu

Reputation: 4895

context.Orders.Include(order => order.OrderItems).Select(order => new {
    OrderId = order.OrderId,
    OrderDate = order.OrderDate,
    Ordertype = order.OrderType,
    TotalPrice = i.OrderItems.Sum(orderItem => orderItem.Price)
});

Maybe you could try something like this.

Upvotes: 0

Related Questions