RowVersion implementation on Entity Framework for PostgreSQL

Question

I am using Entity Framework 6 with PostgreSQL. I have an entity in which I want to prevent concurrency issues, following this documentation I added a RowVersion property with [Timestamp] attribute, however after saving changes to the entity the column RowVersion value stays the same in the database.

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

Am I missing something or is there another way to handle it in PostgreSQL?

Answer 1

Based on postgresql documentation, xmin is one of the hidden columns in Postgres.

The thing that you need is to create a property with type uint and then : For Data Annotation :

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

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

For Fluent API:

class MyContext : DbContext
{
    public DbSet<SomeEntity> SomeEntities { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SomeEntity>()
            .Property(b => b.Version)
            .IsRowVersion();
    }
}

public class SomeEntity
{
    public int Id { get; set; }
    public uint Version { get; set; }
}

It maps version perpery to hidden xmin column. So there is no new column if you apply migration.

For more information, you can read npgsql docs about it.

Answer 2

>= Npgsql v7.0

A lot of the answers here are outdated. Since Npgsql v7.0 (released in 9th November 2022) there is a new recommended approach.

First, let me say that Microsoft explicitly mentions in their EF Core docs that when using "native database-generated concurrency tokens" (i. e. rowversion for SQL Server, xmin for PostgreSQL etc.) the implementation is database specific.

Here quoted from MS Docs for EF Core: Hanlding concurrency conflicts - Native database-generated concurrency tokens:

The rowversion type shown above is a SQL Server-specific feature; the details on setting up an automatically-updating concurrency token differ across databases, and some databases don't support these at all (e.g. SQLite). Consult your provider documentation for the precise details.

So when you take that advice and have a look at the provider which is Npgsql for Postgres, they have very clear documentation on that.

---

Quoting from the Nggsql documentation:

Concurrency Tokens

[!NOTE] Please read the general Entity Framework Core docs on concurrency tokens.

Entity Framework Core supports the concept of optimistic concurrency - a property on your entity is designated as a concurrency token, and EF Core detects concurrent modifications by checking whether that token has changed since the entity was read.

The PostgreSQL xmin system column

Although applications can update concurrency tokens themselves, we frequently rely on the database automatically updating a column on update - a "last modified" timestamp, an SQL Server rowversion, etc. Unfortunately PostgreSQL doesn't have such auto-updating columns - but there is one feature that can be used for concurrency token. All PostgreSQL tables have a set of implicit and hidden system columns, among which xmin holds the ID of the latest updating transaction. Since this value automatically gets updated every time the row is changed, it is ideal for use as a concurrency token.

Starting with version 7.0, you can map a uint property to the PostgreSQL xmin system column using the standard EF Core mechanisms:

Data Annotations

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

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

Fluent API

class MyContext : DbContext {
    public DbSet<SomeEntity> SomeEntities { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SomeEntity>()
            .Property(b => b.Version)
            .IsRowVersion();
    }
}

public class SomeEntity {
    public int Id { get; set; }
    public uint Version { get; set; }
}

---

In older version of the provider, use the following instead:

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.Entity<Blog>().UseXminAsConcurrencyToken();
}

Answer 3

Late 2024 update for Npgsql.EntityFrameworkCore.PostgreSQL 8.0.2. Method UseXminAsConcurrencyToken is obsolete now. It says:

Use EF Core's standard IsRowVersion() or [Timestamp], see https://learn.microsoft.com/ef/core/saving/concurrency

Answer 4

Just an updated answer for EF Core in case anyone else wanders here.

The Npgsql framework has built-in support for this using the hidden system column xmin that the OP is using in his entity as a NotMapped property.

As referenced here, you can set the xmin column as a concurrency token within EF by calling the UseXminAsConcurrencyToken method on your entity within its OnModelCreating method via Fluent API (a Data Annotation is not available at this time as far as I'm aware).

For anyone already using Fluent API configurations, it's as simple as this:

public class AwesomeEntityConfiguration : IEntityTypeConfiguration<AwesomeEntity>
{
    public void Configure(EntityTypeBuilder<AwesomeEntity> builder)
    {
        builder.UseXminAsConcurrencyToken();
    }
}

Answer 5

/// <summary>
/// Meant to validate concurrency en database update
/// This column is updates itself in database and only works in postgresql
/// </summary>
[ConcurrencyCheck]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
//[NotMapped]
public string xmin { get; set; }

Had to add [NotMapped] attribute just for the column not to be added in the migration, commented it after database-update.