When deleting an entity with many to many relationship in nhibernate the relationship is not deleted

Question

I am using NHibernate and C#. I have two entities with many to many relationship between them. When I delete the parent entity, I only have it's ID and I delete it with an hql query.

My problem is that it only deletes the parent entity without deleting it's relationships.

My Entities look like this:

public class Entity_A
{
      public virtual int Code { get; set; }
      public virtual int Id { get; set; }
      public virtual ICollection<Entity_B> Entities_B { get; set; }
}

public class Entity_B
{
      public virtual int Code { get; set; }
      public virtual ICollection<Entity_A> Entities_A { get; set; }
}

mapping

public class EntityAMap : ClassMap<Entity_A>
{
      public EntityAMap()
      {
            Table("ENTITY_A");
            Id(x=>x.Code).GeneratedBy.Identity();
            Map(x=>x.Id).column("A_ID").Not.Nullable();
            HasManyToMany(x->x.Entities_B)
                    .LazyLoad()
                    .Generic()
                    .PropertyRef("Id")
                    .ChildKeyColumn("B_CODE")
                    .ParentKeyColumn("A_ID")
                    .Table("ENTITY_A_TO_ENTITY_B")
                    .Cascade.All();
      }
}

public class EntityBMap : ClassMap<Entity_B>
{
      public EntityBMap()
      {
            Table("ENTITY_B");
            Id(x=>x.Code).GeneratedBy.Identity();
            HasManyToMany(x->x.Entities_A)
                    .Generic()
                    .ChildPropertyRef("Code")
                    .ChildKeyColumn("A_ID")
                    .ParentKeyColumn("B_CODE")
                    .Table("ENTITY_A_TO_ENTITY_B")
                    .Cascade.All()
                    .Inverse();
      }
}

My question is this, what should I change so that when I delete Entity_A with NHibernate HQL query it would also delete all it's relationships with Entity_B (from table ENTITY_A_TO_ENTITY_B).

Answer 1

In case, that your code is lookin like this:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

String hqlDelete = "delete Entity_A ea where ea.Code = :code";
int deletedEntities = session.CreateQuery( hqlDelete )
        .SetString( "code", codeToDelete )
        .ExecuteUpdate();
tx.Commit();
session.Close();

(so if the code looks like the above) then:

1. we are not using HQL as a way how to load Entities into session (and let NHibernate do the magic) -
2. but we are using so called DML

see the doc:

13.3. DML-style operations

As already discussed, automatic and transparent object/relational mapping is concerned with the management of object state. This implies that the object state is available in memory, hence manipulating (using the SQL Data Manipulation Language (DML) statements: INSERT, UPDATE, DELETE) data directly in the database will not affect in-memory state...

Mostly this is the answer: ...will not affect in-memory state...

Simply, this way we do just use DML to effectively issue WRITE statement while using HQL (query language on top of our entities, not SQL)

SOLUTION:

1) Load the instance into memory. We can use HQL, or QueryOver, ICriteria... The important here is just to LOAD INTO MEMORY, resp into ISession

That way, NHiberante on DELETE could start to issue all the expected cascades...

2) use .CreateSQLQuery() to manually delete the rest as well:

session
     .CreateSQLQuery("DELTE FROM ENTITY_A_TO_ENTITY_B WHERE ENTITY_A = :id)
     .SetString( "id", idToDelete )
     .ExecuteUpdate();
...
session.CreateQuery( hqlDelete )

This (second approach) will support effective SQL Statements (without loading instance into session), but will require a bit more coding on our side (NHibernate can cast spells just with its session)