Hi, I'm wondering if anyone has experience with deletions using ICriteria instances.

What I mean is, instead of doing something like:



do this:



This would be helpful for situations where the delete becomes complex and may have several subparts (so instead of using System.Text.StringBuilder to build the query, we can use ICriteria).

The problem is that I can't seem to figure out how the ToSqlString() works. Specifically, I can't figure out where I'm supposed to get an instance of ISessionFactoryImplementor from :?:

Are there other possible ways to do a delete using ICriteria that anyone has had succss with?

Delete doesn't use SQL (only HQL), so your approach won't work anyway. Delete actually first executes the query to load the objects and then deletes them one-by-one, so you might as well do it yourself - execute the query and then traverse the result list and delete each object.

:shock:

Seriously?!?

Basically, I'm building a simple web based log viewer to for log4net output where users can delete log entries. There may be hundreds-thousands of log entries each month that a user wants to delete (I've restricted the users to a month-by-month view to reduce loading times). So if I'm understanding this correctly, ISession.Delete(<string>) actually *loads* all of the data first and *then* issues the delete, even though I haven't specified the type to load?

Seems awfully inefficient...is there a batch delete option or some more efficient way of doing this with NHibernate?

No, NHibernate doesn't have a batch delete feature. The reason it's deleting objects one-by-one is it needs to handle cascades, interceptions and other such things. I believe Hibernate 3 did some work regarding batch deletes, but even if so, it will be quite a long time before this is ported to NHibernate.

So I think for now you can only use direct SQL to delete the entries...

I wanted to jump into the discussion and add a question then:

what is the most performant way to delete a collecition of objects? I found that when I delete and object the cascade gets fired one by one. If I load the object and do a IList.Clear() will make nhibernate realize it has to do a batch delete?

The most performant way is probably to use direct SQL. NHibernate does use batch deletes when deleting a persistent collection, but I don't understand what IList you refer to. If you mean a list of query results, then it's not a persistent collection, but a plain ArrayList, and NHibernate can't detect any changes to it.

No, I'm mean a entity collection.

What I'm seeing is that I have a kind of relationship akin to Order and OrderItem mapped with a Bag. When I issue a deleteById (with HQL) with Order and look at the log I see multiple deletes for each orderitem.

I have set cascade="all-delete-orphan". I'm surely doing something wrong, but don't know what.

Thanks!

No, I see the same thing too. I expected a:

DELETE FROM Child WHERE ParentID = @ParentID

But, it issues a delete for each child record, then for the parent.

I haven't looked at the code but I assume this is because NHibernate knows about objects and not data. It traverses the object graph to the lowest child in the hierarchy then deletes each item in any collections it contains going up the tree generating the delete for each object as it climbs up the tree.

However, what I don't understand is why it issues each command seperately rather than batching them in on command. Seems like alot of traffic could be avoided by sending all the generated deleted in a single command to SQL. Perhaps all back ends don't support this, I don't know. Seems like a db feature the dialect could enumerate.

BOb

I was having terrible performance on a delete operation so I decided to do a cascase delete with a trigger but now I am getting a weird error. When I try to delete the entity I get this error from NHibernate:

Duplicate identifier in table for RemoteNet.Northrop.IbisWork.Domain.Model.SubStep: 2172


Stack Trace:


[HibernateException: Duplicate identifier in table for RemoteNet.Northrop.IbisWork.Domain.Model.SubStep: 2172]
NHibernate.Persister.AbstractEntityPersister.Check(Int32 rows, Object id)
NHibernate.Persister.EntityPersister.Delete(Object id, Object version, Object obj, ISessionImplementor session)
NHibernate.Impl.ScheduledDeletion.Execute()


Any clues as to why this is happening and what table it is referring to. The sql statements that get executed all look good.