How to use Entity Framework to work with many-to-many relationships?

Question

I have a SampleDB that contains 3 tables.

Table Employees (EmployeeID, EmployeeName)
Table Projects (ProjectID, ProjectName)
Table ProjectResources (ProjectID, EmployeeID)

The ProjectResources table is cross reference table that creates a many-to-many relationship between Employees and Projects.

I would like to use LINQ to select all the Employees that have not yet been assigned to a particular project. Here are the steps I took:

1. Created an entity data model from the SampleDB above using the Entity Framework wizard. The wizard create two entities: Employees and Projects, which I renamed to be Employee and Project. The Project entity has an Employees navigation property that references a collection of Employees and the Employee entity has a Projects navigation property that references a collection of Projects. So it looks as if EF has correctly identified my many-to-many relationship between Employees and Projects table.

2. Now here is the code I used to attempt to select all the Employees that have not yet been assigned to a Project.

       SampleDBEntities db = new SampleDBEntities();
       var project = db.Projects.Include("Employees")
                                .FirstOrDefault(p => p.ProjectID == 1);
       var currentEmployees = project.Employees;
       var employeesNotAssignedToProject = 
               db.Employees.Except(currentEmployees);
   

var project loads fine with the Project that has ProjectID of 1 var currentEmployees loads fine with a list of Employees currently assigned to that Project

Then I get the following exception when I attempt to watch the resultsview ofemployeesNotAssignedToProject in the watch window:

{"Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}

So questions are:

1. Why am I getting this exception?
2. Is there another way (that works) of trying to accomplish this type of task? Notice that I am trying to use the "Except" method. Perhaps there is a better way.

Answer 1

What about this:

var employeesNotAssignedToProject = db.Employees.Select(e => e).Where(e => (e.Projects.Count(c => c.ProjectID == 1)) == 0)

I haven't tested this, but basically what it does is it selects only those employees where their Projects collection does not include the project in question by checking the count of projects with the given id.