How to enable cascading delete in Edmx Designer on many to many relation

Question

I am using VS2012 and the Entity designer to generate both the database and the models. I have a very basic scenario of Table1 to Table1and2JoinTable to Table2. Something like Students, Classes, StudentClasses. You can have many students in many classes. I would like to have a cascading delete. So if you delete a student any rows in the StudentClass join table are deleted for that student id. Same for deleting a class any rows in the StudentClass are deleted for that class id. After setting up the many to many association in the designer and setting the cascade delete options you get the following error when you attempt to generate the database:

Error 132: End 'Student' on relationship 'Model1.StudentClass' cannot have operation specified since its multiplicity is ''. Operations cannot be specified on ends with multiplicity ''.

Here is a small example:

Here is the association created:

And the resulting error messages:

Here is a portion of the SQL code for generating the 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 work around this by just editing the database script and add in the on delete cascade option. But, I don't want to do this because I am going to come back to the designer many times as the project grows and I don't want to have to remember this step every time.

Has anyone figured out how to resolve this?

Answer 1

It seems to be an edmx restriction, which I don't really understand. Code-first is perfectly capable of generating a junction table with two cascading ON DELETE constraints, but model-first and database-first do not allow the same configuration in the edmx. Normally, cascade actions are configured on the 'one' end of an association. Maybe it is too complex to check the validity of cascade actions on '*' ends (only when both ends are '*').

For the cascaded delete to happen with a context based on an edmx model, you have to load a parent and its children and then delete the parent.

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

The executed SQL statements show that the Class is fetched from the database in a JOIN statement with Student. Then the StudentClasss and the Class are deleted respectively.

Obviously, this is much more expensive than enabling cascaded delete in the database.

The work-around to modify the DDL each time after is was generated is not attractive, of course. But I think the only alternative is to make StudentClass part of the model and configure cascaded delete on the 'one' ends of the new associations. Or go code-first.