将SQL(复杂)转换为Linq

Question

如何将以下类型的SQL查询类型转换为Linq（扩展方法语法和查询语法），

select t.Roll_Number,t.Status from (
    select * , 
      count(case when status <> 0 then 1 end) over(partition by Roll_Number,Subject_Code) c
    from table_1
) t where c = 0
GROUP BY t.Roll_Number,t.Status
ORDER BY t.Roll_Number

Answer 1

尝试这样的东西：

var result = data
            .GroupBy(x => new {x.RollNumber, x.SubjectCode})
            .Select(x => new {Group = x, Count = x.Count(item => item.Status != 0)})
            .SelectMany(x => x.Group.Select(item => new {item, x.Count}))
            .Where(x => x.Count == 0)
            .GroupBy(x => new {x.item.RollNumber, x.item.Status})
            .OrderBy(x => x.Key.RollNumber);

或在查询语法中：

var result = from x in data
                group x by new { x.RollNumber, x.SubjectCode } into g
                select new { Group = g, Count = g.Count(x => x.Status != 0) } into h
                from x in h.Group.Select(item => new {item, h.Count})
                where x.Count == 0
                group x by new { x.item.RollNumber, x.item.Status } into j
                orderby j.Key.RollNumber
                select j;

Answer 2

我已经将查询分为3个块，因此更容易理解特定的步骤。

//count column 'c' for each row
var rowsWithCount = from t in db.table_1
                    select
                    new
                    {
                        t.RollNumber,
                        t.SubjectCode,
                        t.Status,
                        c = db.table_1.Count(i => i.RollNumber == t.RollNumber && i.SubjectCode == t.SubjectCode && i.Status != 0)
                    };
//add condition for 'c'
rowsWithCount = rowsWithCount.Where(i => i.c == 0);
//group and order results
var groupedResult = from r in rowsWithCount
                    group r by new { r.RollNumber, r.Status }
                    into g
                    orderby g.Key.RollNumber
                    select g;