Pandatech.EFCore.PostgresExtensions 2.0.1

• 1. Pandatech.EFCore.PostgresExtensions
  • 1.1. Features
  • 1.2. Installation
  • 1.3. Usage
    • 1.3.1. Row-Level Locking
    • 1.3.2. Npgsql COPY Integration
      • 1.3.2.1. Benchmarks
        • 1.3.2.1.1. General Benchmark Results
        • 1.3.2.1.2. Detailed Benchmark Results
        • 1.3.2.1.3. Efficiency Comparison
        • 1.3.2.1.4. Additional Notes
  • 1.4. License

1. Pandatech.EFCore.PostgresExtensions

Pandatech.EFCore.PostgresExtensions is an advanced NuGet package designed to enhance PostgreSQL functionalities within Entity Framework Core, leveraging specific features not covered by the official Npgsql.EntityFrameworkCore.PostgreSQL package. This package introduces optimized row-level locking mechanisms and an efficient, typed version of the PostgreSQL COPY operation, adhering to EF Core syntax for seamless integration into your projects.

1.1. Features

1. Row-Level Locking: Implements the PostgreSQL FOR UPDATE feature, providing three lock behaviors - Wait, Skip, and NoWait, to facilitate advanced transaction control and concurrency management.
2. Npgsql COPY Integration: Offers a high-performance, typed interface for the PostgreSQL COPY command, allowing for bulk data operations within the EF Core framework. This feature significantly enhances data insertion speeds and efficiency.

1.2. Installation

To install Pandatech.EFCore.PostgresExtensions, use the following NuGet command:

Install-Package Pandatech.EFCore.PostgresExtensions

1.3. Usage

1.3.1. Row-Level Locking

Configure your DbContext to use Npgsql and enable query locks:

services.AddDbContext<MyDbContext>(options =>
{
    options.UseNpgsql(Configuration.GetConnectionString("MyDatabaseConnection"))
           .UseQueryLocks();
});

Within a transaction scope, apply the desired lock behavior using the ForUpdate extension method:

using var transaction = _dbContext.Database.BeginTransaction();
try
{
    var entityToUpdate = _dbContext.Entities
        .Where(e => e.Id == id)
        .ForUpdate(LockBehavior.NoWait) // Or use LockBehavior.Default (Wait)/ LockBehavior.SkipLocked
        .FirstOrDefault();

    // Perform updates on entityToUpdate
    await _dbContext.SaveChangesAsync();
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();
    // Handle exception
}

1.3.2. Npgsql COPY Integration

For bulk data operations, use the BulkInsert or BulkInsertAsync extension methods:

public async Task BulkInsertExampleAsync()
{
    var users = new List<UserEntity>();
    for (int i = 0; i < 10000; i++)
    {
        users.Add(new UserEntity { /* Initialization */ });
    }

    await dbContext.Users.BulkInsertAsync(users); // Or use BulkInsert for synchronous operation
    // It also saves changes to the database
}

1.3.2.1. Benchmarks

The integration of the Npgsql COPY command showcases significant performance improvements compared to traditional EF Core and Dapper methods:

1.3.2.1.1. General Benchmark Results

1.3.2.1.2. Detailed Benchmark Results

1.3.2.1.3. Efficiency Comparison

1.3.2.1.4. Additional Notes

• The BulkInsert feature currently does not support entity properties intended for JSON storage.

• The performance metrics provided above are based on benchmarks conducted under controlled conditions. Real-world performance may vary based on specific use cases and configurations.

1.4. License

Pandatech.EFCore.PostgresExtensions is licensed under the MIT License.