Identity, Sequences, rowversion, Computed Columns, Collations, and SQL Server Type Mapping

Learning outcomes

1. The problem: the same C# model does not mean the same database behavior

Through Chapter 18, ServiceHub deliberately used SQLite as the mandatory baseline and an application-managed Guid Revision token. Moving to SQL Server introduces store behaviors that did not exist in that lab: IDENTITY, sequences, HiLo allocation, rowversion, persisted computed columns, SQL Server collations, and richer type facets. Those are not cosmetic provider switches. They change generated DDL, write round trips, concurrency predicates, value precision, and sometimes query plans.

2. Reproduce the provider boundary with a free local SQL Server path

Use SQL Server 2025 Developer edition locally (Windows installation or a supported container) for the mandatory provider lab. Developer edition is free for development/test but not a production license. Azure SQL is an optional cloud comparison, not a requirement. Keep the connection string outside source control.

dotnet add src/ServiceHub.SqlServerLab package Microsoft.EntityFrameworkCore.SqlServer --version 10.0.11dotnet add src/ServiceHub.SqlServerLab package Microsoft.EntityFrameworkCore.Design --version 10.0.11dotnet tool update --local dotnet-ef --version 10.0.11dotnet --infodotnet ef --version

var connectionString = configuration.GetConnectionString("SqlServer")    ?? Environment.GetEnvironmentVariable("SERVICEHUB_SQLSERVER")    ?? throw new InvalidOperationException("SQL Server connection is not configured.");services.AddDbContext<SqlServerServiceHubContext>(options =>    options.UseSqlServer(connectionString));

The NuGet 10.0.11 SQL Server provider depends on Microsoft.Data.SqlClient 6.1.6 or later. Record the actual server build and database compatibility level as part of every provider-specific lab because SQL Server and Azure SQL features do not move in lockstep.

3. IDENTITY is the default numeric generated-key strategy

For a numeric primary key configured as generated on add, the SQL Server provider normally creates an IDENTITY column. The value is generated by the database, so EF treats a new entity key as temporary until the INSERT returns the real value. This is different from SQLite implementation details even though the C# property may still be int Id.

modelBuilder.Entity<SqlServerWorkOrder>(entity =>{    entity.ToTable("work_orders");    entity.HasKey(x => x.Id);    entity.Property(x => x.Id)        .HasColumnName("work_order_id")        .UseIdentityColumn(seed: 1, increment: 1);});

CREATE TABLE [work_orders] (    [work_order_id] int NOT NULL IDENTITY(1,1),    ...    CONSTRAINT [PK_work_orders] PRIMARY KEY ([work_order_id]));

Gaps in an identity are normal: rollback, caching, failover, and deleted rows can leave holes. Do not use identity continuity as a business guarantee. ServiceHub continues to keep WorkOrderNumber as the externally meaningful business key.

4. Sequences and HiLo solve different allocation problems

A SQL Server sequence is a schema object independent of any table. You can use it directly as a default or let EF use a sequence-based HiLo strategy. HiLo reduces key-generation round trips by allocating a range of IDs in memory after obtaining a high value; the tradeoff is that unused ranges and gaps become normal and the strategy is provider/model infrastructure, not business numbering.

modelBuilder.HasSequence<long>("service_ticket_seq")    .StartsAt(100_000)    .IncrementsBy(1);modelBuilder.Entity<SqlServerWorkOrder>()    .Property(x => x.InternalTicketNumber)    .HasDefaultValueSql("NEXT VALUE FOR [service_ticket_seq]");

// Alternative lab model; do not combine this with IDENTITY for the same key.modelBuilder.UseHiLo("servicehub_hilo");

5. rowversion is a SQL Server concurrency mechanism, not a portable byte[] timestamp

SQL Server rowversion is an automatically generated binary value that changes when the row is updated. EF maps it as both value-generated and a concurrency token. The provider then includes the original version in UPDATE/DELETE predicates, and a zero-row result becomes DbUpdateConcurrencyException. It is not a wall-clock timestamp and has no SQLite equivalent.

public sealed class SqlServerWorkOrder{    public int Id { get; private set; }    public string WorkOrderNumber { get; private set; } = null!;    public string CustomerName { get; private set; } = null!;    public byte[] Version { get; private set; } = [];}modelBuilder.Entity<SqlServerWorkOrder>()    .Property(x => x.Version)    .IsRowVersion();

UPDATE [work_orders]SET [CustomerName] = @p0OUTPUT INSERTED.[Version]WHERE [work_order_id] = @p1 AND [Version] = @p2;

The returned rowversion replaces the tracked current/original value after a successful save. Keep the course portable Guid Revision token for SQLite and provider-neutral examples; use rowversion only inside the SQL Server boundary.

6. Type facets are data semantics: Unicode, precision, and time

