using System;
using System.Linq;
using System.Collections.Generic;
class M
{
class Student { public int Id { get; set; } public string Name { get; set; } public char Order { get; set; } }
class Class { public int Id { get; set; } public string Title { get; set; } }
class SickDay { public int Id { get; set; } public int StudentId { get; set; } public int ClassId { get; set; } }
static void Main(string[] args)
{
var Students = new List<Student>()
{
new Student { Id = 1, Name = "Al", Order = 'a' },
new Student { Id = 2, Name = "Betty", Order = 'b' },
new Student { Id = 3, Name = "Charles", Order = 'c' },
};
var Classes = new List<Class>()
{
new Class { Id = 1, Title = "A100" },
new Class { Id = 2, Title = "A200" },
};
var SickDays = new List<SickDay>()
{
new SickDay { Id = 1, StudentId = 1, ClassId = 2 },
new SickDay { Id = 2, StudentId = 1, ClassId = 2 },
new SickDay { Id = 3, StudentId = 1, ClassId = 2 },
new SickDay { Id = 4, StudentId = 1, ClassId = 2 },
new SickDay { Id = 5, StudentId = 2, ClassId = 1 },
};
var myId = 2;
var query =
from s in Students
join d in SickDays on s.Id equals d.StudentId into gj
from sd in gj.DefaultIfEmpty()
group sd by new { s.Name, s.Order } into gg
select
new
{
Name = gg.Key.Name,
Order = gg.Key.Order,
Count = gg.Count(x => x != null && x.ClassId == myId)
};
foreach (var q in query)
{
Console.WriteLine("{0}\t{1}\t{2}", q.Name, q.Order, q.Count);
}
}
}
dXNpbmcgU3lzdGVtOwp1c2luZyBTeXN0ZW0uTGlucTsKdXNpbmcgU3lzdGVtLkNvbGxlY3Rpb25zLkdlbmVyaWM7CgpjbGFzcyBNCnsKICAgIGNsYXNzIFN0dWRlbnQgeyBwdWJsaWMgaW50IElkIHsgZ2V0OyBzZXQ7IH0gcHVibGljIHN0cmluZyBOYW1lIHsgZ2V0OyBzZXQ7IH0gcHVibGljIGNoYXIgT3JkZXIgeyBnZXQ7IHNldDsgfSB9CiAgICBjbGFzcyBDbGFzcyB7IHB1YmxpYyBpbnQgSWQgeyBnZXQ7IHNldDsgfSBwdWJsaWMgc3RyaW5nIFRpdGxlIHsgZ2V0OyBzZXQ7IH0gfQogICAgY2xhc3MgU2lja0RheSB7IHB1YmxpYyBpbnQgSWQgeyBnZXQ7IHNldDsgfSBwdWJsaWMgaW50IFN0dWRlbnRJZCB7IGdldDsgc2V0OyB9IHB1YmxpYyBpbnQgQ2xhc3NJZCB7IGdldDsgc2V0OyB9IH0KCiAgICBzdGF0aWMgdm9pZCBNYWluKHN0cmluZ1tdIGFyZ3MpCnsKICAgIHZhciBTdHVkZW50cyA9IG5ldyBMaXN0PFN0dWRlbnQ+KCkKICAgIHsKICAgICAgICBuZXcgU3R1ZGVudCB7IElkID0gMSwgTmFtZSA9ICJBbCIsIE9yZGVyID0gJ2EnIH0sCiAgICAgICAgbmV3IFN0dWRlbnQgeyBJZCA9IDIsIE5hbWUgPSAiQmV0dHkiLCBPcmRlciA9ICdiJyB9LAogICAgICAgIG5ldyBTdHVkZW50IHsgSWQgPSAzLCBOYW1lID0gIkNoYXJsZXMiLCBPcmRlciA9ICdjJyB9LAogICAgfTsKCiAgICB2YXIgQ2xhc3NlcyA9IG5ldyBMaXN0PENsYXNzPigpCnsKbmV3IENsYXNzIHsgSWQgPSAxLCBUaXRsZSA9ICJBMTAwIiB9LApuZXcgQ2xhc3MgeyBJZCA9IDIsIFRpdGxlID0gIkEyMDAiIH0sCn07Cgp2YXIgU2lja0RheXMgPSBuZXcgTGlzdDxTaWNrRGF5PigpCnsKbmV3IFNpY2tEYXkgeyBJZCA9IDEsIFN0dWRlbnRJZCA9IDEsIENsYXNzSWQgPSAyIH0sCm5ldyBTaWNrRGF5IHsgSWQgPSAyLCBTdHVkZW50SWQgPSAxLCBDbGFzc0lkID0gMiB9LApuZXcgU2lja0RheSB7IElkID0gMywgU3R1ZGVudElkID0gMSwgQ2xhc3NJZCA9IDIgfSwKbmV3IFNpY2tEYXkgeyBJZCA9IDQsIFN0dWRlbnRJZCA9IDEsIENsYXNzSWQgPSAyIH0sCm5ldyBTaWNrRGF5IHsgSWQgPSA1LCBTdHVkZW50SWQgPSAyLCBDbGFzc0lkID0gMSB9LAp9OwoKdmFyIG15SWQgPSAyOwoKdmFyIHF1ZXJ5ID0KICAgIGZyb20gcyBpbiBTdHVkZW50cwogICAgam9pbiBkIGluIFNpY2tEYXlzIG9uIHMuSWQgZXF1YWxzIGQuU3R1ZGVudElkIGludG8gZ2oKICAgIGZyb20gc2QgaW4gZ2ouRGVmYXVsdElmRW1wdHkoKQogICAgZ3JvdXAgc2QgYnkgbmV3IHsgcy5OYW1lLCBzLk9yZGVyIH0gaW50byBnZwogICAgc2VsZWN0CiAgICAgICAgbmV3CiAgICAgICAgewogICAgICAgICAgICBOYW1lID0gZ2cuS2V5Lk5hbWUsCiAgICAgICAgICAgIE9yZGVyID0gZ2cuS2V5Lk9yZGVyLAogICAgICAgICAgICBDb3VudCA9IGdnLkNvdW50KHggPT4geCAhPSBudWxsICYmIHguQ2xhc3NJZCA9PSBteUlkKQogICAgICAgIH07CmZvcmVhY2ggKHZhciBxIGluIHF1ZXJ5KQp7CkNvbnNvbGUuV3JpdGVMaW5lKCJ7MH1cdHsxfVx0ezJ9IiwgcS5OYW1lLCBxLk9yZGVyLCBxLkNvdW50KTsKfQp9Cn0=