How can I convert my query featuring RowNumber() to LINQ?

Question

Below is my SQL code:

create Table Dept
(
  DeptNo int identity (1,1) primary key,
  DeptName VarChar(255)
  )

create table Emp
(
  EmpNo int identity(1,1) primary key,
  FirstName VarChar(255),
  LastName VarChar(255),
  DeptNo Int Foreign Key references Dept(DeptNo),
  Salary Int Default 0
  )

insert into dept values ('Operations')
insert into dept values ('Sales')
insert into dept values ('Marketing')
insert into dept values ('HR')
insert into dept values ('Maintenance')

Insert into Emp values ('Danny', 'Rancher', 1, 16000)
insert into emp values ('Test1', 'Test1', 1, 16500)
insert into emp values ('Test2', 'Test2', 2, 10000)
insert into emp values ('Test3', 'Test3', 2, 21000)
insert into emp values ('Test4', 'Test4', 2, 17000)
insert into emp values ('Test5', 'Test5', 3, 5000)
insert into emp values ('Test6', 'Test6', 3, 45000)
insert into emp values ('Test7', 'Test7', 3, 27000)
insert into emp values ('Test8', 'Test8', 4, 23000)
insert into emp values ('Test9', 'Test9', 4, 22000)
insert into emp values ('Test10', 'Test10', 4, 10000)
insert into emp values ('Test11', 'Test11', 4, 50000)

-- Query to get all employees sorted by department and then by highest salaries
select * from emp order by deptno, salary desc

-- Query to get top 2 employees from each department with the highest salaries
SELECT Emp.EmpNo,
       Emp.FirstName, 
       Emp.LastName, 
       Emp.DeptNo,
       Emp.Salary 
FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY DeptNo ORDER BY Salary DESC ) AS 'RowNumber', 
             EmpNo
      FROM Emp 
      ) SubQuery, 
      Emp 
WHERE RowNumber <= 2 
  AND SubQuery.EmpNo = Emp.EmpNo

How can I translate the query, specifically the RowNumber clause, to a Linq 2 SQL query? Thank you.

I cannot seem to apply the indexed overload of select as in this question

• How do I add ROW_NUMBER to a LINQ query or Entity?

Answer 1

This works in memory with an array. Not sure how well it will translate into database calls. (Update: restructured code so the important part is on top)

void Main()
{
    var Emps = Emp.GetData();

    var q = (from d in (from e in Emps
            group e by e.DeptNo)
            select d.Take(2)).SelectMany(e=>e);

    q.Dump();
}

// Boring setup details

class Emp
{
    public int EmpNo {get; set;}
    public string  FirstName {get; set;}
    public string LastName {get; set;}
    public int DeptNo {get; set;}
    public int Salary {get; set;}

    public Emp(int no, string fn, string ln, int de, int sal)
    {
        this.EmpNo = no;
        this.FirstName = fn;
        this.LastName = ln;
        this.DeptNo = de;
        this.Salary = sal;
    } 

    static public Emp[] GetData()
    {
    return new Emp[] {
        new Emp (1,"Danny", "Rancher", 1, 16000),
        new Emp (2,"Test1", "Test1", 1, 16500),
        new Emp (3, "Test2", "Test2", 2, 10000),
        new Emp (4, "Test3", "Test3", 2, 21000),
        new Emp (5, "Test4", "Test4", 2, 17000),
        new Emp (6, "Test5", "Test5", 3, 5000),
        new Emp (7, "Test6", "Test6", 3, 45000),
        new Emp (8, "Test7", "Test7", 3, 27000),
        new Emp (9, "Test8", "Test8", 4, 23000),
        new Emp (10, "Test9", "Test9", 4, 22000),
        new Emp (11, "Test10", "Test10", 4, 10000),
        new Emp (12, "Test11", "Test11", 4, 50000)  
    };
    }

}