How to write universal optimistic concurrency Timestamp column that works with SQL Server and with Postgresql

Question

We need to support both databases: SQL Server and Postgresql. For optimistic concurrency we use a [Timestamp] column.

For SQL Server this code works:

public class DbEntity
{
    ...
    [Timestamp]
    public byte[] Timestamp { get; set; }
    ...
}

In the database, this field is mapped to:

[Timestamp] [timestamp] NOT NULL

For Postgresql we need something like this:

public class DbEntity
{
    ...
    [Timestamp]
    public uint Version { get; set; }
    ...
}

without column in database, because xmin system column used - https://www.npgsql.org/efcore/modeling/concurrency.html?tabs=data-annotations

It is possible to write universal entities that works with both databases? I want to write them once, and do not support 2 applications or 2 branches in source control.

Answer 1

You can use have both properties on your .NET type and vary the EF model configuration based on the provider being used, and ignore the property for the other databases:

public class Blog
{
    public int Id { get; set; }

    [Timestamp]
    public byte[] Timestamp { get; set; }

    [Timestamp]
    public uint Version { get; set; }
}

// In the model configuration:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    if (Database.IsSqlServer())
    {
        modelBuilder.Entity<Blog>().Ignore(b => b.Version);
    }
    else if (Database.IsNpgsql())
    {
        modelBuilder.Entity<Blog>().Ignore(b => b.Timestamp);
    }
}