C# LINQ multiple one to many includes with where condition

Question

I can't find the correct query to get all the data I need.

I've got three sample tables:

Employee (Id, Name, Telephone)
EmployeeDepartment (Id, EmployeeId, DepartmentId)
DepartmentAddress (Id, DepartmentId, City, Street, IsActive...)

Each of this tables has one-to-many relation with next one. An Employee can be assigned to many departments and one department can have many addresses assigned.

I'm trying to write an Entity Framework query which will return, for example, all employees which are assigned to department in City of New York. My problem is that I don't want just employees. I need it to also include all properties of EmployeeDepartment and DepartmentAddress in Employee class.

What I'm trying to do is something like:

var matchingEmployees = ctx.Employee
.Include("EmployeeDepartment")
.Include("EmployeeDepartment.DepartmentAddress")
.Where(p=> p.EmployeeDepartment
.Where(x=>x.DepartmentAddress.Where(y=>y.City == "New York)))
.ToList();

As a result I get all Departments and all EmployeeDepartments, when I need only ones matching Where condition. What query will return the data I need?

invernomuto · Accepted Answer · 2015-07-03 22:13:04Z

0

You don't need to use the where like that just get to the condition directly. I think its something like that, if you post your entities it would be a little more easier.

 var matchingEmployees = ctx.Employee
    .Include("EmployeeDepartment")
    .Include("EmployeeDepartment.DepartmentAddress")
    .Where(p=> p.DepartmentAddress.City == "New York)))
    .ToList();

edited Jul 3, 2015 at 22:13

invernomuto

10.2k5 gold badges42 silver badges54 bronze badges

answered Jul 3, 2015 at 22:10

Luis Tellez

3,0331 gold badge23 silver badges30 bronze badges

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

4 Comments

MattheW Over a year ago

Thank you for answer. Wouldn't that only work if there were one-to-one relationships?

Luis Tellez Over a year ago

Try it :) It doesn't matters if its one to one or not, if it doesn't work post your entity with at least the properties you need to use.

AaronLS Over a year ago

@MattheW It will work assuming it is a many-to-one relationship where each employee has one Department, and each Department has one address. It depends on which direction you are navigating the many-to-one.

MattheW Over a year ago

Actually it's the opposite - one employee can be assigned to many departments and one department can have assigned many addresses. Sorry if that wasn't clear in original question. :(

Dherik · Accepted Answer · 2015-07-03 22:56:06Z

0

Maybe this:

ctx.Employee
    .Include( e => e.EmployeeDepartment)
    .Include( e => e.EmployeeDepartment.DepartmentAddress)
    .Where(e => && e.EmployeeDepartment.DepartmentAddress.All(y => y.City == "New York")).ToList();

answered Jul 3, 2015 at 22:56

Dherik

19.4k15 gold badges131 silver badges177 bronze badges

1 Comment

Dherik Over a year ago

I'm not able to test this, but if works I recommend to inspect the generated SQL to see how the Entity Framework resolve this query.

AaronLS · Accepted Answer · 2015-07-03 23:07:20Z

0

"What I'm trying to do is write Entity Framework query, which will return, for example, all employees which are assigned to department in City of New York."

If you're not interested in actually retrieving the departments, and only want to filter by them, then a join would be more appropriate. It's easier for other developers to see that your intention is to narrow down the list of employees, rather than simply narrowing down the list of nested department addresses. This can be accomplished in extension syntax(which I prefer), but joins are much more readable in expression syntax:

var employeesInNewYorkDepartments = 
    (from employee in ctx.Employee
    let department = employee.EmployeeDepartment
    let departmentAddress = department.DepartmentAddress
    where departmentAddress.City == "New York" 
    select employee).ToList();

Hopefully I don't have syntax errors. This assumes the relationship from employee to department is many-to-one. I.e. many employees are in a department.

If it's the opposite for both relationships:

var employeesInNewYorkDepartments = 
    (from employee in ctx.Employee
    join department in employee.EmployeeDepartments    
    join departmentAddress in department.DepartmentAddresses
    where departmentAddress.City == "New York" 
    select employee).Distinct().ToList();

Note your navigation property name should be plural when navigating in the many direction. They represent access to a collection and therefore a singular navigation property name is extremely misleading. (The entity class name would still be singular though as it declares the structure of a single item.)

edited Jul 3, 2015 at 23:07

answered Jul 3, 2015 at 22:24

AaronLS

38.6k24 gold badges151 silver badges217 bronze badges

2 Comments

MattheW Over a year ago

Thank you for answer. Actually it's the opposite - one employee can be assigned to many departments and one department can have assigned many addresses. Sorry if that wasn't clear in original question.

AaronLS Over a year ago

@MattheW I've added another example that might help you.

Collectives™ on Stack Overflow

C# LINQ multiple one to many includes with where condition

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related