Settings.ColumnIdentity

Settings.ColumnIdentity and Settings.HiLoSequences

How a generated primary key gets its value - the database's identity feature, or a HiLo sequence.


	`Settings.ColumnIdentity`	`Settings.HiLoSequences`
Type	`Func<Column, string>`	`List<HiLoSequence>`
Default	Provider-aware, see below	Empty
Applies to	EF Core mainly; see gotchas	EF Core
In `Database.tt`?	No	Yes

Settings.ColumnIdentity

Returns the fluent call appended after .ValueGeneratedOnAdd() for an identity column.

The default is doing more than it looks, so replace it rather than overwrite it. In order:

On EF 6 and anything below EF Core 8, it returns .UseSqlServerIdentityColumn().
On MySQL it returns an empty string. UseIdentityColumn() is a provider extension method, and Pomelo spells its equivalent UseMySqlIdentityColumn, so emitting UseIdentityColumn() for MySQL produces code that does not compile. ValueGeneratedOnAdd() has already been written and is all Pomelo needs for AUTO_INCREMENT.
It matches the column's table against Settings.HiLoSequences and returns .UseHiLo(...) where one applies.
Only then does it fall through to .UseIdentityColumn().

If you only need one extra rule, add it and hand the rest back:

Settings.ColumnIdentity = delegate(Column c)
{
    if (c.ParentTable.NameHumanCase.Equals("Order", StringComparison.InvariantCultureIgnoreCase))
        return ".UseHiLo(\"order_seq\", \"dbo\")";

    // Keep the shipped behaviour for everything else
    if (!Settings.IsEfCore8Plus())                   return ".UseSqlServerIdentityColumn()";
    if (Settings.DatabaseType == DatabaseType.MySql) return string.Empty;
    return ".UseIdentityColumn()";
};

Returning a bare ".UseIdentityColumn()" for every column is the usual mistake. It breaks MySQL and silently disables HiLoSequences.

Settings.HiLoSequences

HiLo is an identity strategy where the client reserves a block of ids from a database sequence and hands them out locally. The point is that you can insert a graph of related entities without a round trip per row to find out what id the parent got - the client already knows.

Settings.HiLoSequences = new List<HiLoSequence>
{
    new HiLoSequence
    {
        Schema         = "dbo",
        Table          = "Order",        // "*" matches every table in the schema
        SequenceName   = "OrderSequence",
        SequenceSchema = "dbo"
    }
};

The sequence must already exist in the database. The generator does not create it, and EF Core will not either unless a migration does.

When to use HiLo

Bulk inserts of related data. Identity columns force EF to insert the parent, read back the id, then insert the children. HiLo lets it batch the lot.

Ids known before SaveChanges(). Useful when you need to reference the entity - in a message, a log, a cache - before it is persisted.

Leave it alone otherwise. Identity columns are simpler, and HiLo has real costs: gaps in the sequence whenever a block is partly used, ids that are not chronological, and a dependency on every writer using the same strategy.

Gotchas

Settings.ColumnIdentity is not in Database.tt. Add the line yourself. Most people never discover it, which is fine until they need HiLo or hit the MySQL problem.

HiLo needs the sequence to exist, with a suitable increment. If EF reserves blocks of 10 and the sequence increments by 1, you will hand out ids that already belong to somebody else.

Table = "*" matches every table in that schema, which is usually more than you meant.

HiLo is EF Core only. EF 6 has no equivalent, and the setting is ignored.

A sequence default on a column is a different thing. A column declared DEFAULT (NEXT VALUE FOR [dbo].[Seq]) gets .HasDefaultValueSql() emitted automatically, regardless of Settings.GenerateHasDefaultValueSql. That is the database generating the value; HiLo is the client generating it.

Settings.ColumnIdentity

Settings.ColumnIdentity and Settings.HiLoSequences