How can I add a new association(many-to-many) to existing entities in Entity Framework?

Question

There are 3 tables in my database: "Projects", "Users" and "UsersInProjects". In "UsersInProjects" table only two fields, without primary key. MS Entity Framework generated classes by data base, that there is collection of users in project and collection of projects in user. It's great! But I can't add project to user and user to project:

user.Projects.Add(project);
Entities.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

or

project.Users.Add(user);
Entities.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

or

user.Projects.Add(project);
project.Users.Add(user);
Entities.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

like in this sample on codeproject.

At each attempt I have exception: "Unable to update the EntitySet 'UsersInProjects' because it has a DefiningQuery and no element exists in the element to support the current operation."

Can I add an association without stored procedures and whitout addition primary key to "UsersInProjects" table?

I got it! Next query do table and EF work correctly with data base:

USE [BugTracker] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[UserProject]( [Users_Id] [int] NOT NULL, [Projects_Id] [int] NOT NULL, CONSTRAINT [PK_UserProject] PRIMARY KEY NONCLUSTERED ( [Users_Id] ASC, [Projects_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserProject] WITH CHECK ADD CONSTRAINT [FK_UserProject_Project] FOREIGN KEY([Projects_Id]) REFERENCES [dbo].[Project] ([Id]) GO

ALTER TABLE [dbo].[UserProject] CHECK CONSTRAINT [FK_UserProject_Project] GO

ALTER TABLE [dbo].[UserProject] WITH CHECK ADD CONSTRAINT [FK_UserProject_User] FOREIGN KEY([Users_Id]) REFERENCES [dbo].[User] ([Id]) GO

ALTER TABLE [dbo].[UserProject] CHECK CONSTRAINT [FK_UserProject_User] GO

Ladislav Mrnka Ladislav Mrnka · Accepted Answer · 2011-03-14T18:40:03

Entity framework requires that each table must have primary key to be updatable. In your case you must modify junction table and define both FKs as PK. There is no other way except stored procedures.

How can I add a new association(many-to-many) to existing entities in Entity Framework?

1 Answers