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.

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.

Answer 2

Also, consider restoring your db from a backup, as this will ensure that you're always working from a known state (and will be much quicker than recreating the domain). It depends upon your test suite, but I do know that some of the test suits will automatically do this for you. Again, it depends upon how complex your domain is - for small domains this smells really bad. For large domains, it quickly becomes very attractive.

Answer 3

Yes but, can I create ISQLQuery, to bypass fetching?