CODEWITHSUNDEEP
.netlinqentity-frameworklinq-to-entities
user20358
user20358

Reputation: 14736

Combining two LINQ queries into one

I have a table that has a about 15 different fields, some of which are JobID (integer field), Cost (integer field) and LastUpdated (DateTime field)

JobID  Cost      LastUpdated
 1      10        10-July-2011
 1      15        11-June-2011
 2      25        5-May-2011
 1      15        12-April-2011

Is it possible to write one LINQ query to get the sum of Cost for job id = 1 and also the last date such a cost incurred?

The example query output for the sample data above to look like this below:

40 , 10-july-2011

Currently I am doing it with two different linq queries like this, resulting in two hits to the database from the website in this particular case. 

//for last updated
(from row in MyTable where row.JobID == 1
 orderby row.LastUpdated descending
 select row.LastUpdated).First()

//for sum of cost
(from row in MyTable  
 where row.JobID == 1
 select row.Cost).Sum()

Will one linq query be better in such a case? Similar cases like this throughout the page load will result in multiple hits to the database, about 18 in all, to 9 different tables. In combining the sum and LastUpdated date in one query I am trying to reduce that hit count to 9, one for each table.

Thanks for your time...

Upvotes: 2

Views: 185

Answers (2)

Aducci
Aducci

Reputation: 26634

Yes, you can do a group by like this

var query = from row in MyTable
            group row by row.JobID into rows
            where rows.Key == 1 
            select new
            {
              LastUpdated = rows.Select(x => x.LastUpdated).OrderByDescending(x => x).First(),
              Cost = rows.Sum(x => x.Cost),
            };

Upvotes: 2

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174279

you can use this:

MyTable.Where(x => x.JobID == 1)
       .OrderByDescending(x => x.LastUpdated)
       .GroupBy(x => x.JobID)
       .Select(x => new { LastUpdated = x.First().LastUpdated, 
                          Sum = x.Sum(y => y.Cost) } );

Upvotes: 2

Related Questions