Where’s Lou

It’s been said that ninety percent of the time you think there is a one-to-one relationship in NHibernate it’s really a many-to-one. I’ll agree with that. But this post is about one of the ten-percent cases when a single entity spans several table schemas.

So the scenario is one where you have a thing that can be extended with additional data. And capabilities, of course, but this post is just about the data. In a traditional app it’s very straightforward to add additional fields when a new feature is added.

Consider a thing with foo and bar fields, before:

And adding the thing with frap and quad fields, after:

This might not be the best way to go in a dynamic system. If the feature was strictly optional some users wouldn’t choose to activate it at all, so adding columns to a core table would be bad. Maybe not all of the things in the system would use the frap-quad fields, so having a very large number of (null) fields could be nice to avoid. In those cases a vertical data partition could be a nice approach.

In fact let’s go the extra step and make a core Thing record, with a ThingType, and move the foo and bar fields onto their own vertical record partition.

Now that’s starting to look a bit crazy, but that’s really the split-up view of a single logical entity. The goal behind breaking them apart is to have Frappable and FooBar aspects mutually ignorant of each other. In the end the logical view of entity #6 is still just the following:

Now let’s wire that together with some data NHibernate classes and Fluent NHibernate.

public class ThingType {
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

public class Thing {
    public virtual int Id { get; set; }
    public virtual ThingType ThingType { get; set; }
}

public class FooBar {
    public virtual int Id { get; set; }
    public virtual Thing Thing { get; set; }
    public virtual string Foo { get; set; }
    public virtual string Bar { get; set; }
}

public class Frappable {
    public virtual int Id { get; set; }
    public virtual Thing Thing { get; set; }
    public virtual string Frap { get; set; }
    public virtual string Quad { get; set; }
}

As a quick sample let’s use a console app to create and populate a database.

static void Main(string[] args) {
    var database = SQLiteConfiguration.Standard.UsingFile("DataSample.db").ShowSql();
    var model = AutoMap.Assembly(typeof(Thing).Assembly);

    var sessionFactory = Fluently.Configure()
        .Database(database)
        .Mappings(mappings => mappings.AutoMappings.Add(model))
        .ExposeConfiguration(config => new SchemaExport(config).Execute(false, true, false))
        .BuildSessionFactory();

    Console.WriteLine("Adding records");
    var session = sessionFactory.OpenSession();
    var fooType = new ThingType { Name = "foo" };
    var fooPlusType = new ThingType { Name = "fooplus" };

    var thing = new Thing { ThingType = fooPlusType };
    var fooBar = new FooBar { Thing = thing, Foo = "brillig", Bar = "slithe" };
    var frappable = new Frappable { Thing = thing, Frap = "did", Quad = "and" };

    session.Save(fooType);
    session.Save(fooPlusType);
    session.Save(thing);
    session.Save(fooBar);
    session.Save(frappable);
    session.Close();

    Console.WriteLine("Fetching thing #{0}", thing.Id);
    session = sessionFactory.OpenSession();
    var fetch = session.Get<Thing>(thing.Id);
    Console.WriteLine(fetch.ThingType.Name);
    session.Close();
}

And the console output is this

Adding records
NHibernate: INSERT INTO "ThingType" (Name) VALUES (@p0); select last_insert_rowid();@p0 = 'foo'
NHibernate: INSERT INTO "ThingType" (Name) VALUES (@p0); select last_insert_rowid();@p0 = 'fooplus'
NHibernate: INSERT INTO "Thing" (ThingType_id) VALUES (@p0); select last_insert_rowid();@p0 = 2
NHibernate: INSERT INTO "FooBar" (Foo, Bar, Thing_id) VALUES (@p0, @p1, @p2); select last_insert_rowid();@p0 = 'brillig', @p1 = 'slithe', @p2 = 1
NHibernate: INSERT INTO "Frappable" (Frap, Quad, Thing_id) VALUES (@p0, @p1, @p2); select last_insert_rowid();@p0 = 'did', @p1 = 'and', @p2 = 1
Fetching thing #1
NHibernate: SELECT thing0_.Id as Id1_0_, thing0_.ThingType_id as ThingType2_1_0_ FROM "Thing" thing0_ WHERE thing0_.Id=@p0;@p0 = 1
NHibernate: SELECT thingtype0_.Id as Id0_0_, thingtype0_.Name as Name0_0_ FROM "ThingType" thingtype0_ WHERE thingtype0_.Id=@p0;@p0 = 2
fooplus
Press any key to continue . . .

As expected so far it’s not really a one-to-zero-or-one relationship. The FooBar.Id is an identity column generated by the table instead of copied from Thing.Id, and there really shouldn’t be a FooBar.Thing_id column at all. Let’s update that with an automapping alteration…

class Alteration : IAutoMappingAlteration {
    public void Alter(AutoPersistenceModel model) {
        model.Override<FooBar>(map => {
            map.Id(x => x.Id).GeneratedBy.Foreign("Thing");
            map.HasOne(x => x.Thing).Constrained();
        });
        model.Override<Frappable>(map => {
            map.Id(x => x.Id).GeneratedBy.Foreign("Thing");
            map.HasOne(x => x.Thing).Constrained();
        });
    }
}
// and in the main() method
    var model = AutoMap.Assembly(typeof(Program).Assembly)
        .Alterations(alt => alt.Add(new Alteration()));

And the console output is now:

Adding records
NHibernate: INSERT INTO "ThingType" (Name) VALUES (@p0); select last_insert_rowid();@p0 = 'foo'
NHibernate: INSERT INTO "ThingType" (Name) VALUES (@p0); select last_insert_rowid();@p0 = 'fooplus'
NHibernate: INSERT INTO "Thing" (ThingType_id) VALUES (@p0); select last_insert_rowid();@p0 = 2
NHibernate: INSERT INTO "FooBar" (Foo, Bar, Id) VALUES (@p0, @p1, @p2);@p0 = 'brillig', @p1 = 'slithe', @p2 = 1
NHibernate: INSERT INTO "Frappable" (Frap, Quad, Id) VALUES (@p0, @p1, @p2);@p0 = 'did', @p1 = 'and', @p2 = 1
Fetching thing #1
NHibernate: SELECT thing0_.Id as Id1_0_, thing0_.ThingType_id as ThingType2_1_0_ FROM "Thing" thing0_ WHERE thing0_.Id=@p0;@p0 = 1
NHibernate: SELECT thingtype0_.Id as Id0_0_, thingtype0_.Name as Name0_0_ FROM "ThingType" thingtype0_ WHERE thingtype0_.Id=@p0;@p0 = 2
fooplus
Press any key to continue . . .

That’s closer to what we’re looking for. Note the FooBar.Id is an inserted field now where it’s value comes from the Thing property, and the Thing_id column is gone from the schema.

The Id/Thing pair and override become cut-and-paste noise pretty quickly though, so we can factor those into an abstract base class and even make the HasOne alteration work on all data classes descended from it. Like this:

public abstract class ThingPart {
    public virtual int Id { get; set; }
    public virtual Thing Thing { get; set; }
}

class Alteration : IAutoMappingAlteration {
    public void Alter(AutoPersistenceModel model) {
        model.OverrideAll(map => {
            var recordType = map.GetType().GetGenericArguments().Single();
            if (recordType.IsAssignableFrom(typeof(ThingPart))) {
                var changeType = typeof(Change<>).MakeGenericType(recordType);
                var change = (IChange)Activator.CreateInstance(changeType);
                change.Go(map);
            }
        });
    }

    interface IChange {
        void Go(object mapObject);
    }

    class Change<TRecord> : IChange where TRecord : ThingPart {
        void IChange.Go(object mapObject) {
            var map = (AutoMapping<TRecord>)mapObject;
            map.Id(x => x.Id).GeneratedBy.Foreign("Thing");
            map.HasOne(x => x.Thing).Constrained();
        }
    }
}

There! In the end the following is all you need for an extension to add a new handful of fields onto thing.

public class FooBar : ThingPart {
    public virtual string Foo { get; set; }
    public virtual string Bar { get; set; }
}

public class Frappable : ThingPart {
    public virtual string Frap { get; set; }
    public virtual string Quad { get; set; }
}

There are some more tricks you need to query across several parts at once, but that’s probably another blog post on it’s own.