CODEWITHSUNDEEP
c#linq
Cheung
Cheung

Reputation: 15552

SQL to LINQ Conversion - Group by

I using LinQPad and AdventureWorks LT DB to learn LINQ query.

I would like to convert the SQL below to LINQ, it is success and resulting what I want.

 SELECT SalesOrderID, SUM(OrderQty) as TotalQty, SUM(UnitPrice) as TotalPrice 
 FROM SalesLT.SalesOrderDetail detail
 WHERE detail.SalesOrderID = '71920'
 GROUP BY  detail.SalesOrderID;

SQL Resultset

LINQ query alternate to above.

 from detail in SalesOrderDetails
 where detail.SalesOrderID.Equals(71920) 
 group detail by detail.SalesOrderID into g
 select new { 
    SalesOrderID = g.Select(item =>item.SalesOrderID).Distinct().First(),        /*Distinct() : Enumerable<Int32>, First() Int32 */
    TotalQty =  g.Select(item =>Convert.ToDouble(item.OrderQty)).Sum(),
    TotalPrice = g.Select(item =>item.UnitPrice).Sum()
}

LINQ Result

But i would know it is necessary use

 Select new { ... }

to create another struct? Is it possible shorten the syntax?

Thanks.

Upvotes: 0

Views: 369

Answers (2)

leppie
leppie

Reputation: 117220

Little bit shorter

 from detail in SalesOrderDetails
 where detail.SalesOrderID.Equals(71920) 
 group detail by detail.SalesOrderID into g
 select new { 
    SalesOrderID = g.Key,    
    TotalQty =  g.Sum(item =>Convert.ToDouble(item.OrderQty)),
    TotalPrice = g.Sum(item =>item.UnitPrice)
}

That Distinct does not seem necessary. Also, if you specify the SalesOrderId, why select it again?

Upvotes: 2

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

a few elements could be shortened, in your select new

I give you the method syntax, but the content of the select part would be the same.

SalesOrderDetails.Where(sod => sod.SalesOrderID == 71920)
                 .GroupBy(sod => sod.salesOrderID)
                 .Select(g => new {
                               SalesOrderId = g.Key,
                               TotalQty = g.Sum(i => Convert.ToDouble(i.OrderQty)),
                               TotalPrice = g.Sum(i => i.UnitPrice)
                              });

Upvotes: 4

Related Questions