What is an Active Record anyway?

While looking for Castle ActiveRecord examples, I found a blog by David Hayden - post 1 (design pattern overview), post 2 (some details on a possible implementation) and post 3 (using the Castle Project). David had a series of articles about Rocky Lhotka's .NET Business Objects (one for each chapter), which I have also read and tried to apply. Don't get me wrong, Rocky's CSLA library works, but as of .Net 1.1 it required a great deal of code on my part. There are reasons why I and many others were investigating code generation tools.

I have been studying Castle's ActiveRecord framework for a week and a half, and it is awesome for new development and clean data. During this discussion, remember that ActiveRecord mostly generates the XML mapping files for you and delegates the heavy lifting to NHibernate. If you have a legacy database with "sometimes there" keys, the framework will punish you severely. Below is an example of a legacy scenario that will result in many extra futile selects, attempting to get data that just isn't there.

CREATE TABLE Users (
   ID int identity(1,1) not null primary key,
   Username varchar(30) not null unique,
   Password varchar(30) not null,
   CreatedOn smalldatetime not null default getdate()
) ;

CREATE TABLE LogEntries (
   UName varchar(30) null,
   UEvent varchar(255) null
) ;

INSERT INTO Users(Username, Password) VALUES('admin' , '123') ;
INSERT INTO Users(Username, Password) VALUES('jsmith', '999') ;

INSERT INTO LogEntries (UName, UDate, UEvent)
   VALUES('jsmith', '2007-02-21 07:00', 'Woke up') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
   VALUES('tjones', '2007-02-21 07:30', 'Looked out kitchen window') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
   VALUES('jsmith', '2007-02-21 07:32', 'Ate breakfast') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
   VALUES('tjones', '2007-02-21 08:00', 'Drove to work') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
   VALUES('khaus' , '2007-02-21 12:00', 'Ate Lunch') ;

You may have noticed that the LogEntries table does not have a foreign key relation to Users. This is because the table was imported from a text file, and we can't guarantee that all users listed are in our database. For this scenario, NHibernate will do an "N+1" fetch if we try to link LogEntries to users. The HQL statement [SELECT le, us FROM LogEntry le LEFT JOIN le.user] will cause us problems no matter what we try. If we add a [BelongsTo] User property in the LogEntry class (NHibernate <many-to-one\> mapping), we find that the PropertyKey xml attribute is not mapped into BelongsTo. If we add the property and modify ActiveRecord's XmlGenerationVisitor.cs to add PropertyRef, we find that NHibernate's SessionImpl.cs and Loader.cs basically ignore the username link because it is not a primary key, and select the associated User once for each log entry. This happens whether the user was loaded in the initial join or not, and whether the user has been loaded into memory or not. I tried lying and telling ActiveRecord that UserName was the primary key. That helped, and I now only saw extra selects when the UserName was not found in the initial join. I patched NHibernate's SessionImpl.cs to remember failed fetches, and reduced the number of selects to 1 + count(unique missing usernames). I can not submit this patch to NHibernate, because it only works for the scenario of joining on a primary key - it breaks for the PropertyRef scenario. The only way that I found to fix this multi-select problem was to add a foreign key reference to LogEntries (it does not have to have a constraint). So LogEntries would look like:

CREATE TABLE LogEntries (
UName varchar(30) null,
UEvent varchar(255) null,
User_ID int null
 ) ;

Now we have to run an update every time we import a log file, but the subsequent processes will run with good performance (Any remaining performance issues can be solved with indexes, etc.). Do NOT add a foreign key constraint, or your imports will fail on bad data.

UPDATE LogEntries SET User_ID = u.ID
    FROM LogEntries le
    JOIN Users u ON u.UserName = le.UName
    WHERE User_ID is NULL ;

In the future, I will share my log4net file.