CODEWITHSUNDEEP
c#sqlentity-frameworkmany-to-manyfluent-interface
kghantous
kghantous

Reputation: 149

Entity Framework many to many with 2 tables

Scenario

I'm working with an existing database that contains two tables, which create a many-to-many relationship because they are not properly normalized. The table structures look like the following.

Employee (table)

 - PersonId
 - JobId
 - JobTitle
 - OfficeLocation
 - EmailAddress

Projects (table)

 - PersonId
 - JobId
 - Supervisor
 - ProjectName

I have the following POCOs:

public class Employee{
    public int PersonId{get;set;}
    public int JobId{get;set;}
    //...
    public virtual ICollection<Project> Projects{get;set;}
}

public class Project{
    public int PersonId{get;set;}
    public int JobId{get;set;}
    //...
    public virtual Employee Employee{get;set;}
}

I'm wiring this up using an EF Fluent API call

this.HasRequired(p=>p.Employee)
.WithMany(e=>e.Projects)
.HasForeignKey(p=>new {p.PersonId, p.JobId});

Problem

The situation is that some employees have multiple job titles and locations which generates multiple Employee records, and then each employee has multiple projects. The problem is that Project.Employee is returning multiple records and generates the following error:

A relationship multiplicity constraint violation occurred: An EntityReference can have no more than one related object, but the query returned more than one related object. This is a non-recoverable error.

I've attempted to change the Project class to 

public virtual ICollection<Employee> Employee{get;set;}

with

this.HasMany(e=>e.Courses)
.WithMany()

But EF expects there to be a 'tweener table relating employee to projects.

I realize there are problems with the database structure, but at this time I'm left to work with this setup. Is it possible to do this type of relationship using Entity Framework or does any have a recommendation on how to tackle this?

Upvotes: 2

Views: 1013

Answers (3)

Anshul Nigam
Anshul Nigam

Reputation: 1628

looking at your poco class it appears that many to many relation is not well defined Try changing project to public class Project{ public int PersonId{get;set;} public int JobId{get;set;} //... public virtual ICollection <Employee> Employees{get;set;} }

changing 
public virtual ICollection&lt;Employee&gt; Employees{get;set;}
</code>

and do check there must be table with name ProjectEmployee or visa versa in DB.

Upvotes: 0

mmilleruva
mmilleruva

Reputation: 2178

My first recommendation would be to fix the schema, but if that is not possible, I ran into a somewhat similar issue on our project and what worked best for us was creating two DBContexts. In your case, each context would have a one to many relationship going in each direction. Depending on which way you wanted to query the data, either Employee->Project or Project -> Employee would dictate which DBContext you used. If you are using the fluent API you can keep all of your pocos the same and just have two different mapping config files. Here is a blog post I wrote on the issue.

http://blog.mvmiller.net/2013/10/working-with-legacy-database-schemas.html

Upvotes: 1

Ahmed Ghazey
Ahmed Ghazey

Reputation: 489

I Suggest you make another associative Entity One to Many for Each of Them call the associative Entity (EmployeeProjects) which is One to Many for both of Employee and Projects PK will be ProjectID+EmployeeID.

Upvotes: 0

Related Questions