LINQ - Querying a list filtered via a Many-to-Many reltionship

Question

Please excuse the context of my question for I did not know how to exactly word it.

To not complicate things further, here's my business requirement: "bring me back all the Employees where they belong in Department "X".

So when I view this, it will display all of the Employees that belong to this Department.

Here's my environment: Silverlight 3 with Entity Framework 1.0 and WCF Data Services 1.0. I am able to load and bind all kinds of lists (simple), no problem. I don't feel that my environment matters and that's why I feel it is a LINQ question more than the technologies.

My question is for scenarios where I have 3 tables linked, i.e. entities (collections).

For example, I have this in my EDM: Employee--EmployeeProject--Project.

Here's the table design from the Database:

Employee (table1)
-------------
EmployeeID (PK)
FirstName
other Attributes ...

EmployeeProject (table2)
-------------
EmployeeProjectID (PK)
EmployeeID (FK)
ProjectID (FK)
AssignedDate
other Attributes ...

Project (table3)
-------------
ProjectID (PK)
Name
other Attributes ...

Here's the EDM design from Entity Framework:

------------------------
Employee (entity1)
------------------------
(Scalar Properties)
-------------------
EmployeeID (PK)
FirstName
other Attributes ...
-------------------
(Navigation Properties)
-------------------
EmployeeProjects

------------------------
EmployeeProject (entity2)
------------------------
(Scalar Properties)
-------------------
EmployeeProjectID (PK)
AssignedDate
other Attributes ...
-------------------
(Navigation Properties)
-------------------
Employee
Project

------------------------
Project (entity3)
------------------------
(Scalar Properties)
-------------------
ProjectID (PK)
Name
other Attributes ...
-------------------
(Navigation Properties)
-------------------
EmployeeProjects

So far, I have only been able to do:

var filteredList = Context.Employees
    .Where(e => e.EmployeeProjects.Where(ep => ep.Project.Name == "ProjectX"))

NOTE: I have updated the syntax of the query after John's post.

As you can see, I can only query, the related entity (EmployeeProjects). All I want is being able to filter to Project from the Employee entity.

Thanks for any advice.

Answer 1

If I understand your question properly, you're looking for something like this:

var filteredList = employees.Where(e => e.EmployeeProjects.Count(ep => ep.Project.Name == "Some project name") > 0)