SQL Query to Linq (Group By and Sum)

A

Alex Sauceda

Hi Everyone,

I'm trying to convert this query into LINQ but I don't have idea how to do
it.

Select C.sName as CustomerName, S.sName as Salesperson,
Sum(O.nQty) as Qty, Sum(O.mAmount) as Amount
From tOrders O
Inner Join tCustomer C On C.CustomerID = O.CustomerID
Inner Join tSalesperson S On S.SalespersonID = O.SalespersonID
Group By C.sName, S.sName

I Hope you can help me.

Regards,

Alex
 
M

Marc Gravell

Well, it is hard to test without more info (perhaps something similar
on "pubs" or "northwind"?) - but perhaps something along the lines of:

var query = from order in db.Orders
group order by new { Customer = order.Customer,
SalesPerson = order.SalesPerson }
into grp
select new
{
Customer = grp.Key.Customer.Name,
SalesPerson = grp.Key.SalesPerson.Name,
Quantity = grp.Sum(o => o.Quantity),
Amount = grp.Sum(o => o.Amount)
};

foreach (var item in query)
{
Console.WriteLine("{0}, {1}, {2}, {3}", item.SalesPerson,
item.Customer, item.Amount, item.Quantity);
}
 
A

Alex Sauceda

Using Northwind it would be something like this:
Select C.CompanyName, O.ShipCity, Sum(D.Quantity) as Quantity,
Sum(D.Quantity*D.UnitPrice) as Amount
From Customers C Inner Join
Orders O On C.CustomerID = O.CustomerID Inner Join
[Order Details] D on O.OrderID = D.OrderID
Group by C.CompanyName, O.ShipCity
Order by C.CompanyName, O.ShipCity


Thanks a lot for helping me.
 
M

Marc Gravell

Your sample helped me a lot.

No problem; actually, it occurred to me that do get something close to
your original, you should probably group directly on the names, i.e.

group order by new { CustomerName = order.Customer.Name,
SalesPersonName = order.SalesPerson.Name }
into grp
select new
{
grp.Key.CustomerName,
grp.Key.SalesPersonName,
<etc as before>

For reference, you can inspect the generated SQL on a data-context via
GetCommand(query); or use a SQL trace.

Glad it helped, though ;-p
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top