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))
Assuming:-
You can use:-
context.Employees.Where(x => x.Id == 17)
.SelectMany(x => x.Roles)
.SelectMany(x => x.Employees)
.Distinct()
.SelectMany(x => x.PurchaseOrders);
Distinct() after SelectMany Employees?
Assuming that your above query is just a bit weird and you actually meant to do:
SELECT * FROM PurchaseOrders WHERE CreatedById = 17;
Your LINQ query would be:
PurchaseOrders.Where(Order => Order.CreatedById = 17);
or
var Orders = from Order in PurchaseOrders
where Order.CreatedById = 17
select Order;
Seeing your update I guess you would actually be better off selecting you employee and then all of the purchase orders i.e.
var Orders = Employees.Single(x => x.Id == 17).PurchaseOrders;
but beware above will work only if there is such an employee
var s= (from po in _db.PurchaseOrders
join er in _db.EmployeeRoles on po.CreatedById equals er.Employees_Id
let _ser in _db.EmployeeRoles.Where(c=>c.Employees_Id == 17)
where _ser.Select(c=>c.Roles_Id).contails(er.Roles_Id)
Select po).toList();
