Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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
share|improve this question

1 Answer

up vote 1 down vote accepted

You can do this faily easily with the Where / Any methods:

var results = dbContext.Employees.Where(e => e.EmployeeTypes.Any(et => et.Id == 2));

Or if you prefer query syntax:

var results =
    from e in dbContext.Employees
    where e.EmployeeTypes.Any(et => et.Id == 2)
    select e;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.