SQL Query to Linq (Group By and Sum)

[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

 

[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);
}

 

[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.

 

[Alex Sauceda]

Your sample helped me a lot.

Thanks a lot Marc.

Regards,

Alex

 

[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

 

[Alex Sauceda]

SQL Trace helped me a lot.

Thanks a again for answering.

Regards,

Alex

 

[Alex Sauceda]

SQL Trace helped me a lot.

Thanks again for answering.

Regards,

Alex