fork download
  1. using System;
  2. using System.Linq;
  3. using System.Collections.Generic;
  4.  
  5. class M
  6. {
  7. class Student { public int Id { get; set; } public string Name { get; set; } public char Order { get; set; } }
  8. class Class { public int Id { get; set; } public string Title { get; set; } }
  9. class SickDay { public int Id { get; set; } public int StudentId { get; set; } public int ClassId { get; set; } }
  10.  
  11. static void Main(string[] args)
  12. {
  13. var Students = new List<Student>()
  14. {
  15. new Student { Id = 1, Name = "Al", Order = 'a' },
  16. new Student { Id = 2, Name = "Betty", Order = 'b' },
  17. new Student { Id = 3, Name = "Charles", Order = 'c' },
  18. };
  19.  
  20. var Classes = new List<Class>()
  21. {
  22. new Class { Id = 1, Title = "A100" },
  23. new Class { Id = 2, Title = "A200" },
  24. };
  25.  
  26. var SickDays = new List<SickDay>()
  27. {
  28. new SickDay { Id = 1, StudentId = 1, ClassId = 2 },
  29. new SickDay { Id = 2, StudentId = 1, ClassId = 2 },
  30. new SickDay { Id = 3, StudentId = 1, ClassId = 2 },
  31. new SickDay { Id = 4, StudentId = 1, ClassId = 2 },
  32. new SickDay { Id = 5, StudentId = 2, ClassId = 1 },
  33. };
  34.  
  35. var myId = 2;
  36.  
  37. var query =
  38. from s in Students
  39. join d in SickDays on s.Id equals d.StudentId into gj
  40. from sd in gj.DefaultIfEmpty()
  41. group sd by new { s.Name, s.Order } into gg
  42. select
  43. new
  44. {
  45. Name = gg.Key.Name,
  46. Order = gg.Key.Order,
  47. Count = gg.Count(x => x != null && x.ClassId == myId)
  48. };
  49. foreach (var q in query)
  50. {
  51. Console.WriteLine("{0}\t{1}\t{2}", q.Name, q.Order, q.Count);
  52. }
  53. }
  54. }
Success #stdin #stdout 0.06s 37424KB
stdin
Standard input is empty
stdout
Al	a	4
Betty	b	0
Charles	c	0