Get the second highest salary of the employees using LINQ

Question

I have the following entity

public class Employee
{
    public int EmployeeID { get; set; }
    public string EmployeeName { get; set; }
    public string Department { get; set; }
    public long Salary { get; set; }
}

I am trying to find out the second highest salary for the employees of every individual department using LINQ.

So far here is what I have done

 var Query = (from emp in Employees
              orderby emp.Salary descending
              group emp by emp.Department into g
              select new
              {
                  id = g.Select(i=>i.EmployeeID),
                  sal  = g.Select(s=>s.Salary),
                  name = g.Select(n=>n.EmployeeName),
                  dept = g.Select(d=>d.Department)
              }

              ).Skip(1).ToList();

But this query is not working.,

E.g.

Suppose I have

EmployeeID = 1,EmployeeName  ="A", Department ="Dept1", Salary = 10000
EmployeeID = 2,EmployeeName ="B", Department ="Dept1", Salary = 20000
EmployeeID = 3,EmployeeName ="C"  Department ="Dept1", Salary = 20000
EmployeeID = 4,EmployeeName="D",  Department ="Dept1", Salary = 30000
EmployeeID = 5,EmployeeName  ="A1", Department ="Dept2", Salary = 12000
EmployeeID = 6,EmployeeName ="B1", Department ="Dept2", Salary = 4500

The expected output will be

EmployeeID = 2,EmployeeName ="B", Department ="Dept1", Salary = 20000
EmployeeID = 3,EmployeeName ="C"  Department ="Dept1", Salary = 20000
EmployeeID = 6,EmployeeName ="B1", Department ="Dept2", Salary = 4500

in the result set

Using C#3.0 and Dotnet framework 3.5

Answer 1

Not sure which is your desired behavior so here are two versions.

//select people with second person's salary
var q = from emp in Employees
        group emp by emp.Department into g
        let salary = g.OrderByDescending(e => e.Salary).Skip(1).First().Salary
        let second = g.Where(e => e.Salary == salary)
        from emp in second
        select emp;

//select people with second highest overall
var q = from emp in Employees
        group emp by emp.Department into dept
        let seconds = dept.GroupBy(e => e.Salary).OrderByDescending(g => g.Key).Skip(1).First()
        from emp in seconds
        select emp;

Answer 2

I don't have access right now to a C# compiler, but this should work:

List<Employee> employees = new List<Employee>();
foreach(var grouped in db.Employees.GroupBy(p=>p.Department))
{
    if (grouped.Count() > 1)
    {
        int tmpSalary = grouped.OrderByDescending(q=>q.Salary)
            .Skip(1).Take(1).Single().Salary;
        employees.Concat(grouped.Where(q=>q.Salary == tmpSalary));
    }
}