How to efficiently delete all but the latest x children of parent entity using NHibernate?

Question

Hi,

Please forgive me if I don't explain this very well. First off, I am using NHibernate 2.0 with .NET 3.5. Basically I have an entity "EntityA" (for simplicity) with one or more children of type EntityB. Each EntityB has a number indicating how recently it was created. I would like to delete all but the x most recent EntityB. This forms part of a purge operation.

I am struggling to see an efficient way of doing this, The problem is that the EntityB instances are actually quite complex and could have hundreds of child objects themselves. The list of EntityB on EntityA is lazily loaded and I would ideally like to avoid loading it in memory if possible.

I tried passing an HQL query to Session.Delete. Unfortunately HQL doesn't seem to support the top statement so I cannot do a subselect to choose which ones not to delete.

The cascades are set up in NHibernate and not in the database. I'm not sure but I wonder if NHibernate will load the whole object graph even if the delete is done via HQL.

Any advice would be appreciated.

[Edit]Unfortunately any query must be HQL not SQL since it needs to be database independent[/Edit]

Cheers,

James

Answer 1

From my experience with nHibernate I don't think you're going to find a clean solution for this. But, it doesn't matter. Stepping outside the framework is only a bad thing if the framework offers a viable alternative. Use a parameterized SQL statement, make sure it's clear in the code where and why you're doing this and it'll be a great solution.

EDIT:

I'm fairly certain you could come up with a database independent SQL query for this but anyway... Instead of trying to use a top statement try using MAX() (assuming there is such a function in HQL) to grab the top item id and then structure a delete statement with a conditional that the id is not the MAX id.

Answer 2

If nothing else you could set up the cascades in the database and just do

Session.CreateSQLQuery([SqlStatement]).SetParameter([ParameterStuff]).ExecuteUpdate();

I always try to keep cascades, default values, etc set up in both the database and nhibernate for cases like this when you need to do something directly to the database.

Answer 3

You should be able to use HQL delete Child c where c in (:list) where list is a copy of the list of children with only the to-be-removed elements included. list may be obtained through an HQL query such as from Child c where c.Parent = :parent - HQL queries apparently do not obey the mapping's fetch strategy (lazy vs eager) and will only fetch children eagerly when instructed to fetch children eagerly (so just don't put in the left join fetch c.SubChildren) - and then filtered to include only the elments to be removed.