«

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

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

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.


"Marc Gravell" <> wrote in message
news:29d78dac-5605-44cf-a8e7-...
> 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);
> }

Your sample helped me a lot.

Thanks a lot Marc.

Regards,

Alex

"Marc Gravell" <> wrote in message
news:29d78dac-5605-44cf-a8e7-...
> 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);
> }

> 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

SQL Trace helped me a lot.

Thanks a again for answering.

Regards,

Alex

"Marc Gravell" <> wrote in message
news:9c64409e-6ac0-4a88-abad-...
>> 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

SQL Trace helped me a lot.

Thanks again for answering.

Regards,

Alex

"Marc Gravell" <> wrote in message
news:9c64409e-6ac0-4a88-abad-...
>> 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