CODEWITHSUNDEEP
asp.net-mvcentity-framework
Aserian
Aserian

Reputation: 1127

Remove entries from a table that maintains a many to many relationship asp.net mvc entity framework

I am writing an application based on an existing database. I have two tables, a server table and a support table (people who support the specified server). These tables can have a many to many relationship, and as such I cannot maintain a foreign key within one of the tables pointing to another.

The solution that the person who designed the schema came up with was to add a third table, a server support junction, that has just two columns - ServerID and SupportID, both foreign keys pointing to their respective table.

When I import this database schema into Entity Framework, it gives me the following warning:

Warning 2   Error 6002: The 
table/view 'dbo.Server_Support_Junction' does
 not have a primary key defined. The key has been inferred and the definition
 was created as a read-only table/view.

As such, the table does not appear in the edmx model and it does not create a class for the table.

As part of the application, I would like the DBA to be able to delete a server or a support (they leave the company/no longer support a certain server/etc). Is entity framework smart enough to see that this table is purely relational and will remove any connections when a support or server is deleted? Or must this be done explicitly?

If it must be done explicitly, what is a workaround for this? I tried adding a primary key called RelationID to the table, but it yelled at me saying that the primary key was not mapped or something.

Upvotes: 0

Views: 566

Answers (1)

Aserian
Aserian

Reputation: 1127

Gert Arnold helped to find the solution. First, a primary key was added to the table consisting of both the Foreign keys, the SQL was:

ALTER TABLE dbo.Server_Support_Junction 
ADD CONSTRAINT pk_ServerSupportJunc PRIMARY KEY (ServerID, SupportID)

I then updated the model by opening the edmx, right clicking on the blank space -> update model from database -> refresh -> finish.

To delete the relationship in the controller, the code was as follows:

Support support = db.Support.Find(id);
support.Servers.Clear();
db.Support.Remove(support);
db.SaveChanges();

Obviously you should do some error checking to make sure the entity was actually found, but that's the gist of it!

Special thanks to Gert Arnold!

Upvotes: 1

Related Questions