CLR types do not specify SQL Server storage precision on their own. Define facets that follow the domain and query workload. For money-like values, precision/scale is a business rule; for strings, Unicode and length affect storage/indexability; for times, choose datetime2 or datetimeoffset deliberately and define precision where comparisons or external interchange depend on it.

var e = modelBuilder.Entity<SqlServerWorkOrder>();e.Property(x => x.WorkOrderNumber).HasMaxLength(40).IsUnicode(false);e.Property(x => x.CustomerName).HasMaxLength(200).IsUnicode();e.Property(x => x.EstimatedCost).HasPrecision(12, 2);e.Property(x => x.OpenedUtc).HasColumnType("datetimeoffset(3)");e.Property(x => x.ClosedUtc).HasColumnType("datetime2(3)");

7. Defaults and computed columns run in the database

A default is used when an INSERT does not provide a value. A computed column derives its value from other columns. These are store behaviors, so EF model configuration must match the actual DDL and value-generation metadata.

e.Property(x => x.CreatedUtc)    .HasDefaultValueSql("SYSUTCDATETIME()");e.Property(x => x.SearchLabel)    .HasComputedColumnSql(        "CONCAT([WorkOrderNumber], N' · ', [CustomerName])",        stored: true);

[CreatedUtc] datetime2 NOT NULL DEFAULT (SYSUTCDATETIME()),[SearchLabel] AS (CONCAT([WorkOrderNumber],N' · ',[CustomerName])) PERSISTED

Do not set a computed property as though it were ordinary writable state. Verify ValueGenerated metadata and inspect INSERT/UPDATE commands to ensure EF is not sending a value the store owns.

8. Collation decides text equality and ordering—and can decide index usage

SQL Server installations are commonly case-insensitive by default, while .NET string equality is case-sensitive. EF translates simple string equality to SQL equality and therefore inherits the database/column collation. If the domain requires a particular rule, configure it at database or column scope. Query-level EF.Functions.Collate is an escape hatch but may prevent use of an index whose collation differs.

e.Property(x => x.WorkOrderNumber)    .UseCollation("Latin1_General_100_BIN2");

var exact = await db.WorkOrders    .Where(w => EF.Functions.Collate(        w.CustomerName,        "SQL_Latin1_General_CP1_CS_AS") == customer)    .ToListAsync(ct);

9. Failure case: carrying explicit SQLite IDs into an IDENTITY table

A data-import script materializes old rows, copies their integer IDs, and calls Add. SQL Server rejects the insert because explicit values cannot normally be inserted into an identity column.

SqlException: Cannot insert explicit value for identity column in table'work_orders' when IDENTITY_INSERT is set to OFF.

Repair: for ordinary application inserts, leave the identity key unset and preserve external/business identity in WorkOrderNumber. If a one-time migration must preserve physical IDs, design a reviewed data-migration step that scopes SET IDENTITY_INSERT ... ON/OFF, validates uniqueness/foreign keys, and never becomes normal application behavior.

10. Mandatory lab: inspect model → migration → INSERT → returned values

1. Create a disposable SQL Server 2025 Developer database such as ServiceHubSqlServerLab; never point these exercises at production.
2. Build SqlServerServiceHubContext with identity, explicit facets, a store default, a persisted computed column, and Version.IsRowVersion().
3. Add a migration named SqlServerProviderBaseline and inspect both C# operations and generated SQL before applying it.
4. Insert one work order without setting the identity, computed value, default timestamp, or rowversion.
5. After SaveChangesAsync, print Id, CreatedUtc, SearchLabel, and the hex rowversion; inspect EF command logs for returned store values.
6. Update the row from two contexts to re-confirm the concurrency predicate and conflict behavior learned in Chapter 13.
7. Query sys.columns, sys.default_constraints, and sys.indexes to verify the live database rather than relying only on EF metadata.

11. Production judgment and bridge

SQL Server-specific value generation and type mapping are appropriate when the provider is a deliberate architectural dependency. Pin Microsoft EF packages at 10.0.11, record Microsoft.Data.SqlClient/server/compatibility levels, and review migrations for generated-value or collation changes. Rowversion protects a row from lost updates but does not replace aggregate/business concurrency policy. Collations and type facets can affect indexes and query results. The next lesson adds temporal history and JSON—features where server version and compatibility level become even more visible.

Authoritative references

These features are provider- and server-sensitive; re-check the SQL Server provider and engine documentation before changing a production model.

• SQL Server value generation - EF Core — IDENTITY, sequences, HiLo and rowversion
• SQL Server columns - EF Core — Unicode/UTF-8 and sparse-column provider features
• Collations and case sensitivity - EF Core — database/column/query collation and index implications
• Generated values - EF Core — defaults, computed columns and value-generation metadata
• Concurrency conflicts - EF Core — concurrency-token predicates and conflict resolution
• SQL Server provider - EF Core — provider configuration, compatibility and Azure SQL distinctions