I need help filtering a LINQ query on an Entity Framework (EF) navigation property. The tables were created first (not code-first).
For this problem, let's use the characters from The Office.
Suppose I have the following tables and columns:
Offices Id Name Location Employees Id Name EmployeeTypes Id Description Employees_EmployeeTypes Id EmployeeId EmployeeTypeId The tables have the following data:
Offices 1 North Branch PA 2 South Branch FL 3 East Branch NY Employees 1 Jim 2 Pam 3 Oscar 4 Dwight 5 Michael 6 Angela 7 Kevin 8 Stanley EmployeeTypes 1 Manager 2 Salesman 3 Assistant 4 Receptionist 5 Accountant Employees_EmployeeTypes 1 1 2 2 2 4 3 3 5 4 4 2 5 5 1 6 6 5 7 7 5 8 8 2 These tables are brought into EF using the "Update Model From Database" functionality in Visual Studio.
All entities are named exactly like the table names and the entity sets are pluralized.
Entity Name Entity Set Name Office Offices Employee Employees EmployeeType EmployeeTypes The Employees_EmployeeTypes table is a junction table (many-to-many), will translate into a navigation property in EF and is named "EmployeeTypes". Here is an example of its usage:
var employeeTypes = dbContext.Employees.Find(1).EmployeeTypes; Now that we have the setup, let's get to the question. Using LINQ, how can I select all employees who are a saleman using the navigation property "EmployeeTypes"? In SQL it would look like this:
select e.Id, e.Name from Employees e join Employees_EmployeeTypes e_et on e_et.EmployeeId = e.Id join EmployeeTypes et on et.Id = e_et.EmployeeTypeId where et.Id = 2 
