Lack of Auto-Increment Support in Cosmos DB with EF Core #1105
Description
// might want to mark this issue as bug, to be fixed in the long run
Issue
Cosmos DB does not support auto-incrementing fields like SQL's IDENTITY. When using Entity Framework Core with Cosmos DB, there is no built-in way to generate a unique, sequential global number for each entity. This makes it difficult to assign a reliable GlobalSequenceNumber across all documents.
For ex for PostgreSQL schema looks like GlobalSequenceNumber bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
Workaround
A custom EF Core interceptor is used to set the GlobalSequenceNumber for new entities. The current workaround uses UTC ticks as a unique value, but this is not a true auto-increment and can lead to collisions under high load. The recommended robust solution is to implement a counter document with optimistic concurrency, similar to other event stores to guarantee sequential numbers in Cosmos DB.
Also not sure why for efcore GlobalSequenceNumber property is init-Only
Ugly but working fix (with reflection for init prop)
services.AddDbContext<MyOwnDbContext>((provider, options) => options.UseCosmos( "...", databaseName: "mydb-dev", optionsx => { optionsx.ConnectionMode(ConnectionMode.Gateway); }) .AddInterceptors(provider.GetRequiredService<EntityModificationInterceptor>()) );public class EntityModificationInterceptor : SaveChangesInterceptor { public override InterceptionResult<int> SavingChanges( DbContextEventData eventData, InterceptionResult<int> result) { var context = eventData.Context; if (context == null) return result; var data = context.ChangeTracker.Entries().ToList(); foreach (var entry in data) { if (entry.CurrentValues.Properties.Any(p => p.Name == nameof(EventEntity.GlobalSequenceNumber))) { var currentValue = entry.CurrentValues[nameof(EventEntity.GlobalSequenceNumber)]; var tsValue = DateTime.UtcNow.Ticks; long globalSequence = currentValue is long l ? l : 0; if (globalSequence == 0) { if (entry.Entity is EventEntity entity) { var prop = entry.Entity.GetType().GetProperty(nameof(EventEntity.GlobalSequenceNumber)); if (prop != null) { prop.SetValue(entry.Entity, tsValue); } } entry.Property(nameof(EventEntity.GlobalSequenceNumber)).CurrentValue = tsValue; var typeName = entry.Entity.GetType().Name; entry.Property("__id").CurrentValue = $"{typeName}|{tsValue}"; } } } return result; } public override async ValueTask<InterceptionResult<int>> SavingChangesAsync( DbContextEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default) { SavingChanges(eventData, result); return await base.SavingChangesAsync(eventData, result, cancellationToken); } }