流利的NHibernate自引用多对多 [英] Fluent NHibernate Self Referencing Many To Many

问题描述

我有一个名为Books的实体，可以有一个名为RelatedBooks的书籍列表。

缩写的Book实体看起来像这样：

  public class Book 
 {
 public virtual long Id {get;私人设置} 
 
公共虚拟IList< Book>相关书籍{get;组; } 
 
 $ 
 
 $ p 
 $ b 
下面是这个关系的映射关系。 b 
 $ b 
  HasManyToMany（x => x.RelatedBooks）
 .ParentKeyColumn（BookId）
 .ChildKeyColumn（RelatedBookId ）
。表（RelatedBooks）
 .Cascade.SaveUpdate（）; 
  
以下是在相关书籍表中生成的数据示例：
 b 
 $ b 
  BookId RelatedBookId 
 1 2 
 1 3 
  
当我尝试删除一本书时，会发生问题。如果删除ID为1的图书，则一切正常，并且相关书籍表格中的两个对应记录已删除。但是，如果我尝试删除ID为3的书，则会出现DELETE语句与REFERENCE约束冲突的错误FK5B54405174BAB605，在数据库Test，表dbo.RelatedBooks，RelatedBookId列。 
 
 
基本上，发生的事情是Book不能被删除，因为相关书籍表中的相关书籍的记录不会被删除。
 
 
 
如何删除某本书时删除该记录？
 
 
 
 编辑 
 
 
从SaveUpdate（）更改为All（）后，同样的问题仍然存在，如果我尝试删除ID为3的Book。同时Cascade设置为All如果删除Book，ID为1，那么所有3本书（ID：1，2和3）都将被删除，以至于无法正常工作。
 
 
 
在删除ID为3的Book时调用Book.Delete（）方法时执行的SQL，它看起来像SELECT语句正在查看错误的列（我假设这意味着SQL DELETE语句会使同样的错误，因此从不删除该记录）。以下是相关书籍的SQL  
 
 
  SELECT relatedboo0_.BookId as BookId3_ 
，relatedboo0_.RelatedBookId as RelatedB2_3_ 
，book1_.Id as Id14_0_ 
 
 FROM RelatedBook relatedboo0_ 
 left outer join [Book] book1_ on relatedboo0_.RelatedBookId = book1_.Id 
 
 WHERE relatedboo0_.BookId = 3 
  
 WHERE语句应该看起来像这样的特殊情况：
 
 
  WHERE relatedboo0_.RelatedBookId = 3 
  
 
 
 <解决方案
    
 
 
这是我必须要做的， b 
映射：
 
 
  HasManyToMany（x => x.RelatedBooks）
 .ParentKeyColumn（BookId ）
 .ChildKeyColumn（RelatedBookId）
 .Table（RelatedBooks）; 
  
代码： 
 
 
  var book = currentSession.Get< Book>（bookId）; 
 
 if（book！= null）
 {
 //删除所有相关书籍
 book.RelatedBooks.Clear（）; 
 
 //获取所有其他将本书作为相关书籍的书籍
 var booksWithRelated = currentSession.CreateCriteria< Book>（）
 .CreateAlias（RelatedBooks，br ）
 .Add（Restrictions.Eq（br.Id，book.Id））
 .List&Book>（）; 
 
 //将这本书作为所有其他书籍的相关书籍删除
 foreach（bookWithRelated中的tempBook）
 {
 tempBook.RelatedBooks.Remove（book）; 
 tempBook.Save（）; 
} 
 
 //删除书
 book.Delete（）; 
 
  
 
 
解决方案
 ，我想你只需要在删除书籍之前清空相关书籍集合即可。 
 
 
  book.RelatedBooks.Clear（）; 
 session.Delete（book）; 
  
级联删除通常不会在多对多关系中完成，因为它会删除关系的另一端，在这种情况下是一本书。
 
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();
}

解决方案
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.

                        
这篇关于流利的NHibernate自引用多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！