NHibernate HiLo Identity Generator on SQL Azure

NHibernate POID Generators

NHibernate supports several POID (Persistent Object ID) Generators, including: Identity, Guid, Guid.Comb, and HiLo.

You can find a good explanation here, about the various strategies.

Avoiding Identity and going with Guid.Comb or HiLo is a good practice, but there's a slight problem when your Database is SQL Azure...

HiLo 

HiLo, similar to Guid.Comb, generates the Ids disconnect from the Database, but in contrast it takes less space, and is human readable.

You might find this article very helpful.

Defining an Entity to work with HiLo using Fluent NHibernate might look something like this:

public class ProdcutMap: ClassMap<Prodcut>
{
   public ProdcutMap()
   {           
      Id(x => x.Id).GeneratedBy.HiLo(100);
      ...
   }
}

And if you prefer the conventions way, the previous example would translate to something like:

public class HiLoIdConvention: IIdConvention
{               
   public void Apply(IIdentityInstance instance)
   {
      Id(x => x.Id).GeneratedBy.HiLo(100);
   }
}

Problem

Out of the box, the NHibernate HiLo Identity Generator will not work with SQL Azure, as SQL Azure will insist on having a Clustered Index on the table, something that the default implementation doesn't take into account.

As soon as the schema will be generated on the Database, and it will hit the line where it's inserting the default value, you'll get an exception.

Solution

I think the preferred solution would be to use an SQL Azure Dialect, but since it's not built into NHibernate, as long as I know, the second best approach would be to create a custom Identity generator.

Ended up with a Custom Generator:

class AzureCompliantTableHiLoGenerator: TableHiLoGenerator
{           
    public override string[] SqlCreateStrings(NHibernate.Dialect.Dialect dialect)
    {            
        IList<string> script = new List<string>();

        script.Add(string.Format("create table {0} ( {1} {2}, {3} varchar(128) not null )", HiLoIdConvention.NHibernateHiLoIdentityTableName, 
                                                                                            HiLoIdConvention.NextHiValueColumnName, 
                                                                                            dialect.GetTypeName(this.columnSqlType),
                                                                                            HiLoIdConvention.TableColumnName));

        script.Add(string.Format("create clustered index IX_{0}_{1} on {0} ( {1} asc );", HiLoIdConvention.NHibernateHiLoIdentityTableName,
                                                                                            HiLoIdConvention.TableColumnName));
                        
        script.Add("go");            

        return script.ToArray();            
    }

    public override string[] SqlDropString(NHibernate.Dialect.Dialect dialect)
    {
        return new string[] { dialect.GetDropTableString(HiLoIdConvention.NHibernateHiLoIdentityTableName) };
    }
}

An Id Convention:

public class HiLoIdConvention: IIdConvention
{      
    private const string TableParamName = "table";
    private const string ColumnParamName = "column";
    private const string MaxLoParamName = "max_lo";
    private const string WhereParamName = "where";
        
    public const string NHibernateHiLoIdentityTableName = "NHibernateHiLoIdentity";
    public const string NextHiValueColumnName = "NextHiValue";
    public const string TableColumnName = "Entity";
    public const int MaxLo = 10;
 
    public void Apply(IIdentityInstance instance)
    {                   
        instance.GeneratedBy.Custom(typeof(AzureCompliantTableHiLoGenerator),
                                    builder => builder.AddParam(TableParamName, NHibernateHiLoIdentityTableName)
                                                        .AddParam(ColumnParamName, NextHiValueColumnName)
                                                        .AddParam(MaxLoParamName, MaxLo.ToString())
                                                        .AddParam(WhereParamName, string.Format("{0} = '[{1}]'", TableColumnName, instance.EntityType.Name)));             
    }

    public static void CreateHiLoScript(NHibernate.Cfg.Configuration config)
    {
        var script = new StringBuilder();

        script.AppendFormat("delete from {0};", NHibernateHiLoIdentityTableName);

        script.AppendLine();

        script.AppendLine("go");

        script.AppendLine();

        foreach (var tableName in config.ClassMappings.Select(m => m.Table.Name).Distinct())
        {
            script.AppendFormat(string.Format("insert into [{0}] ({1}, {2}) values ('[{3}]',1);", 
                                                NHibernateHiLoIdentityTableName, 
                                                TableColumnName, 
                                                NextHiValueColumnName, 
                                                tableName));

            script.AppendLine();
        }

        config.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(script.ToString(), 
                                                                            null, 
                                                                            new HashedSet<string> 
                                                                            { 
                                                                                typeof(MsSql2000Dialect).FullName, 
                                                                                typeof(MsSql2005Dialect).FullName, 
                                                                                typeof(MsSql2008Dialect).FullName 
                                                                            }));
    }
}

And my Bootstrapper code, removed most of it for brevity, looks something like this:

public static class Bootstrapper
{    
    private const string ConnectionStringName = "MyDB";
    
    public static void RegisterTypes(IUnityContainer container)
    {
        CreateAndRegisterSessionFactory(container);
        ...
    }
    
    private static void CreateAndRegisterSessionFactory(IUnityContainer container)
    {
        NHibernate.Cfg.Configuration configuration = Fluently.Configure()                
                                                                .Database(DefineDatabase)
                                                                .Mappings(DefineMappings)
                                                                .ExposeConfiguration(HiLoIdConvention.CreateHiLoScript)                                                                    
                                                                .BuildConfiguration();

        CreateSchema(configuration);

        ISessionFactory sessionFactory = configuration.BuildSessionFactory();

        container.RegisterInstance<ISessionFactory>(sessionFactory);
    }
   
    private static IPersistenceConfigurer DefineDatabase()
    {                
        return MsSqlConfiguration.MsSql2008
                                    .ConnectionString(x => x.FromConnectionStringWithKey(ConnectionStringName));                                                                 
    }

    private static void DefineMappings(MappingConfiguration mapping)
    {
        mapping.FluentMappings                   
                .AddFromAssemblyOf<ProductMap>()
                .Conventions.Add<HiLoIdConvention>();
    }
    
    private static void CreateSchema(NHibernate.Cfg.Configuration configuration)
    {
        SchemaExport schemaExport = new SchemaExport(configuration);          

        schemaExport.Drop(false, true);
        schemaExport.Create(true, true);                        
    }
}

It works fine, no complaints.... But still, would be glad find out that NHibernate supports an Azure Dialect, or that there is already some Library that I can use instead...