EF query with conditions from multiple tables

Question

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!

Jeff Ogata · Accepted Answer · 2010-11-29 21:00:45Z

4

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

answered Nov 29, 2010 at 21:00

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Umesh Aawte · Accepted Answer · 2012-07-03 06:15:54Z

1

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)

edited Jul 3, 2012 at 6:15

Umesh Aawte

4,7007 gold badges44 silver badges52 bronze badges

answered Jul 3, 2012 at 5:21

Michele Ranum

111 bronze badge

Comments

Dan J · Accepted Answer · 2010-11-29 21:01:29Z

0

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

answered Nov 29, 2010 at 21:01

Dan J

16.8k7 gold badges53 silver badges82 bronze badges

Collectives™ on Stack Overflow

EF query with conditions from multiple tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related