Fluent NHibernate Self Referencing Many To Many
- by Jeremy
I have an entity called Books that can have a list of more books called RelatedBooks.
The abbreviated Book entity looks something likes this:
public class Book
{
public virtual long Id { get; private set; }
public virtual IList<Book> RelatedBooks { get; set; }
}
Here is what the mapping looks like for this relationship
HasManyToMany(x => x.RelatedBooks)
.ParentKeyColumn("BookId")
.ChildKeyColumn("RelatedBookId")
.Table("RelatedBooks")
.Cascade.SaveUpdate();
Here is a sample of the data that is then generated in the RelatedBooks table:
BookId RelatedBookId
1 2
1 3
The problem happens when I Try to delete a book. If I delete the book that has an ID of 1, everything works ok and the RelatedBooks table has the two corresponding records removed. However if I try to delete the book with an ID of 3, I get the error "The DELETE statement conflicted with the REFERENCE constraint "FK5B54405174BAB605". The conflict occurred in database "Test", table "dbo.RelatedBooks", column 'RelatedBookId'".
Basically what is happening is the Book cannot be deleted because the record in the RelatedBooks table that has a RelatedBookId of 3 is never deleted.
How do I get that record to be deleted when I delete a book?
EDIT
After changing the Cascade from SaveUpdate() to All(), the same problem still exists if I try to delete the Book with an ID of 3. Also with Cascade set to All(), if delete the Book with and ID of 1, then all 3 books (ID's: 1, 2 and 3) are deleted so that won't work either.
Looking at the SQL that is executed when the Book.Delete() method is called when I delete the Book with an ID of 3, it looks like the SELECT statement is looking at the wrong column (which I assume means that the SQL DELETE statment would make the same mistake, therefore never removing that record). Here is the SQL for the RelatedBook
SELECT relatedboo0_.BookId as BookId3_
, relatedboo0_.RelatedBookId as RelatedB2_3_
, book1_.Id as Id14_0_
FROM RelatedBooks relatedboo0_
left outer join [Book] book1_ on relatedboo0_.RelatedBookId=book1_.Id
WHERE relatedboo0_.BookId=3
The WHERE statment should look something like this for thie particular case:
WHERE relatedboo0_.RelatedBookId = 3
SOLUTION
Here is what I had to do to get it working for all cases
Mapping:
HasManyToMany(x => x.RelatedBooks)
.ParentKeyColumn("BookId")
.ChildKeyColumn("RelatedBookId")
.Table("RelatedBooks");
Code:
var book = currentSession.Get<Book>(bookId);
if (book != null)
{
//Remove all of the Related Books
book.RelatedBooks.Clear();
//Get all other books that have this book as a related book
var booksWithRelated = currentSession.CreateCriteria<Book>()
.CreateAlias("RelatedBooks", "br")
.Add(Restrictions.Eq("br.Id", book.Id))
.List<Book>();
//Remove this book as a Related Book for all other Books
foreach (var tempBook in booksWithRelated)
{
tempBook.RelatedBooks.Remove(book);
tempBook.Save();
}
//Delete the book
book.Delete();
}
