NHibernate - using tenant specific table prefixes

We are developing a multi-tenant application using NHibernate where all tenants share the same database.

One option we considered was to use a tenant specific prefix for our database objects (I believe this the same approach taken by Orchard).

This would at least give us some kind of recovery model for tenant data and would mean we wouldn't have to include a tenantid filter on most of our queries.

So my question - has anyone employed this strategy? If so, how did you go about it.

Specifically, can we use the same SessionFactory for all tenants and can we use NHibernate to generate a new set of tables for a "new" tenant at runtime (is it safe to do so).

Thanks Ben

[Update]

This was a worthwhile investigation but ultimately we decided that a shared schema was more sui开发者_开发知识库table for our needs. Schema per tenant clearly offers better separation of tenant data but makes maintenance more difficult. Since our tenants are only storing small amounts of data, the thought of having 10 tables * 1K tenants is a little off-putting.

---

There are a couple of points of customization / extension that you may want to consider.

I don't think that you will be able to share the same session factory across tenants. I think that the simplest thing to do may be to update the mappings based on the tenant associated with the session factory.

public class EntitytMap:ClassMap<Entity>
{
     public EntitytMap()
    {
        Table("TableName");
        Schema(Session.TenantName);
        Id(p => p.Id, "Id").GeneratedBy.Identity();

If you want to have each tenant in their own schema, this should work. If you want to keep the schema the same but have a prefix on the table, you could change to:

public class EntityMap:ClassMap<Entity>
{
     public EntityMap()
    {
        Table(Session.TenantPrefix + "TableName");
        Schema("SCHEMA");
        Id(p => p.Id, "Id").GeneratedBy.Identity();

You can also try providing your own ConnectionProvider. Derive a class from NHibernate.Connection.DriverConnectionProvider and reference your own copy in the nhibernate configuration file instead of:

 <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

use

<property name="connection.provider">My.Project.DAL.NHibernate.CustomConnectionProvider, My.Project.DAL</property>

When the GetConnection provider is called, you can specify your own connection string based on the tenent.

---

can we use NHibernate to generate a new set of tables for a "new" tenant at runtime (is it safe to do so)

I'd suggest that you wouldn't want to grant your web application the level of permissions required to perform these DDL tasks. I'd leave the web app with the minimum level of permissions for normal DML operations and have a background service operating as a 'Provisioning Service'. Its role would be schema modifications for the new tenant, and it is also a good place to put any other tenant provisioning tasks such as creating new folders, configuring IIS etc. All these tasks take time too and are best not done in a single web request. The background database can update a provisioning table, updating it with information about its progress until its complete and the web ui moves to the next step.