NHibernate – Custom Id-generator

Recently I had a case where I needed to switch from the Identity generator to the HiLo generator in an existing project with existing data. Why? Well for one thing it gives better performance since identities brakes the ability to use batch inserts, since the generated Id needs to be returned (selected) after each insert.

I didn’t want to go through and configure params for each entity, hence I created a custom implementation of an Id-generator and added some conventionbased configuration in that class instead.

How to setup HiLo the normal way

The xml below is taken from 5.1.4.2 – Hi/Lo Algorithm – (http://www.nhforge.org/doc/nh/en/index.html#mapping-declaration-id) You can of course skip the params and go with default values.

<id name="Id">
	<generator class="hilo">
			<param name="table">hi_value</param>
			<param name="column">next_value</param>
			<param name="max_lo">100</param>
	</generator>
</id>

This will let every entity share the same table (that’s ok) and the same column for the next hi-value. For me, this wasn’t something I wanted. I wanted one specific value per entity. I also wanted one row per entity so that I could keep away from unnecessary update locks.

I wanted the following table:

create table dbo.NHibHiLoIdentities
(
	EntityName varchar(128) not null primary key,
	NextHiValue bigint not null
)

Custom generator

To get the behavior I wanted I extended the existing TableHiLoGenerator.

public class NHibIdGenerator : TableHiLoGenerator
{
    public override void Configure(IType type, IDictionary<string, string> parms, Dialect dialect)
    {
        if (!parms.ContainsKey("table"))
            parms.Add("table", "NHibHiLoIdentities");

        if (!parms.ContainsKey("column"))
            parms.Add("column", "NextHiValue");

        if (!parms.ContainsKey("max_lo"))
            parms.Add("max_lo", "100");

        if (!parms.ContainsKey("where"))
            parms.Add("where", string.Format("EntityName='{0}'", parms["target_table"]));

        base.Configure(type, parms, dialect);
    }
}

This way, if I want, I can specify the params in the xml-mapping, but if I don’t they will be injected in the Configure override. The trick is the where criteria. This tells NHibernate that I want to have a where criteria against the column “EntityName” generated when the next Hi-value is going to be checked out. I use the name of the entity which is allready populated in the dictionary.

Determine the new value

Since there allready was data in the tables for each entity I just looked at the existing Max(Id) for each and inserted a row in the NHibHiLoIdentities with concerns taken to the value of max_lo above.

insert into dbo.NHibHiLoIdentities (EntityName, NextHiValue) values
(
	'Customer',
	coalesce((select max(Id)/100 from dbo.Customer) + 1, 1)
)

Update your mappings

You will also have to update the mappings with information to make use of your Id-generator. It’s not hard. Just specifiy the fullname of your class for the generator element.

<id name="Id">
  <generator class="NHibAdventures.Storage.NHibIdGenerator, NHibAdventures.Storage" />
</id>

That’s it. Happy tweaking.

//Daniel

Ps! If you need a easy get going sandbox environment, checkout https://github.com/danielwertheim/NHibernate-Adventures

About these ads

9 thoughts on “NHibernate – Custom Id-generator

  1. Pingback: DotNetShoutout

  2. Pingback: NHibernate – Custom Id-generator « Daniel... | .NET and NHibernate | Syngu

  3. Does this solution generate the extra EntityName column when you run a schemaexport or does this depend on creating the table manually?
    Thanks
    Jason

  4. ive adapted your code to java and hilo reads from the table i tell , but it doesnt care about the entityname. whatever i write as the entry name to the database , it always uses the first row’s NextHiValue column.

    have any idea? i

    • I think you should look at this link

      http://www.philliphaydon.com/2010/10/using-hilo-with-fluentnhibernate/

      However there is a slight change in the code in case you adapt hilo strategy from recent Nhibernate trunck . The method signatures have changed . So instead of

      Id(x => x.Id).Column(“ProductId”)
      .GeneratedBy
      .HiLo(“NH_HiLo”, “NextHi”, “1000″, “TableKey = ”Product””);

      you need to use

      Id(x => x.Id).Column(“ProductId”).GeneratedBy.HiLo(“NH_HiLo”, “NextHi”, “10″, x => x.AddParam(“where”, “TableKey=’” + instance.EntityType.Name + “‘”));

  5. Pingback: hibernate HiLo – one table for all entities | PHP Developer Resource

  6. ///
    /// Custom TableHiLo Generator with additional columns based on Entity name (domain/model)
    ///
    public class HiLoIdGenerator : NHibernate.Id.TableHiLoGenerator
    {
    private string tableName;
    private string columnName;
    private string targetTable;

    private const string KeyColumnName = “DomainName”;

    /// Default table name
    public const string newDefaultTableName = “NH_HiloIdGenerator”;

    static HashSet tables = new HashSet();
    private static List creatingColumns = new List();
    private static List insertedColumns = new List();

    private struct ColumnInfo
    {
    public ColumnInfo(NHibernate.SqlTypes.SqlType type, string name)
    : this()
    {
    ColumnType = type;
    Name = name;
    IsNotNull = string.Empty;
    }

    public ColumnInfo(NHibernate.SqlTypes.SqlType type, string name, bool isNotNull)
    : this(type, name)
    {
    ColumnType = type;
    Name = name;

    if (isNotNull)
    IsNotNull = ” not null “;
    }

    public string Name;
    public NHibernate.SqlTypes.SqlType ColumnType;
    public string IsNotNull { get; private set; }
    }

    public override void Configure(IType type, IDictionary parms, Dialect dialect)
    {
    tableName = PropertiesHelper.GetString(TableParamName, parms, newDefaultTableName);
    columnName = PropertiesHelper.GetString(ColumnParamName, parms, DefaultColumnName);
    targetTable = string.Format(“‘{0}’”, parms["target_table"]);

    if (!insertedColumns.Contains(targetTable))
    insertedColumns.Add(targetTable);

    if (!parms.ContainsKey(TableParamName))
    parms.Add(TableParamName, tableName);
    else
    parms[TableParamName] = tableName;

    string whereTemplate = string.Format(“{0}={1}”, KeyColumnName, targetTable);
    if (!parms.ContainsKey(Where))
    parms.Add(Where, whereTemplate);
    else
    parms[Where] = string.Format(“{0} and {1}”, whereTemplate, parms[Where]);

    base.Configure(type, parms, dialect);
    }

    public override string[] SqlCreateStrings(Dialect dialect)
    {
    string sqlCreatingTbl = SqlCreatingTable(dialect);
    string[] sqlInsertingTbl = SqlInsertingTable();

    var sqlCreations = new List();
    sqlCreations.Add(sqlCreatingTbl);
    sqlCreations.AddRange(sqlInsertingTbl);

    return sqlCreations.ToArray();
    }

    private string SqlCreatingTable(Dialect dialect)
    {
    creatingColumns.AddRange(new ColumnInfo[]
    {
    new ColumnInfo(NHibernate.SqlTypes.SqlTypeFactory.GetString(150),
    KeyColumnName, true),
    new ColumnInfo(columnSqlType, columnName)
    });

    string sqlCreateTemplate = string.Format(“create table {0} ({1})”, tableName, “{0}”);
    var sqlCreateTbl = new StringBuilder();

    var creatingColumn = new StringBuilder();
    foreach (var info in creatingColumns)
    {
    creatingColumn.Append(string.Format(“{0} {1} {2},”,
    info.Name,
    dialect.GetTypeName(info.ColumnType),
    info.IsNotNull));
    }

    string createColums = creatingColumn.ToString();
    sqlCreateTbl.AppendFormat(sqlCreateTemplate, createColums.Remove(createColums.Length – 1, 1));

    return sqlCreateTbl.ToString();
    }

    private string preCreatedColumns()
    {
    var createdColumn = new List();
    foreach (var info in creatingColumns)
    createdColumn.Add(info.Name);

    if (creatingColumns.Count > 0) return string.Join(“,”, createdColumn.ToArray());

    return string.Empty;
    }

    private string[] SqlInsertingTable()
    {
    string sqlInsertTemplate = string.Format(“insert into {0} ({1}) values ( {2} )”,
    tableName, preCreatedColumns(), “{0},{1}”);

    string defaultValue = “1″;
    var sqlInserting = new List();
    foreach (var column in insertedColumns)
    sqlInserting.Add(string.Format(sqlInsertTemplate, column, defaultValue));

    return sqlInserting.ToArray();
    }
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s