Monday, 6 February 2012

Fluent NHibernate Solution to enable SchemaExport to create HiLo columns

I spent a few hours recently trying to figure out why Fluent NHibernate (FNH) wasn’t doing what I thought it should be doing when using the HiLo generator conventions and then using the SchemaExport tool to generate my schema. After eventually looking at the hbm files that FNH was producing for a possible error and to make sure that I was using FNH correctly, I realised that FNH wasn't the problem, and that the problem lies within SchemaExport.
Fabio Maulo puts this down to design and gives a solution for using ConfORM. I agree with the comments made and find his answer very unsatisfactory and that there is a bug and that it should be fixed.
Whilst I am sure that that debate will roll on, I have created a solution that matches Fabio’s one for ConfORM but for FNH shown below:
using System.Linq;
using System.Text;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;
using Iesi.Collections.Generic;
using NHibernate.Dialect;
using NHibernate.Mapping;

namespace Conventions
    public class CustomIdentityHiLoGeneratorConvention :IIdConvention
        public const string NextHiValueColumnName= "NextHiValue";
        public const string NHibernateHiLoIdentityTableName = "NHibernateHiLoIdentity";
        public const string TableColumnName = "Entity";

        #region Implementation of IConvention<IIdentityInspector,IIdentityInstance>

        public void Apply(IIdentityInstance instance)
            instance.GeneratedBy.HiLo(NHibernateHiLoIdentityTableName, NextHiValueColumnName, "500", builder => builder.AddParam("where", string.Format("{0} = '[{1}]'", TableColumnName, instance.EntityType.Name)));

        public static void CreateHighLowScript(NHibernate.Cfg.Configuration config)
            var script = new StringBuilder();
            script.AppendFormat("DELETE FROM {0};", NHibernateHiLoIdentityTableName);
            script.AppendFormat("ALTER TABLE {0} ADD {1} VARCHAR(128) NOT NULL;", NHibernateHiLoIdentityTableName, TableColumnName);
            script.AppendFormat("CREATE NONCLUSTERED INDEX IX_{0}_{1} ON {0} (Entity ASC);", NHibernateHiLoIdentityTableName, TableColumnName);
            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));
            config.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(script.ToString(), null, new HashedSet<string> { typeof(MsSql2000Dialect).FullName, typeof(MsSql2005Dialect).FullName, typeof(MsSql2008Dialect).FullName }));
This is then used as follows:

Submit this story to DotNetKicks Shout it


  1. Hi, nice soulition, but how do i set low value acording to what i've mapped with fluentnhibernate to each class. THANKS

    1. Hi Matias,

      I am not sure if you will get two answers here as I already posted one answer, but it doesn't appear to be showing up yet...
      Anyway here it is again:
      On line 39 at the end of the script there is a hard coded 1. This is the low value. You can replace this with any number that you wish. If its an existing database you could use some kind of formula to work out what it needs to be based on existing entities and their Ids or set to 2 say to give yourself some hard coded day 0 entities if that is your rational.
      Hope this helps

  2. Just curious how you made AddAuxiliaryDatabaseObject work, FluentNHib still doesn't support database-object feature today.