Convert SQL to LINQ in MVC3 with Ninject

Posted by Jeff on Stack Overflow See other posts from Stack Overflow or by Jeff
Published on 2012-07-21T23:49:30Z Indexed on 2012/10/24 11:01 UTC
Read the original article Hit count: 259

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about asp.net-mvc-3