Determining FluentNH Schema Mappings based on Entity Namespaces

I’m setting up some Fluent NHibernate mappings for a rewrite of some of our legacy code, and one of the issues I’ve hit is that we make extensive use of cross-database views and joins – the data supporting our app is split across three separate SQL Server databases (which, thankfully, are all hosted by the same SQL Server instance).

Turns out this is pretty easy to do – Mike Hadlow has a great post here which covers the fundamentals.

I’ve extended this principal a bit, using the Conventions facility provided by Fluent NHibernate, so that you can determine the SQL database for each entity based on your entities’ namespaces, so I have a model that looks (very) roughly like this. Let's imagine that my core web data is in a database called WebStuff, my accounts system is in CashDesk and my CRM database is in DylanCrm. Each mapped entity is declared in a sub-namespace of my common Dylan.Entities namespace, with these sub-namespaces named to reflect the database they’re mapping:

namespace Dylan.Entities.WebStuff {
	public class WebUser {
		public int Id { get; set; }
		public Customer AssociatedCustomer { get; set; }
	}
}

namespace Dylan.Entities.CashDesk {
	public class Invoice {
		public int Id { get; set; }
		public Customer Customer { get; set; }
	}
}

namespace Dylan.Entities.DylanCrm {
	public class Customer {
		public int Id { get; set; }
		public IList Invoices { get; set; }
	}
}

NHibernate will quite happily retrieve and update data across multiple databases, by prepending the schema name to the table names - so you end up running SQL statements like SELECT ... FROM CashDesk.dbo.Invoice WHERE .... If you're only mapping a handful of tables, it's easy to specify the schema for each table/object as in Mike's example - but you can also use FluentNHibernate.Conventions to achieve the same thing.

First off, you'll need to add a new class which implements IClassConvention and modifies the Schema property of each class mapping:

public class SchemaPrefixConvention : IClassConvention {

	private string ExtractDatabaseName(string entityNamespace) {
		return (entityNamespace.Substring(entityNamespace.LastIndexOf('.') + 1));
	}

	public void Apply(IClassInstance instance) {
		instance.Schema(ExtractDatabaseName(instance.EntityType.Namespace) + ".dbo");
	}
}

Once you've done that, you just need to reference this convention when you set up your mappings; if you're using the auto-mapping facility, it looks like this:

mappings.AutoMappings.Add(
	AutoMap
		.AssemblyOf<Invoice>()
		.Where(t => t.Namespace == "Dylan.Entities.CashDesk")
		.Conventions.Add<SchemaPrefixConvention>()
	);

mappings.AutoMappings.Add(
	AutoMap
		.AssemblyOf<Customer>()
		.Where(t => t.Namespace == "Dylan.Entities.DylanCrm")
		.Conventions.Add<SchemaPrefixConvention>()
	);

mappings.AutoMappings.Add(
	AutoMap
		.AssemblyOf<WebUser>()
		.Where(t => t.Namespace == "Dylan.Entities.WebStuff")
		.Conventions.Add<SchemaPrefixConvention>()
	);

Fluent NH will run your Apply() method to each mapped class in each of these three mappings, which means the resulting configuration will qualify each table name with a schema name derived from the mapped class’ namespace – and once that’s in place, you can query, retrieve, update, join and generally hack your objects about at will, and completely ignore the fact that under the hood they're actually being persisted across multiple data stores.

I think that's quite neat.