Mapping entities to multiple databases with NHibernate

The legacy application I'm currently replacing features multiple databases for some insane reason. Luckily it's quite easy to get NHibernate to do joins across databases so long as they are on the same server. The technique is detailed by Hector Cruz in this thread on the NHibernate forum. The trick is to specify the schema you are addressing in each mapping file. Because the schema name simply becomes a table prefix, you can also use it to specify cross database joins. So long as you follow good practice and have one mapping file per entity, it means that, in theory, each entity could be persisted to a different database. I've put together a little project to show this working using Northwind. You can download the code here:

http://static.mikehadlow.com/Mike.NHibernate.Multiple.zip

I took a backup of Northwind and then restored it to a new database so that I had a Northwind and a Northwind2. I'm going to get the Product entity from the Products table on Northwind and the Supplier from the Suppliers table in Northwind2. The great thing is that you only need a single connection string pointing to one database (in my case the original Northwind).

Here's the NHibernate configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler,NHibernate" />
  </configSections>
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
      <property name="connection.connection_string">Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True</property>
      <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
      <property name="show_sql">true</property>
      <mapping assembly="Mike.NHibernate.Multiple"/>
    </session-factory>
  </hibernate-configuration>
</configuration>

Nothing special here. I've just nominated the original Northwind database to be my  initial catalogue. Next I've got two entities, Product and Supplier:

namespace Mike.NHibernate.Multiple
{
    public class Product : Entity
    {
        public virtual string ProductName { get; set; }
        public virtual Supplier Supplier { get; set; }
    }
}

namespace Mike.NHibernate.Multiple
{
    public class Supplier : Entity
    {
        public virtual string CompanyName { get; set; }
    }
}

Once again pure persistence ignorance. You don't have to do anything special with your entities. Now, here's the trick: The mapping file for the Product entity specifies the schema as Northwind.dbo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Northwind.dbo" >
  <class name="Mike.NHibernate.Multiple.Product, Mike.NHibernate.Multiple" table="Products">
    <id name="Id" column="ProductID" type="Int32">
      <generator class="identity" />
    </id>
    <property name="ProductName" />
    <many-to-one name="Supplier" class="Mike.NHibernate.Multiple.Supplier, Mike.NHibernate.Multiple" column="SupplierID" />
    
  </class>
</hibernate-mapping>

While the Supplier mapping file specifies Northwind2.dbo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Northwind2.dbo" >
  <class name="Mike.NHibernate.Multiple.Supplier, Mike.NHibernate.Multiple" table="Suppliers">
    <id name="Id" column="SupplierID" type="Int32">
      <generator class="identity" />
    </id>
    <property name="CompanyName" />
  </class>
</hibernate-mapping>

Now, hey-presto! When I run this little console program to retrieve a Product from NHibernate, I get an object graph back with Product entities from Northwind and Supplier entities from Northwind2.

using System;
using NHibernate.Cfg;
namespace Mike.NHibernate.Multiple
{
    class Program
    {
        static void Main()
        {
            var configuration = new Configuration();
            configuration.Configure();
            var sessionFactory = configuration.BuildSessionFactory();
            var session = sessionFactory.OpenSession();
            var product = session.Load<Product>(1);
            Console.WriteLine("Product: {0}, Supplier: {1}", product.ProductName, product.Supplier.CompanyName);
        }
    }
}

NHibernate generates this SQL:

 

NHibernate: 
SELECT 
 product0_.ProductID as ProductID0_0_, 
 product0_.ProductName as ProductN2_0_0_, 
 product0_.SupplierID as SupplierID0_0_ 
FROM Northwind.dbo.Products product0_ 
WHERE product0_.ProductID=@p0; @p0 = '1'
NHibernate: 
SELECT 
 supplier0_.SupplierID as SupplierID1_0_, 
 supplier0_.CompanyName as CompanyN2_1_0_ 
FROM Northwind2.dbo.Suppliers supplier0_ 
WHERE supplier0_.SupplierID=@p0; @p0 = '1'

As you can see the Product was retrieved from Northwind and the Supplier from Northwind2. It's similar to when you do cross database joins in a stored procedure. The stored procedure has to live in a particular database, but because each table gets prefixed with it's database name the DBMS simply looks up the table in the referenced database.

Note that this trick is simply to deal with a legacy situation that I can't do much about. You really don't want to architect a system like this from scratch.