NHibernate Cross-database Hacks

Cross-Database Queries

Ok, so this is a bit of a hack, but it does work.  Thanks to this which set me down the "right" path.

I work with a few legacy systems that have central databases for common information, and individual databases for application specific information.  The data is queried using joins across the databases to retrieve the data required.
A separate service model was introduced for the common data, but when performing filtered queries across both data sets the service model was not efficient (this is a greater issue of context boundaries that I won’t go into here).  To perform the queries that were previously performed using stored procedures using cross-database joins in nHibernate required a bit of a cheat.

nHibernate mappings have a “schema” property as well as the more commonly used “table” property.  By manipulating this schema property you can convince nHibernate to perform cross-database queries.  Setting the schema to “{database}.{schema}” any join query to that element will effectively use the full cross-database query syntax when converted to a SQL query.

Neat (but ultimately not very satisfying because it is not a very nice design).

Bigger Hack, run away

If the target database name is not known until runtime, you can even hack it more to support this.

During the configuration of the nHibernate session factory, you can make a few modifications that will allow you to update the schema property of an entity.  This is useful if you have a different ‘other’ database name for each environment (e.g. OtherDatabase-dev, OtherDatabase-prd).

First we appropriately generate the fluent configuration, and build it.
We then iterate through the class mappings.  Each persistentClass.MappedClass is the underlying POCO model object of the entity.
We check if this is one that we want to override the schema property for (IOtherDatabaseEntity is a simple blank interface, it could be done via naming convention or whatever)
And then update the schema property on the mapping
Finally we create the session factory from the modified config

var fluentConfiguration = config.Mappings(m =>
        m.FluentMappings
        .AddFromAssemblyOf()
        );
var builtConfig = fluentConfiguration.BuildConfiguration();

foreach (PersistentClass persistentClass in builtConfig.ClassMappings)
{
        if (
            typeof(IOtherDatabaseEntity)
                .IsAssignableFrom(persistentClass.MappedClass)
            )
        {
            persistentClass.Table.Schema = cdrName;
        }
}
                  
ISessionFactory sessionFactory = builtConfig
        .BuildSessionFactory();

Hacks away!