Entity Framework many to many relationship with extra field (Database First)

Question

I have a program using Entity Framework (EF) Database First in the data access layer. I know that in order for EF to auto-generate the navigation properties of a many to many relationship, the mapping table needs to contain only a composite primary key:

Project
  - ProjectId (PK)
  - Name

ContentType
  - ContentTypeId (PK)
  - Name

ProjectContentTypeMapping
  - ProjectId (PK)
  - ContentTypeId (PK)

In this case everything works fine, and I can access Projects from ContentTypes and the other way around with navigation properties.

However I have a requirement to have extra fields that are particular to the relation between Projects and ContentTypes, and that would be extra columns in the ProjectContentTypeMapping table. Once I add these I loose the navigation properties, and EF shows the mapping table in the designer.

Is there any way I can manually configure the mapping between these two tables in EF (Database First)? Alternatively, how can I represent this? I was thinking of maybe having an extra "metadata" table with a FK to the mapping table, but it looks "hacky" to me...

Thanks

Discordant

Answer 1

No, you cannot have extra columns in your mapping table in entity framework. Because, having an extra column implies you intend to use it, but Mapping tables are not part of the entity, so, entity framework, no longer treats your mapping table with extra columns as a Mapping table. You will have to manipulate the Mappings manually.

Lets take example of your classes:

Project
  - ProjectId (PK)
  - Name
  - ProjectContents

ContentType
  - ContentTypeId (PK)
  - Name
  - ProjectContents

ProjectContentTypeMapping
  - ProjectId (PK)
  - ContentTypeId (PK)
  - OtherRelevantColumn

where ProjectContents is of type ProjectContentTypeMapping

so whenever you add a Project with Certain ContentType, you would be doing this:

Project prj = new Project();
//fill out scalar properties
ProjectContentTypeMapping pctm = new ProjectContentTypeMapping();
pctm.ContentTypeId = 1; //or whatever you want, or selected from UI

prj.ProjectContents = new ProjectContentTypeMapping();
prj.ProjectContents.Add(pctm);

dataContext.Projects.Add(prj);

Now in case of editing (adding ContentTypes) to an existing project, you won't do prj.ProjectContents = new ... rather, you would do it only when it is null i,e,

if(prj.ProjectContents==null)
   prj.ProjectContents = new ProjectContentTypeMapping();

also, one very very important thing, since now your ProjectContentTypeMapping is no longer a mapping table, so, deleting a Project would give you an error, if its id is being present in ProjectContentTypeMapping table; you will have to remove them manually i.e

foreach(var pctm in prj.ProjectContents.ToList())
{
   prj.ProjectContents.Remove(pctm);
   datacontext.ProjectContentTypeMappings.Remove(pctm);
}
datacontext.Remove(prj);
datacontext.SaveChanges();

Commodus

Can you provide more information on how to manipulate the mappings manually? – Discordant

Isn't it possible to have the extra columns but tell Entity Framework to treat the table as if it didn't? To somehow only import two columns instead of the whole table from the database? I need the additional columns in the DB for other reasons (workplace policy, other applications which use the same database) but my application doesn't even have to be aware of them. – Orangery