Best way to delete all rows in a table using NHibernate?

Question

To keep my integration tests independent I remove all old data and insert new test data before each test. Is there a better way of doing this than simply querying for all entities and deleting them one by one?

I have considered writing a stored proc that runs "delete from tablename;" for each table that is to be cleared. That ought to quite a bit faster, but it would be nice to do it without doing SQL queries or calling SPs via NH.

I'm using vanilla NHibernate and Linq to NHibernate. I beleive Castle Active Record has something like Foo.DeleteAll(), but I don't want to use Active Record for this project.

Any ideas?

Thanks /Erik

UPDATE:

Since this question was asked and answered, progress has been made by the NHibernate team. As Ayende explains in this blog post, you can now execute DML queries directly, without NHibernate having to fetch any entities.

To delete all Foo objects you could do like this:

using (ISession session = ...)
using (ITransaction transaction = session.BeginTransaction())
{
    session.CreateQuery("delete Foo f").ExecuteUpdate();

    transaction.Commit();
}

This query would generate the following SQL:

delete from Foo

which aught to be significantly faster than fetching the entities first and then deleting them. Be careful though, since queries like these do not affect the level 1 cache.

Thorstein

If you are using SQL Server it is MUCH more efficient to execute TRUNCATE TABLE foo; as that is a minimally logged operation. Executing DELETE FROM foo; logs all the deletes for recovery purposes; on large tables this requires significant disk/time. – Antioch

TRUNCATE TABLE is almost never available when there are foreign keys involved. – Aminopyrine

Answer 1

In the TearDown of my UnitTests, I mostly do this:

using( ISession s = ... )
{
   s.Delete ("from Object o");
   s.Flush();
}

This should delete all entities. If you want to delete all instances of one specific entity, you can do this:

using( ISession s = .... )
{
    s.Delete ("from MyEntityName e");
    s.Flush();
}

Offcourse, there's a drawback with this method, and that is that NHibernate will first fetch the entities before deleting them.

Anthony

Oh, I had totally missed that you can pass a query to session.Delete(). Thanks! – Udder

BTW: It's deadly slow on large tables – Cosentino

INdeed, that's the drawback that I mentionned. – Anthony

This is equivalent to foreach(var o in s.Load("from Object o")) s.Delete(o); which is slooow. – Dimity

Answer 2

I use Fluent Nhibernate attributes so I modify code a little in order not to hardcore table names

private static void CleanUpTable<T>(ISessionFactory sessionFactory)
{
    var metadata = sessionFactory.GetClassMetadata(typeof(T)) as NHibernate.Persister.Entity.AbstractEntityPersister;
    string table = metadata.TableName;

    using (ISession session = sessionFactory.OpenSession())
    {
        using (var transaction = session.BeginTransaction())
        {
            string deleteAll = string.Format("DELETE FROM \"{0}\"", table);
            session.CreateSQLQuery(deleteAll).ExecuteUpdate();

            transaction.Commit();
        }
    }
}

usage

CleanUpTable<Person>(sessionFactory);

Wozniak

I've decided against editing this question because “hardcore” really sounds much better than “hardcode”, even though it's the latter that was probably meant. – Bechance

Answer 3

With NHibernate 5.0 you can now simply do:

session.Query<Foo>().Delete();

Documentation:

    //
    // Summary:
    //     Delete all entities selected by the specified query. The delete operation is
    //     performed in the database without reading the entities out of it.
    //
    // Parameters:
    //   source:
    //     The query matching the entities to delete.
    //
    // Type parameters:
    //   TSource:
    //     The type of the elements of source.
    //
    // Returns:
    //     The number of deleted entities.
    public static int Delete<TSource>(this IQueryable<TSource> source);

Cuirbouilli