CODEWITHSUNDEEP
vb.netlinq
noisyass2
noisyass2

Reputation: 580

LINQ group by maxcount

I have this table named Projects.

Name            Date           Hours
p1                  1/1/13         1
p1                  1/8/13         2
p1                  1/9/13         1
p2                  1/3/13         5
p2                  1/4/13         3

I'd like to aggregate these into some thing like this

p1    Tue    4
p2    Thu    8

I don't know how to get the Max of Count of Date.Weekday part.. So far, I've tried something like this.

Dim qry = From er in Projects 
          Group er by er.Name Into Max(er.Date.Value.Weekday), Sum(er.Hours)

But this would give me "Wed" on p1 because its higher than "Tue". What I really want is "Tue" because it has more records thatn "Wed"

Upvotes: 0

Views: 385

Answers (1)

BTJ
BTJ

Reputation: 198

The trick for finding the day with the most entries is to group by day, then sort the groups by the number of entries in each group, then grab the first one.

Dim query = Projects.GroupBy(Function(e) e.Name, Function(name, groupedEntries) New With { _
    .Name = name, _
    .TopDay = groupedEntries.GroupBy(Function(e) e.[Date].DayOfWeek).OrderByDescending(Function(g) g.Count()).First().Key, _
    .TotalHours = groupedEntries.Sum(Function(e) e.Hours) _
})

Upvotes: 2

Related Questions