Combining two LINQ queries into one

Question

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

score 2 · Answer 1 · answered Mar 21 '12 at 12:46

2

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

answered Mar 21 '12 at 12:46

Daniel Hilgarth

171,043
40
335
443

thanks Daniel for the quick response, dont mean to be rude but just for my understanding how is this query better than the one given by Aducci? I ran both in SQL profiler and they both seem to consume the same amount of cpu and time. However his query seems more readable to me coming from a SQL background. Is this the better way to write Linq queries or should I go with what I find more readable. Again, I dont mean to be rude or question your knowledge. this is just for my understanding... thanks :) – user20358 Mar 21 '12 at 13:06
@user20358: The answers should be equivalent. I personally don't like the query syntax and find it hard to read, that's why I am using method chains. It's a matter of personal preference. – Daniel Hilgarth Mar 21 '12 at 13:11

Aducci · Accepted Answer · 2012-03-21T13:00:58.270

2

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

edited Mar 21 '12 at 13:00

answered Mar 21 '12 at 12:47

Aducci

26,101
8
63
67

I tried this in linqpad .. and got an error : No overload for method 'OrderByDescending' takes 0 arguments. – user20358 Mar 21 '12 at 12:59
however this line which I changed did the trick.. LastUpdated = rows.OrderByDescending(x => x.LastUpdated).First().LastUpdated – user20358 Mar 21 '12 at 13:00
@user20358 - sorry I updated to *.OrderByDescending(x => x)* that should work for you now. Your line is also good. – Aducci Mar 21 '12 at 13:01

Combining two LINQ queries into one

2 Answers2