How to generate SQL COUNT (*) OVER (PARTITION BY {ColumnName}) in LINQ-to-SQL?

Question

Is it possible to generate the following SQL query using a LINQ-to-SQL query expression or chains of methods that are deferred?

Data structure

alt text http://www.freeimagehosting.net/uploads/e062a48837.jpg

Select Distinct ClassRoomTitle, Count(*) Over(Partition By ClassRoomNo) As [No Sessions Per Room], TeacherName, Count(*) Over(Partition By ClassRoomNo, TeacherName) As [No Sessions Per Teacher] From ClassRoom 

Expected Result

alt text http://www.freeimagehosting.net/uploads/47a79fea8b.jpg

Answer 1

Try the following:

  var vGroup = from p in ClassRoom group p by new { p.ClassRoomNo, p.TeacherName } into g from i in g select new { i.ClassRoomNo, i.TeacherName, i.ClassRoomTitle, NoSessionsPerTeacher = g.Count() }; var pGroup = from p in vGroup group p by new { p.ClassRoomNo } into g from i in g select new { i.ClassRoomTitle, NoSessionsPerRoom = g.Count(), i.TeacherName, i.NoSessionsPerTeacher }; var result = pGroup.OrderBy(p => p.ClassRoomNo).ThenBy(p => p.TeacherName); 

I have not tested above, but you can check your source code in case something happened in the rewriting:

  var vGroup = from p in Products group p by new { p.ProductId, p.VariantId } into g from i in g select new { i.ProductId, i.VariantId, VariantCount = g.Count() }; var pGroup = from p in vGroup group p by new { p.ProductId } into g from i in g select new { i.ProductId, ProductCount = g.Count(), i.VariantId, i.VariantCount }; var result = pGroup.OrderBy(p => p.ProductId).ThenBy(p => p.VariantId); 

Answer 2

 var classRooms = from c in context.ClassRooms group c by new {c.ClassRoomNo} into room select new { Title = room.First().ClassRoomTitle, NoSessions = room.Count(), Teachers = from cr in room group cr by new {cr.TeacherName} into t select new { Teacher = t.Key, NoSessions = t.Count() } }; 

A little more structured than the expected result, but I think it is better.

You can always use SelectMany if you want to return to unstructured:

 var unstructured = classRooms .SelectMany(c=> c.Teachers.Select( t=> new { Title = c.Title, SessionsPerRoom = c.NoSessions, Teacher = t.Teacher, SessionsPerTeacher = t.NoSessions });