将SQL（complex）转换为LINQ

debugcn 发表于 Dev

脑病

如何将以下类型的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

温星

尝试这样的事情：

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。