👁 Image
Pandatech.EFCore.PostgresExtensions 7.0.0

Pandatech.EFCore.PostgresExtensions

PostgreSQL-specific extensions for Entity Framework Core that fill the gaps left by the official Npgsql provider.

Targets net8.0, net9.0, and net10.0.

Installation

dotnet add package Pandatech.EFCore.PostgresExtensions

Row-level locking

PostgreSQL's FOR UPDATE clause lets you lock selected rows for the duration of a transaction. This package exposes it as a LINQ extension method with three lock behaviors.

Setup

Register the query interceptor on your DbContext:

services.AddDbContextPool<AppDbContext>(options =>
 options.UseNpgsql(connectionString)
 .UseQueryLocks());

Usage

The ForUpdate method must be called inside a transaction:

await using var transaction = await dbContext.Database.BeginTransactionAsync();

var order = await dbContext.Orders
 .Where(o => o.Id == orderId)
 .ForUpdate(LockBehavior.SkipLocked)
 .FirstOrDefaultAsync();

// Modify the locked row
order.Status = OrderStatus.Processing;
await dbContext.SaveChangesAsync();
await transaction.CommitAsync();

Lock behaviors

Random-increment sequence IDs

Generates bigint IDs that increment by a random amount within a configurable range. The IDs are unique and always increasing, but the gaps between them are unpredictable — preventing enumeration attacks while keeping an index-friendly insert order.

1. Configure the entity

public class Animal
{
 public long Id { get; set; }
 public string Name { get; set; }
}

public class AnimalConfiguration : IEntityTypeConfiguration<Animal>
{
 public void Configure(EntityTypeBuilder<Animal> builder)
 {
 builder.HasKey(x => x.Id);
 builder.Property(x => x.Id)
 .HasRandomIdSequence();
 }
}

2. Create the function in a migration

The function must be created before the table that references it. Add it manually at the top of your Up() method:

protected override void Up(MigrationBuilder migrationBuilder)
{
 // Create the sequence + function first
 migrationBuilder.CreateRandomIdSequence(
 tableName: "animal",
 pkName: "id",
 startValue: 5,
 minRandIncrementValue: 5,
 maxRandIncrementValue: 10);

 // Then create the table (the default value references the function)
 migrationBuilder.CreateTable(...);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
 migrationBuilder.DropRandomIdSequence("animal", "id");
 migrationBuilder.DropTable("animal");
}

Parameters: startValue is the first ID returned, minRandIncrementValue and maxRandIncrementValue define the random gap range between consecutive IDs.

Natural sort keys

Sorts strings that contain numbers the way a human would expect: "Item 2" before "Item 10", not after it. The package creates a PostgreSQL function that zero-pads numeric substrings, producing a sortable text key.

1. Create the function (once per database)

protected override void Up(MigrationBuilder migrationBuilder)
{
 migrationBuilder.CreateNaturalSortKeyFunction();
}

protected override void Down(MigrationBuilder migrationBuilder)
{
 migrationBuilder.DropNaturalSortKeyFunction();
}

2. Add a computed column

public class Building
{
 public long Id { get; set; }
 public string Address { get; set; }
 public string AddressNaturalSortKey { get; set; }
}

public class BuildingConfiguration : IEntityTypeConfiguration<Building>
{
 public void Configure(EntityTypeBuilder<Building> builder)
 {
 builder.Property(x => x.AddressNaturalSortKey)
 .HasNaturalSortKey("address");
 }
}

Then order by the computed column:

var sorted = await dbContext.Buildings
 .OrderBy(b => b.AddressNaturalSortKey)
 .ToListAsync();

Encrypted-column unique indexes

For columns that store encrypted data where only the first 64 characters are deterministic (e.g., hash prefix), you can create a unique index on that prefix:

// In migration Up()
migrationBuilder.CreateUniqueIndexOnEncryptedColumn("users", "email_encrypted");

// With an optional WHERE condition
migrationBuilder.CreateUniqueIndexOnEncryptedColumn("users", "email_encrypted", "is_active = true");

// In migration Down()
migrationBuilder.DropUniqueIndexOnEncryptedColumn("users", "email_encrypted");

API reference

Extension methods

Enums

LockBehavior: Default (wait), SkipLocked, NoWait.

License

MIT