Generate sequential Id for primary key column in NHibernate

Question

How can I generate a Sequential guid for a primary key column in NHibernate. I know how to do that auto increment stuff. But I don't know how to create a sequential Guid for primary key.

In the database, data type of Id column is uniqueidentifier. I've checked the official docs and some other web search results. But I'm not able to find any solution. Here is what I have now:

public UserMap()
{
    Table("Users");
    Id(x => x.Id);
    Property(x => x.Username, x => x.NotNullable(true));
    Property(x => x.Email, x => x.NotNullable(true));
    Property(x => x.PasswordHash, x =>
    {
        x.NotNullable(true);
        x.Column("password_hash");
    });
}

score 2 · Accepted Answer · answered Nov 01 '16 at 16:33

2

I do that like so:

In my business entity classes

public abstract class EntityBase : IEntity
{
    public virtual Guid Id { get; protected set; }
    ...
}

The fluent mapping:

Id(x => x.Id).GeneratedBy.GuidComb();

Read this about Guid.comb

answered Nov 01 '16 at 16:33

ThommyB

1,456
16
34

score 1 · Answer 2 · answered Nov 01 '16 at 11:19

Check the doc by Adam Bar

Mapping-by-Code - Id, NaturalId

Let me cite:

The most common primary key mapping is available through Id method.
Id(x => x.Id, m =>
{
    m.Column("id");

    m.Generator(Generators.Native, g => g.Params(new
    {
        // generator-specific options
    }));

    m.Length(10);
    m.Type(new Int32Type());
    m.Access(Accessor.Field);
});
There are several features missing - like unsaved-value (will be added in NHibernate 3.3) or DDL column options.

Probably the most important option is Generator. It specifies the way NHibernate (or database) generates unique values. There are 8 predefined types of generators available through static Generators class:
public static class Generators
{
    ...

    // Guid
    public static IGeneratorDef Guid { get; }

    // sequential Guid
    public static IGeneratorDef GuidComb { get; }

So, based on that, we can adjust the mapping like that:

public UserMap()
{
    Table("Users");
    Id(x => x.Id, m =>
    {
        m.Generator(Generators.GuidComb);
    }
    ...

score 0 · Answer 3 · answered Sep 17 '21 at 05:43

If you're using SQL Server 2012 or newer, you could CREATE SEQUENCE and use it for IdGeneration. For example:

mapping.Id(x => x.Id).GeneratedBy.Sequence("TableSequence");

For this to work, you have to make sure, that in your hibernate-configuration you have property set as: <property name="dialect">NHibernate.Dialect.MsSql2012Dialect</property> or newer.

To Create a sequence, you could use Migration script as follows:

DECLARE @Command NVARCHAR(255);
DECLARE @SequenceStartId INT;

/* Create TableSequence */
IF EXISTS (SELECT * FROM sys.sequences WHERE OBJECT_ID = OBJECT_ID('dbo.TableSequence'))
BEGIN
   DROP SEQUENCE dbo.[TableSequence]
END

SET @SequenceStartId = (SELECT MAX(Id) FROM dbo.[Table]) + 1;

SET @Command = FORMATMESSAGE('
      CREATE SEQUENCE dbo.TableSequence
      START WITH %i  
      INCREMENT BY 1 AS INT;
   ', @SequenceStartId)

EXECUTE sp_executesql @Command

More about sequences: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

Generate sequential Id for primary key column in NHibernate

3 Answers3

Mapping-by-Code - Id, NaturalId