Entity Framework 4 - Delte many-to-many

Question

Hello anyone and everyone,

Please can you help me to enable the deleting of books from my database?

I am using EF 4 and have a many-to-many relationship between books and authors. When I try to delete a book, I get the following:

he DELETE statement conflicted with the REFERENCE constraint "FK_BookAuthor_Book". The conflict occurred in database "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10.SQLEXPRESS\MSSQL\DATA\NTCODING.MDF", table "dbo.BookAuthor", column 'BookAuthor_Author_Id'. The statement has been terminated.

Being a SQL guru is not what I do, but I think it is telling me that there is a record in the join table that also needs to be deleted. I'm not sure which property I need to set or what bit of coding I need to add.

If you can help me with this I would be really appreciative.

Thanks in advance

Nick

Answer 1

Looks like my sql knowledge wasn't too bad. I hoped that by removing the authors from the book's collection of authors the join table would remove the records....a la fantastico!

I had to do this in the controller action of my web application because I use a generic repository and do not cast it to a book in there. So if you do know a way I could enforce this rule in my repository instead, that would be useful still.

Thanks

Answer 2

When you create the foreign key relation between the table book and the table lying between book and author (the one that breaks the many-to-many relationship in two one-to-many relationship), try to specify the "cascade" action at "insert and update specification". The same for the link between authors and the middle table. Thus, when you try to delete an author (or a book), all the dependent records from the middle table will be deleted.