c# - 如何在 LINQ-to-SQL 中生成 SQL COUNT(*) OVER (PARTITION BY {Column Name})？

Question

是否可以使用可延迟执行的 LINQ-to-SQL 查询表达式或方法链来生成以下 SQL 查询？

数据结构

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

预期结果

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

Answer 1

试试这个:

        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);

我没有测试上面的内容，但你可以检查我的原始代码，以防我在重写时出错:

        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);