Many to Many delete in NHibernate two parents with common association
- by Joshua Grippo
I have 3 top level entities in my app: Circuit, Issue, Document
Circuits can contain Documents and Issues can contain Documents.
When I delete a Circuit, I want it to delete the documents associated with it, unless it is used by something else. I would like this same behavior with Issues. I have it working when the only association is in the same table in the db, but if it is in another table, then it fails due to foreign key constraints.
ex 1(This will cascade properly, because there is only a foreign constraint from Circuit to Document)
Document1 exists.
Circuit1 exists and contains a reference to Document1.
If I delete Circuit1 then it deletes Document1 with it.
ex 2(This will cascade properly, because there is only a foreign constraint from Circuit to Document.)
Document1 exists.
Circuit1 exists and contains a reference to Document1.
Circuit2 exists and contains a reference to Document1.
If I delete Circuit1 then it is deleted, but Document1 is not deleted because Circuit2 exists.
If I then delete Circuit2, then Document1 is deleted.
ex 3(This will throw an error, because when it deletes the Circuit it sees that there are no other circuits that reference the document so it tries to delete the document. However it should not, because there is an Issue that has a foreign constraint to the document.)
Document 1 exists.
Circuit1 exists and contains a reference to Document1.
Issue1 exists and contains a reference to Document1.
If I delete Circuit1, then it fails, because it tries to delete Document1, but Issues1 still has a reference.
DB:
This think won't let upload an image, so here is the ERD to the DB: http://lh3.ggpht.com/_jZWhe7NXay8/TROJhOd7qlI/AAAAAAAAAGU/rkni3oEANvc/CircuitIssues.gif
Model:
public class Circuit
{
public virtual int CircuitID { get; set; }
public virtual string CJON { get; set; }
public virtual IList<Document> Documents { get; set; }
}
public class Issue
{
public virtual int IssueID { get; set; }
public virtual string Summary { get; set; }
public virtual IList<Model.Document> Documents { get; set; }
}
public class Document
{
public virtual int DocumentID { get; set; }
public virtual string Data { get; set; }
}
Mapping Files:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Model" assembly="Model">
<class name="Circuit" table="Circuit">
<id name="CircuitID">
<column name="CircuitID" not-null="true"/>
<generator class="identity" />
</id>
<property name="CJON" column="CJON" type="string" not-null="true"/>
<bag name="Documents" table="CircuitDocument" cascade="save-update,delete-orphan">
<key column="CircuitID"/>
<many-to-many class="Document">
<column name="DocumentID" not-null="true"/>
</many-to-many>
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Model" assembly="Model">
<class name="Issue" table="Issue">
<id name="IssueID">
<column name="IssueID" not-null="true"/>
<generator class="identity" />
</id>
<property name="Summary" column="Summary" type="string" not-null="true"/>
<bag name="Documents" table="IssueDocument" cascade="save-update,delete-orphan">
<key column="IssueID"/>
<many-to-many class="Document">
<column name="DocumentID" not-null="true"/>
</many-to-many>
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Model" assembly="Model">
<class name="Document" table="Document">
<id name="DocumentID">
<column name="DocumentID" not-null="true"/>
<generator class="identity" />
</id>
<property name="Data" column="Data" type="string" not-null="true"/>
</class>
</hibernate-mapping>
Code:
using (ISession session = sessionFactory.OpenSession())
{
var doc = new Model.Document() { Data = "Doc" };
var circuit = new Model.Circuit() { CJON = "circ" };
circuit.Documents = new List<Model.Document>(new Model.Document[] { doc });
var issue = new Model.Issue() { Summary = "iss" };
issue.Documents = new List<Model.Document>(new Model.Document[] { doc });
session.Save(circuit);
session.Save(issue);
session.Flush();
}
using (ISession session = sessionFactory.OpenSession())
{
foreach (var item in session.CreateCriteria<Model.Circuit>().List<Model.Circuit>())
{
session.Delete(item);
}
//this flush fails, because there is a reference to a child document from issue
session.Flush();
foreach (var item in session.CreateCriteria<Model.Issue>().List<Model.Issue>())
{
session.Delete(item);
}
session.Flush();
}
