How do I convert this statement into linq?

Question

How can I use Sum() in Linq?

  select sum(op.Quantity),
         sum(p.UnitPrice),
         sum(p.Discount),
         o.CreateAt 
    from OrderProduct op join 
         [Order] o on o.ID = op.OrderID join 
         Product p on p.ID = op.ProductID
group by o.CreateAt

Answer 1

Instead of just writing a SQL statement, it would be more helpful to define your goal. You have some tables and you want to extract information from it. Which information do you want? Now I have to guess your structure.

It seems you have Orders and Products and OrderProducts.

Every Order has zero or more OrderProducts; every OrderProduct belongs to exactly one Order. Every Order also has a CreateAt property.

Every Product has zero or more OderProducts; every OrderProduct belongs to exactly one Product. Every Product also has a UnitPrice and a Discount.

Finally every OrderProduct has a property Quantity

You didn't mention whether you are using entity framework or not. But even if not, you have access to the three tables using IQueryables. In Entity Framework this is done via the DbSets:

IQueryable<Product> products = ...
IQueryable<Order> orders = ...
IQueryable<OrderProduct> orderProducts = ...

The query in baby steps:

var joinedItems = from product in Products
join orderProduct in orderProducts on oderProduct.ProductId == product.Id
join order in orders on order.Id == orderProduct,OrderId
select new
{
    Quantity = orderProduct.Quantity,
    UnitPrice = product.UnitPrice,
    Discount = product.Discount,
    CreateAt = order.CreateAt,
};

Multiple joins is the only time I use query syntax instead of method syntax. Click here to see the method syntax for multiple joins

Because every product has only one UnitPrice, I assume you don't want the sum of all UnitPrice of one Product, but you want the sum of all Unitprices of items created on CreateAt, and similarly the sum of all Discounts and Quantities.

So we first group all joinedItems into joinedItems with the same CreateAt:

var itemsGroupedByCreateAt = joinedItems
    .GroupBy(joinedItem => joinedItem.CreateAt);

In words: take all joinedItems and group them into groups where all joinedItems in the group have the same value for CreateAt.

The result is a sequence of Groups. Every group has joinedItems. All joinedItems in the group have the same value for CreateAt. this common value is the Key of the group.

So all you have to do is for every group to sum the UnitPrices of all joinedItems in the group. Do the same for the Quantity and Discount. Note that every group will create one sumResult:

var sumResults = itemsGroupedByCreateAt
    .Select(group => new 
    {
        TotalQuantity = group
            .Select(groupElement => groupElement.Quantity)
            .Sum(),
        TotalUnitPrice = group
            .Select(groupElement => groupElement.UnitPrice)
            .Sum(),
        Totaldiscount = group
            .Select(groupElement => groupElement.UnitPrice)
            .Sum(),
        // only if you need it in your result:
        CreateAt = group.Key,
    });

In words: take all your groups of joinedItems. From every group, take the joinedItems in the group. From these joinedItems take only the Quantities, and Sum them. Put the result in totalQuantity. Do the same for the UnitPrices and the Discounts

Of course you can do this all in one big linq statement. Not sure whether this would improve readability and maintainability.