CODEWITHSUNDEEP
c#linq
Theomax
Theomax

Reputation: 6810

LINQ query to Group and get Sum

I have the following query which joins a collection of RailwayStation objects and ExpenditureData objects joined by StationId and I am trying to get the top 10 StationCargoCodes (a property on the Station object) and also select the Sum of the ExpenditureCost for the top 10 but I cannot get the correct syntax:

var data = (from s in stations join e in expenditureData on s.stationId 
equals e.StationId 
orderby e.expenditureAmount descending 
select s)
.Sum(e => e.expenditureAmount)
.GroupBy(n => n.StationCargoCode)
.Take(10);

What LINQ would I need to do the grouping and sum for this?

Upvotes: 1

Views: 529

Answers (2)

CodingYoshi
CodingYoshi

Reputation: 27039

var query = from s in stations
            join e in expenditureData
                on s.stationId equals e.StationId into se
            group se by new
            {
                s.StationCargoCode, ExpenditureCostSum = se.Sum(x => x.ExpenditureCost)
            } into g
            orderby g.Key.ExpenditureCostSum descending
            select g.Key;

var top10 = query.Take(10).ToList();

Upvotes: 2

Alen.Toma
Alen.Toma

Reputation: 4870

your periority is wrong my friend. think about it, you get the top 10 and then do the sum operation. the code above get the sum of all and then you take the last 10. Your code should be more like this instead

var data = (from s in stations 
join e in expenditureData on s.stationId equals e.StationId 
orderby e.expenditureAmount descending 
select s)
.GroupBy(n => n.StationCargoCode) // you may need to do Select(x=> x.First()) if you want to have only distinct
.Take(10) // take the top 10 eg from 0 to 10
.Sum(e => e.expenditureAmount) // then Do sum on the result which will be the top 10

Upvotes: 1

Related Questions