ActiveRecord and Bulk Deletes

I came across an issue the other day that I thought would be fairly straightforward: deleting a bunch of records from the database based on certain criteria. I've done things like this plenty of times before in Java, but this was my first time doing it in .NET, using NHibernate and ActiveRecord.

The Problem

Let's pretend I want to delete all of a user's settings when they choose to import a new batch of settings (rather than deal with merging the two sets). In HQL I would say something like:

delete from UserSettings where User = :user

and then to actually run it, I'd replace the named parameter with an instance of the logged in user.

I wanted to use this same kind of HQL in my .NET application, and that's where I ran into some issues. There is almost no good documentation on the ActiveRecord project. The API documents are incomplete and the user guide is only good for the very basics. The one thing that the documentation does recommend, using the SimpleQuery or ScalarQuery objects, forgets to mention one key piece of advice: THEY DON'T WORK WITH DML-STYLE STATEMENTS. Seems like an important thing to mention.

Searching the internet for a solution wasn't much better. Most examples show some sort of retrieval happening before the DELETE, either retrieving the entire domain object, or retrieving just the PK's with a projection and passing those into a DeleteAll method. But I didn't want to have to retrieve things from the database just to delete them, so I came up with this approach instead.

The Solution

The first thing to do is extend the Castle.ActiveRecord.ActiveRecordBaseQuery, like so:

class DeleteUserSettingsQuery : ActiveRecordBaseQuery
{
    private User user = null;
        
    public DeleteUserSettingsQuery(User user) : base(typeof(UserSetting))
    {
        this.user = user;
    }

    protected override IQuery CreateQuery(ISession session)
    {
        IQuery query = session.CreateQuery("delete from UserSetting where User = :user");
        query.SetParameter("user", this.user);

        return query;
    }

    protected override object InternalExecute(ISession session)
    {
        return CreateQuery(session).ExecuteUpdate();
    }
}

The trick here is to override the CreateQuery method AND override the InternalExecute method. If you don't override InternalExecute, it will use the default implementation of this method which calls List() which does not support DML-style operations.

Using it is as simple as:

UserSetting.ExecuteQuery(new DeleteUserUserSettingsQuery(user));

The ExecuteQuery method is available because UserSetting is a domain object that extends ActiveRecordBase.

That's it. Easy. Hopefully this solution saves someone else some time and frustration.