Delete Cascade is not working with NHibernate

Question

I have a table Communication which has a reference to PersonCompany. In the mapping for PersonCompany i have defined a Cascade-Delete for this reference:

this.HasMany(x => x.Communications)
  .AsSet()
  .KeyColumn("PersonCompanyId")
  .Fetch.Select()
  .Inverse()
  .Cascade.Delete();

But when I now execute the fallowing HQL-Query:

var sql = "delete from PersonCompany where Person.Id in (:idList) or Company.Id in (:idList)";

with

var query = NHibernateHelper.CurrentSession.CreateQuery(sql);
query.SetParameterList("idList", contactIdList);
query.SetTimeout(0);
query.ExecuteUpdate();

I always get this SqlException:

The DELETE statement conflicted with the REFERENCE constraint "FK_PersonCompany_Communication". The conflict occurred in database "proconact", table "dbo.Communication", column 'PersonCompanyId'. The statement has been terminated.

I think, NHibernate should now delete cascade the referenced records in Communication - should'nt it?

I hope someone can help me, what I am doing wrong.

score 1 · Accepted Answer · answered Jun 29 '14 at 15:06

1

The syntax you've used is in fact part of the

13.3. DML-style operations,

which are in fact used to BULK operation on the DB server. They use the HQL syntax, but do not cover the cascade (because they are not executed in memory, just on the DB side)

This way, we can load the objects, to be deleted... and explicitly delete them. This will trigger cascades:

//var sql = "delete from PersonCompany where Person.Id in (:idList) or Company.Id in (:idList)";
var sql = "from PersonCompany where Person.Id in (:idList) or Company.Id in (:idList)";
var query = NHibernateHelper.CurrentSession.CreateQuery(sql);
query.SetParameterList("idList", contactIdList);
query.SetTimeout(0);
//query.ExecuteUpdate();
var list = query.List<PersonCompany >();
foreach (var item in list)
{
    session.Delete(item);
}
session.Flush();

What happened is, that each and every item to be deleted, and placed in ISession. During the Delete() all the cascades were properly executed

9.9. Lifecyles and object graphs

answered Jun 29 '14 at 15:06

Radim Köhler

122,561
47
239
335

In my understanding, cascade delete will work with code from "BennoDual" if it is set on database side by `ON DELETE CASCADE`. Is this correct understanding? – Amit Joshi Dec 05 '16 at 07:47
@A_J, I'd bet a lot that you know the answer. BUT I would say, that applying DB side cascade, will simply be executed regardless of any external SQL tool handler (including ORM, NHibernate). I would not treat this as "**code** would work if DB is set..." because in that case it is out of app domain, out of our hands. Our **code** was just a silent trigger. Sooner or later that setting *(as well as magic triggers)* will lead to long, frustrating searching "why those rows have disappeared". Other words,my experience would protect me form comparing hidden DB setting and application mapping/code. – Radim Köhler Dec 05 '16 at 08:29
Agreed. But your answer confused me. If constraint on database side can handle the cascades, why should he load all entities just to delete them? Are you suggesting this for caching consistency then? – Amit Joshi Dec 05 '16 at 09:08
1

@A_J The *trigger* on a DB side is coming with your comment. Not with question, nor with my answer. Not sure how could confuse you then... I am (in my answer) working just with the NHIBERNATE cascading... and that is working on application side, only if you'll load stuff into session. Hope it is a bit more clear now – Radim Köhler Dec 05 '16 at 10:40

score 0 · Answer 2 · answered Jun 30 '14 at 05:51

way to do is,

IList<PersonCompany> _pCompanies = ....; <load the required person companies>
foreach (var pc in _pCompanies)
{
_session.delete(pc);
}

because when you use bulk updates, built in constraints is not going to work in Nhibernate. You could try to create a DB level constraint.

Delete Cascade is not working with NHibernate

2 Answers2

Linked