NHibernate collection delete

[Graham Bunce]

Hi,

I'm looking to ways of improving performance in NHibernate and came
across this in the documentation:

16.5.4. One shot delete

Occasionally, deleting collection elements one by one can be extremely
inefficient. NHibernate isn't completely stupid, so it knows not to do
that in the case of an newly-empty collection (if you called list.Clear
(), for example). In this case, NHibernate will issue a single DELETE
and we are done!

I'm not sure how to ensure this works as in my tests NHibernate never
does this e.g. If newContent is an object with a collection of Assets
and I want to delete the Content, I do this:

Session.Delete(content).

Assuming my mapping file is set to cascade="delete" the related assets
will get deleted, but line by line, i.e. based on their unique id, not
the fact that all have a contentId (SQL would be delete from Asset
where contentid = ?)

If I do a content.Assets.Clear(), from the above, I would expect
NHibernate to be really clever and do a one-shot delete.... but it
doesn't. In fact it crashes with a database referential integrity
error as no deletes for assets ever get submitted, presumably because
I've cleared the collection.

What am I doing wrong?

[Ayende Rahien]

You need to call clear for this to work

[Graham Bunce]

Thanks Ayende, but I'm being a bit stupid here.... I am calling Clear
() on my list (as per the example in the documentation) aren't I?

My list resolves to an IList<T> not an ISet and the mapping refers to
it as a bag (with an ID column). Does that make a difference?

[Fabio Maulo]

on-delete="cascade"

2008/11/26 Graham Bunce <graha...@hotmail.com>

--
Fabio Maulo

[Graham Bunce]

Ok.. well that doesn't work either. Mapping is as follows:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.2"
namespace="Domain.SupplyChain"
assembly="Domain.SupplyChain"
default-access="field.camelcase-underscore"
default-lazy="true">
<class name="Asset"
proxy="Domain.SupplyChain.Interfaces.IAsset,
Domain.SupplyChain.Interfaces"
table="Asset">
<id name="Id" column="Id" unsaved-
value="00000000-0000-0000-0000-000000000000" access="property">
<generator class="guid.comb" />
</id>
<property name="Name" type="string" length="255" not-null="true" /
>
<bag name="AssetContents" table="AssetContents" inverse="true"
cascade="none" >
<key column="AssetId" on-delete="cascade" />
<one-to-many class="AssetContent" />
</bag>
</class>
</hibernate-mapping>

I've tried it with cascade="delete" at bag level at the same time as
well, no difference.

My code is something like:

content.Assets.Clear();
session.Delete(content);

This caused a database RI crash, so I just tried "session.Delete
(content);" which deleted each AssetContent line by line as opposed to
"delete from AssetContent where contentid = ?".

Now, digging around on the internet for what on-delete="cascade"
actually means I came across an article that suggested it means
NHibernate leaves deletes of collections to the database, i.e. I need
to change the database RI to be ON_DELETE CASCADE. This is ok in my
scenario (its a new database) but now I have rules in my DB and in my
domain.

However, if I do have ON_DELETE CASCADE then I can change my map to
have on-delete="cascade" or not to have it and and long as I use a
list.Clear() method then it actually makes no difference at all, as
I'm using the DB to do the delete and not HNibernate. If I don't have
a list.Clear() then no matter what the map setting is (with or without
cascade) NHibernate will submit several deletes as opposed to one.

I really can't work out how to do what I want (single shot delete) in
NHibernate without changing my database RI.

I'm using NHIbernate 2.0.1GA

[Fabio Maulo]

what mean cascade="none" + on-delete="cascade" ?

2008/11/27 Graham Bunce <graha...@hotmail.com>

--
Fabio Maulo

[Fabio Maulo]

2008/11/27 Graham Bunce <graha...@hotmail.com>

I really can't work out how to do what I want (single shot delete) in
NHibernate without changing my database RI.

I don't understand...

You can have a one-shot-delete without call Clear() using on-delete="cascade" <===== Fact

You want a one-shot-delete without call Clear() <===== Fact

What mean "I really can't work out how to do what I want (single shot delete) in NHibernate without changing my database RI" ?

--
Fabio Maulo

[Graham Bunce]

Fabio,

I want to have one shot delete performed by NHibernate.

With the code - Session.Delete(newContent) and the above map in SQL
Profiler I see something like this:

DELETE FROM Content WHERE Id = ?

This causes a crash as my database does not have CASCADE DELETE.

I expect to see this:

DELETE FROM AssetContent WHEREContentId = ?
DELETE FROM Content WHERE Id = ?

Therefore on-delete="cascade" does not do one shot delete as I
understand it. As far as I can tell, it does nothing at all. I accept
I may not understand what it does so please explain - thanks.

[Fabio Maulo]

on-delete="cascade" make two things:

1) create in DB CASCADE DELETE

2) NH know that there is a CASCADE DELETE and run only one SQL

If you want a one-shot-delete you can use Clear or on-delete="cascade" (with CASCADE DELETE in DB).

What I don't understand is: I have two solution for my problem but I want another one.

Obviously the most efficient solution is on-delete="cascade"

2008/11/27 Graham Bunce <graha...@hotmail.com>

--
Fabio Maulo

[Graham Bunce]

