CODEWITHSUNDEEP
c#asp.net-mvclinqsql-to-linq-conversion
bnil
bnil

Reputation: 1541

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

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))

enter image description here

Upvotes: 0

Views: 139

Answers (3)

Sandip
Sandip

Reputation: 987

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();

Upvotes: 0

Iain Galloway
Iain Galloway

Reputation: 19230

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

Upvotes: 2

Dave Williams
Dave Williams

Reputation: 2246

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

Upvotes: 0

Related Questions