LINQ - Querying a list filtered via a Many-to-Many reltionship
- by user118190
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.