Thanks Fabio, my problem was not that I wanted a third solution but
that I did not understand what on-delete="cascade" actually did. I
could not find any defintion of this property in the on-line reference
section 6.3. Collections of Values and Many-To-Many Associations. Now
you have explained it I do understand it, and its implications, so I
will use it.

I would suggest that the documentation is updated to include this
property and that the text related to my original post updated to
explain that list.Clear() also depends on database RI CASCADE DELETE
to work. In fact the text:

"Occasionally, deleting collection elements one by one can be
extremely inefficient. NHibernate isn't completely stupid, so it knows
not to do that in the case of an newly-empty collection (if you called

list.Clear(), for example). In this case, NHibernate will issue a

single DELETE and we are done! "

suggests that NHibernate will do the behaviour I expected (issue a
DELETE FROM AssetContent WHERE contentID = ?" whereas in fact from my
tests, it doesn't. This code:

newContent.Assets.Clear();
Session.Delete(newContent);

(where newContent.Assests is an IList<IAsset> mapped to the bag)
appears to remove the collection objects from the session so
NHibernate no longer knows about them - therefore no delete occurs for
these. Without database CASCADE the code will crash.


I'm happy to update the documentation for you but I do not know how
gain access to it or if this is something you would allow.

[Fabio Maulo]

2008/11/27 Graham Bunce <graha...@hotmail.com>

Thanks Fabio, my problem was not that I wanted a third solution but
that I did not understand what on-delete="cascade" actually did. I
could not find any defintion of this property in the on-line reference
section 6.3. Collections of Values and Many-To-Many Associations. Now
you have explained it I do understand it, and its implications, so I
will use it.

Yes I know... the time to write the doc is one of our problems; Where(our).IsEqualsTo(NH-community)

Our wiki, for reference, stay in the same state I leave it; Where(our).IsEqualsTo(NH-community)

http://nhforge.org/wikis/reference2-0en/default.aspx

BTW

http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#mapping-declaration-key

--
Fabio Maulo

[Jan Van Ryswyck]

Hi,

I'm still struggling with this issue myself:

- First of, I really like the on-delete="cascade" feature, but I can't
seem to convince our DBA's to include a CASCADE DELETE. Long story,
quite boring.
- Second, clearing a collection only seem to work on collections that
don't have inverse=true (correct me if I'm wrong about this).

Does this mean I'm out of options?


On Nov 27, 3:13 pm, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> 2008/11/27 Graham Bunce <grahambu...@hotmail.com>

> BTWhttp://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#map...
>
> --
> Fabio Maulo

[Fabio Maulo]

2008/11/28 Jan Van Ryswyck <jan.van...@gmail.com>

Does this mean I'm out of options?

IMO, does mean another thing...

probably somebody need an update

http://www.agiledata.org/essays/dbaSkills.html

--
Fabio Maulo

[Jan Van Ryswyck]

I agree 100%. But DBA rule the world over here, so, this leaves me
personally with no options. That was all I'm saying.
BTW: just read your post on Entities behavior injection. Well done!


On Nov 29, 12:19 am, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> 2008/11/28 Jan Van Ryswyck <jan.van.rysw...@gmail.com>

[Graham Bunce]

I agree with you Jan in that it it not always possible to tell the
DBAs to change things to accomodate NHibernate... really it has to be
the other way around unless you are fortunate enough in a brand new
development.

I think the behaviour I mentioned above would be a welcome addition to
NHibernate behaviour but I don't know the code base to be able to add
it in myself, and Fabio is the lead anyway so I presume NHibernate
goes in the direction he believes is best. I presume from the comments
above he doesn't think this is a big issue and I suspect he is quite
busy at the moment.

What you could try is writing a new stored procedure to do the delete
and the cascade for you, and hook NHibernate to that procedure. I
imagine most DBA's won't mind a new stored proc, as its something
within their control. Look on this website http://www.summerofnhibernate.com/
Chapter 10 and this one http://davybrion.com/blog/2008/11/populating-entities-from-stored-procedures-with-nhibernate/

Good luck !

[John Rayner]

Jan,

It seems to me from reading the NHib code (perhaps someone could
correct me if I'm wrong) that when a collection mapped via a one-to-
many is removed, the first action is to null out the foreign key
column. This immediately rules out the possibility of getting the SQL
you are wanting.

I think that we could generate a more efficient query when a one-to-
many collection is removed, i.e. process further cascades from child
objects and then issue a single query to delete all the children.
Does this sound possible?

Cheers,
John

On Nov 28, 10:32 pm, Jan Van Ryswyck <jan.van.rysw...@gmail.com>
wrote:

> > Fabio Maulo- Hide quoted text -
>
> - Show quoted text -

[Graham Bunce]

>the first action is to null out the foreign key column. This immediately rules out the possibility of getting the SQL
you are wanting.

As far as I know, not if you have inverse=true on the collection
mapping

[John Rayner]

Yes you are correct. I was playing with a few things and forgot to
mention that I had removed inverse="true" as part of trying to get the
requested functionality. If you have inverse="true" then the
operation will cascade via the collection but no SQL will be generated
as a result of modifications to the collection. Or at least, that's
my understanding of the meaning of inverse="true" :-)