Fluent NHibernate Self Referencing Many To Many

Question

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();
}

Answer 1

Rather than setting the cascade attribute, I think you need to simply empty the RelatedBooks collection before deleting a book.

book.RelatedBooks.Clear();
session.Delete(book);

Cascading deletes is not typically done in a many-to-many relationship because it will delete the object at the other end of the relationship, in this case a Book.