Linq: group not working
I have a problem that my GROUP BY is working correctly. Can anyone understand why?
public void MonthlyTurnover(int year, int month) {
var q1 = (from sp in _db.Species
from p in _db.Pets
from b in _db.Bookings.Where(x => x.ExpectedArrivalTime.Year == year &&
x.ExpectedArrivalTime.Month == month)
where p.SpeciesId == sp.Id && b.PetId == p.Id && b.PetId == p.Id
select new {sp.SpeicesName, Sum = b.Services.Sum(i => i.Price)}).ToList();
foreach (var v in q1) {
Console.WriteLine(v);
}
}
What do I get without a group
public void MonthlyTurnover(int year, int month) {
var q1 = (from sp in _db.Species
from p in _db.Pets
from b in _db.Bookings.Where(x => x.ExpectedArrivalTime.Year == year &&
x.ExpectedArrivalTime.Month == month)
where p.SpeciesId == sp.Id && b.PetId == p.Id && b.PetId == p.Id
select new {sp.SpeicesName, Sum = b.Services.Sum(i => i.Price)})
.GroupBy(x => new{x.SpeicesName, x.Sum}).ToList();
foreach (var v in q1) {
Console.WriteLine(v.Key);
}
}
What do I get from the group on
and what I want ...
+3
source to share
2 answers
Group only SpeicesName
, try this:
var q1 = (from sp in _db.Species
from p in _db.Pets
from b in _db.Bookings.Where(x => x.ExpectedArrivalTime.Year == year &&
x.ExpectedArrivalTime.Month == month)
where p.SpeciesId == sp.Id && b.PetId == p.Id && b.PetId == p.Id
select new {sp.SpeicesName, Sum = b.Services.Sum(i => i.Price)})
.GroupBy(x => x.SpeicesName).Select(g=>new {SpeicesName=g.Key,Sum=g.Sum(e=>e.Sum)}).ToList();
+3
source to share
Don't group by amount ... just group by species name.
...
.GroupBy(x => x.SpeicesName).ToList();
Now you have a group of groups, where the key is the name of the view. You can display the view name (once) and then sum all the individual amounts.
foreach (var v in q1)
{
Console.WriteLine("{0}: {1}", v.Key, v.Sum(x => x.Sum)); // "Dog: 7500", "Cat: 3500", etc
}
+2
source to share