Convert SQL(complex ) to LINQ

Question

How can i convert following type of sql query to LINQ (Both Extension method syntax and Query Syntax),

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

Try something like this:

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

or in query syntax:

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;