Clever Geek Handbook

Combining two LINQ queries into one

I have a table that has 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 a single LINQ query to get the cost for job id = 1 as well as the latest date of that cost?

Sample query output for the example data above to look like this:

40 , 10-july-2011

I am currently doing this with two different linq queries like this, which results in two images 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()

Would a single linq query be better in this case? Cases like this during page load will result in multiple hits in the database, about 18 in total, to 9 different tables. When combining the sum and the LastUpdated date in one query, I am trying to reduce the hit count to 9, one for each table.

Thank you for your time...

+3


source to share


2 answers


Yes you can do 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),
            };
+2


source


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


source






More articles:


All Articles