Linq to SQL Grouping

[David]

Hi all,

At the moment, I am not sure how I would apply this in SQL yet, so I don't
know if it is possible immediately without making it too complicated.

I am running Linq to SQL.

I have this query.

var TrailerHistory = from th in dc.TrailerHistories
where th.TrailerID == FleetIDEdit.Text
&& th.DateOfRepair >=
DateTime.Today.AddYears(-1)
group th by th.Grp into g
select new
{
grp = g.Key,
cost = g.Sum(a => a.Value)
};

This works fine, however, the client has now asked that I have both the cost
within the past year (as the query returns) and cost for the whole history
(not limited to the past year)

I have absolutely no idea how I can achieve this without using a second
similar query without the time constraint.

Any ideas that I can follow would be very much appreciated.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

 

[Ashish Sheth]

Can you try:

var TrailerHistory = from th in dc.TrailerHistories
group th by th.Grp into g
select new
{
grp = g.Key,
wholeHistorycost = g.Sum(a => a.Value),
pastOneYearCost = g.Where(b =>
b.DateOfRepair >= DateTime.Today.AddYears(-1)).Sum(c => c.Value)
};
I guess this should work.

 

[David]

Cool, that seems to work, though I had to add
..GetValueOrDefault(0.0M)

to each of the cost lines in the query so that it didn't cause me a null
object (as I am totalling all rows as well as displaying individual rows)

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available