How to enable cascading deletion in Edmx Designer in many ways

Question

I use VS2012 and the Entity constructor to create both a database and models. I have a very simple script, Table1 to Table1and2JoinTable for Table2. Something like students, classes, StudentClasses. You can have many students in many classes. I would like to have cascading deletion. Therefore, if you delete a student, any rows in the StudentClass join table are deleted for this student ID. The same goes for class deletion; any rows in StudentClass are deleted for this class identifier. After you created the association "many to many" in the designer and set the parameters for removing the cascade, you get the following error when trying to create a database:

Error 132: End 'Student' in relation to 'Model1.StudentClass' cannot have the specified operation because its multiplicity is equal to ''. Operations cannot be specified at the ends with a multiplicity. ''

Here is a small example:

Here is the association created:

And the error messages received:

Here is the part of the SQL code for generating database tables:

-- Creating foreign key on [Students_Id] in table 'StudentClass' ALTER TABLE [dbo].[StudentClass] ADD CONSTRAINT [FK_StudentClass_Student] FOREIGN KEY ([Students_Id]) REFERENCES [dbo].[Students] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; -- This should be ON DELETE CASCADE ON UPDATE NO ACTION; GO -- Creating foreign key on [Classes_Id] in table 'StudentClass' ALTER TABLE [dbo].[StudentClass] ADD CONSTRAINT [FK_StudentClass_Class] FOREIGN KEY ([Classes_Id]) REFERENCES [dbo].[Classes] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; -- This should be ON DELETE CASCADE ON UPDATE NO ACTION; GO 

I know how to get around this by simply editing the script database and adding the on delete cascade option. But I do not want to do this, because I am going to return to the designer many times when the project is growing, and I do not want to remember this step every time.

Has anyone figured out how to solve this?

Answer 1

This seems to be an edmx limitation that I really don't understand. The first code is quite capable of generating a connection table with two cascading DEL DELE constraints, but at first the models and databases do not allow using the same configuration in edmx. Usually cascading actions are configured at the "one" end of the association. It may be too difficult to verify the correctness of cascading actions at the ends of " * " (only if both ends are " * ").

For cascading deletion to happen with the context based on the edmx model, you need to load the parent element and its children, and then delete the parent element.

 var cls = db.Classes.Include(c => c.Students).Single(c => c.Id = 1); db.Classes.Remove(cls); db.SaveChanges(); 

Executed SQL statements indicate that the Class is retrieved from the database in the JOIN statement using Student . Then StudentClass and Class are deleted respectively.

Obviously, this is much more expensive than including cascading deletes in the database.

The workaround to change the DDL every time after it has been created, of course, is not attractive. But I think that the only alternative is to create StudentClass part of the model and configure cascading deletion at the ends of new associations. Or enter the code first.

Answer 2

First of all, make sure that you have ON DELETE CASCADE specified in the Foreign Keys on the database side. I had a similar problem and just adding ON DELETE CASCADE to solve it instead of setting the End1OnDelete and End2OnDelete .