want to convert SQL query to LINQ query in E.F

Question

I am developing app with MVC 3.5 and EF.

I have written the SQL query and I want to rewrite in the LINQ, but I don't know how to use it...

here is the sql query

select * from PurchaseOrders where CreatedById in
(select Employees_Id from EmployeeRole where Roles_Id in 
(select Roles_Id from EmployeeRole where Employees_Id = 17))

Answer 1

Assuming:-

1. your context is set up correctly and you have all the navigation properties in place
2. your query is "Get me all of the purcahse orders created by any employee who shares a role with employee #17"

You can use:-

context.Employees.Where(x => x.Id == 17)
                 .SelectMany(x => x.Roles)
                 .SelectMany(x => x.Employees)
                 .Distinct()
                 .SelectMany(x => x.PurchaseOrders);