CODEWITHSUNDEEP
c#entity-framework.net-4.0entity-framework-4
DenaliHardtail
DenaliHardtail

Reputation: 28296

EF query with conditions from multiple tables

I have a table named "Employees". The employeeId from this table MAY be in another table (a many-to-many join tbale) named "Tasks". The other field in the "Tasks" table is a taskId linked to the "TaskDetails" table. This table includes details such as budgetHours.

Using EF4, how do I write the WHERE statement such that the return is employees assigned to tasks where the budgetHours is > 120 hours?

THe WHERE statement in the following limits rows in the Employees table but now I need to add the conditions on the TaskDetails table.

var assocOrg = Employees.Where(x => x.EmployeeTypeID == 2 && x.DepartmentName == "ABC").Select (x => x.EmployeeID);

Thanks!

Upvotes: 2

Views: 485

Answers (3)

Michele Ranum
Michele Ranum

Reputation: 11

If you table sturcture is as below,

TableName            Employee  Task               TaskDetails
ReferenceKeys        EmpID     EmpdID/TaskID      TaskID/BudgetHours

then use,

Employee.Where(x => x.Task.EmpID == x.EmpID && x.Task.TaskDetails.TaskID == x.Task.TaskID && x.Task.TaskDetails.BudgetHours > 120).select(x => x.EmpID)

Upvotes: 1

Dan J
Dan J

Reputation: 16708

Assuming you have a Tasks navigation property on the Employee entity, this should be straightforward:

var assocOrg = Employees.Where(x => x.Tasks.Any(t => t.BudgetHours > 120) && x.DepartmentName == "ABC").Select (x => x.EmployeeID);

Of course, this requires the Tasks property to be resolved at this point, either explicitly, via lazy-loading, or with .Include().

(kudos to @adrift for getting Tasks.Any() right... oops.)

Upvotes: 0

Jeff Ogata
Jeff Ogata

Reputation: 57783

If Employees has a navigation property named Tasks, try this:

var assocOrg = Employees.Where(x => x.EmployeeTypeID == 2 && 
                                    x.DepartmentName == "ABC" &&
                                    x.Tasks.Any(t => t.BudgetHours > 120))
                        .Select (x => x.EmployeeID);

Upvotes: 4

Related Questions