Convert SQL to LINQ in MVC3 with Ninject
- by Jeff
I'm using MVC3 and still learning LINQ. I'm having some trouble trying to convert a query to LINQ to Entities. I want to return an employee object.
SELECT E.EmployeeID, E.FirstName, E.LastName, MAX(EO.EmployeeOperationDate) AS "Last Operation"
FROM Employees E
INNER JOIN EmployeeStatus ES ON E.EmployeeID = ES.EmployeeID
INNER JOIN EmployeeOperations EO ON ES.EmployeeStatusID = EO.EmployeeStatusID
INNER JOIN Teams T ON T.TeamID = ES.TeamID
WHERE T.TeamName = 'MyTeam'
GROUP BY E.EmployeeID, E.FirstName, E.LastName
ORDER BY E.FirstName, E.LastName
What I have is a few tables, but I need to get only the newest status based on the EmployeeOpertionDate. This seems to work fine in SQL. I'm also using Ninject and set my query to return Ienumerable. I played around with the group by option but it then returns IGroupable. Any guidance on converting and returning the property object type would be appreciated.
Edit: I started writing this out in LINQ but I'm not sure how to properly return the correct type or cast this.
public IQueryable<Employee> GetEmployeesByTeam(int teamID)
{
var q = from E in context.Employees
join ES in context.EmployeeStatuses on E.EmployeeID equals ES.EmployeeID
join EO in context.EmployeeOperations on ES.EmployeeStatusID equals EO.EmployeeStatusID
join T in context.Teams on ES.TeamID equals T.TeamID
where T.TeamName == "MyTeam"
group E by E.EmployeeID into G
select G;
return q;
}
Edit2:
This seems to work for me
public IQueryable<Employee> GetEmployeesByTeam(int teamID)
{
var q = from E in context.Employees
join ES in context.EmployeeStatuses on E.EmployeeID equals ES.EmployeeID
join EO in context.EmployeeOperations.OrderByDescending(eo => eo.EmployeeOperationDate) on ES.EmployeeStatusID equals EO.EmployeeStatusID
join T in context.Teams on ES.TeamID equals T.TeamID
where T.TeamID == teamID
group E by E.EmployeeID into G
select G.FirstOrDefault();
return q;
}
