Sign in Subscribe
6 min read

Why every .NET developer should know about Dapper Plus

Why every .NET developer should know about Dapper Plus

Every .NET web application requires a database for data persistence. To simplify development, most projects adopt Entity Framework Core as their default ORM
However, there often comes a point where you need faster data access, and EF no longer fits the need. So, what are the next steps?

When I  encounter this situation, I go with Dapper.
The image below shows the performance benefits from a test on a local database with 50k records.

While excellent for queries and basic CRUD operations, Dapper lacks native support for bulk operations, making its performance for inserting or updating large datasets significantly slower than that of EF Core.

Fortunately, there is a solution to this problem: Dapper Plus.

What is Dapper Plus?

Dapper Plus is a micro ORM that makes bulk operations a piece of cake.
It can be use with or without Dapper and the methods at your disposal are:

  • Bulk insert
  • Bulk update
  • Bulk delete
  • Bulk merge
  • Bulk synchronize

Each of these operations also has an asynchronous equivalent:

BulkInsertAsync

  • BulkUpdateAsync
  • BulkDeleteAsync
  • BulkMergeAsync
  • BulkSynchronizeAsync

That's the foundation. Now, let's see how it works in code.

Prerequisites

To be able to follow along, you need few things:

  • A preferred IDE (Visual Studio or Rider)
  • SQL Server

Or if you prefer a lighter setup, you can go with .NET Fiddle.

Dapper Plus in action

When I think about real word example that fits these methods, the first thing that comes to mind is a synchronization service.
The goal of such a service is to keep its domain's data synchronized with an external system or another microservice.

First, let's create a database model and complete the database setup.

 public class Product
 {
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int Id { get; set; }
     public required string Sku { get; set; }
     public required string Name { get; set; }
     public string? Description { get; set; }
     public decimal Price { get; set; }
     public bool IsActive { get; set; }
     public DateTime LastModifiedUtc { get; set; }
 }

Product.cs

Before you add table to your database, ensure the database itself has been created, because Dapper Plus can only create tables.

public static void CreateProductTable()
{
    var connection = new SqlConnection(_connectionString);

    connection.Open();
    if (!TableExist("Products"))
    {
        connection.CreateTable<Product>("Products");
    }
    

    bool TableExist(string tableName)
    {
        string query = """
                           SELECT COUNT(*) 
                           FROM INFORMATION_SCHEMA.TABLES 
                           WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = @TableName
                   """;

        using var connection = new SqlConnection(_connectionString);

        connection.Open();

        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@TableName", tableName);
            int count = (int)command.ExecuteScalar();
            return count > 0;
        }
    }

}

ProductDbContext.cs

Finally, configure the dependency injection container.

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddOpenApi(); 

builder.Services.AddScoped<ProductBulkService>(); // <-- add this line

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
}

app.UseHttpsRedirection();

Helper.CreateProductTable(); // <-- this also

app.Run();

Program.cs

With that in place, the next step is to create a service that will contain the CRUD methods for manipulating the data.
For data itself, I will use Bogus library to generate fake data from an external source for simplicity. To use Dapper Plus you need NuGet package.

private static List<Product> Prepare(int count)
{
  var skuCounter = 0;
  
  Faker<Product>? faker = new Faker<Product>()
    .RuleFor(p => p.Sku, _ => $"SKU-{Interlocked.Increment(ref skuCounter):000000}")
    .RuleFor(p => p.Name, f => f.Commerce.ProductName())
    .RuleFor(p => p.Description, f => f.Commerce.ProductDescription())
    .RuleFor(p => p.Price, f =>
    {
      // Generate a realistic price with two decimals
      var price = f.Random.Decimal(1.00m, 500.00m);
      
      return Math.Round(price, 2, MidpointRounding.AwayFromZero);
      })
    .RuleFor(p => p.IsActive, f => f.Random.Bool(0.9f))
    .RuleFor(p => p.LastModifiedUtc, f => f.Date.RecentOffset(30).UtcDateTime);

  return faker.Generate(count);
}

Bulk insert

With just single line of code, you are able to add multiple rows in a single database roundtrip.

public sealed class ProductBulkService
{
    private readonly IConfiguration _configuration;
    private readonly List<Product> _originalList;
    private readonly string _connectionString;

    public ProductBulkService(IConfiguration configuration)
    {
        _configuration = configuration;

        _originalList = Prepare(10_000);

        _connectionString = _configuration.GetConnectionString("DefaultConnection")!;
    }

    public async Task BulkInsertAsync()
    {
        await using SqlConnection connection = new SqlConnection(_connectionString);

        await connection.UseBulkOptions(options => options.InsertIfNotExists = true).BulkInsertAsync(_originalList);
    }
}

This pretty straightforward usage, but the real world applications often present two potential problems:

  • Data integrity
  • Unique Id value

Fortunately, the library provides us with two extension method o solve these exact issues:

  • InsertIfNotExists - ensures that only new entities that do not already exist in the database are inserted
  • InsertKeepIdentity - allows you to insert specific values into an identity column (e.g. GUID)

And how fast this method is check here.

Bulk update

Same as for the insert method, update also sends all changes in a single roundtrip.

public async Task BulkUpdateAsync()
{
    await using SqlConnection connection = new(_connectionString);

    Faker faker = new();

    List<Product> updatedList = [];

    foreach (var product in _originalList)
    {
        product.Price = decimal.Parse(faker.Commerce.Price());

        updatedList.Add(product);
    }

    DapperPlusManager.Entity<Product>().UseBulkOptions(options =>
    {
        options.UpdateMatchedAndConditionExpression = product => new { product.Price };
        options.CoalesceOnUpdateExpression  = product => new { product.Price };
    });

    await connection.BulkUpdateAsync(updatedList);
}

In case you need partial updates you can extend entity behavior with Conditional update options(UpdateMatchedAndOneNotConditionExpression  and UpdateMatchedAndOneNotConditionNames).
Also, if your database column value is not null but your entity's property value is null, you can opt to keep the database value with the CoalesceOnUpdateExpression and CoalesceOnUpdateNames options.

Bulk delete

Like in the update, you can have conditional delete with DeleteMatchedAndConditionNames and DeleteMatchedAndConditionExpression options. If you want to have number of affected row, well that is...possible. You just need to set UseRowsAffected to true and view the results from the ResultInfo property.

public async Task BulkDeleteAsync()
{
    await using SqlConnection connection = new(_connectionString);

    ResultInfo resultInfo = new();

    DapperPlusManager.Entity<Product>().UseBulkOptions(options =>
    {
        options.DeleteMatchedAndConditionExpression = product => new { product.Name };
    });

    await connection.UseBulkOptions(options =>
    {
        options.UseRowsAffected = true;
        options.ResultInfo = resultInfo;
    })
    .BulkDeleteAsync(_originalList);
}

Bulk merge

This is my favorite method from the list because it combines insert and update.
It performs upsert: inserting new records and updating existing ones based on a key.
If you worked with older version of EF Core you probably used AddOrUpdate method. Well this is works same, but faster. 

 public async Task BulkMergeAsync(IEnumerable<Product> products, CancellationToken ct)
 {
     await using SqlConnection connection = new(_connectionString);

     DapperPlusManager.Entity<Product>()
                      .UseBulkOptions(x =>
                      {
                          x.IgnoreOnMergeInsertExpression = product => new { product.Price };
                          x.IgnoreOnMergeUpdateExpression = product => new { product.IsActive };
                      });

     await connection.BulkMergeAsync(products);
 }

Since, this are two method in one, is logically that they share options like:

  • Ignore properties on insert or update:
    • For insert phase use IgnoreOnMergeInsertExpression or IgnoreOnMergeInsertNames
    • For update phase use IgnoreOnMergeUpdateExpression or IgnoreOnMergeUpdateNames
  • Conditional update

Bulk synchronize

In my opinion, this is most powerful method in Dapper Plus arsenal. It's designed to make a database table perfectly match a collection in your application with a single, highly efficient operation.
When it compares provided collection and existing database records it will execute a combination of operations:

  • Insert: new rows are added that do not exist in the database
  • Update: existing records will be updated if the properties have changed
  • Delete:  rows that are no longer present in your data source.

You can think of it as "all-in-one" solution.

public async Task BulkSynchronizeAsync()
{
    await using SqlConnection connection = new(_connectionString);

    DapperPlusManager.Entity<Product>()
        .UseBulkOptions(x =>
        {
            x.ColumnSynchronizeDeleteKeySubsetExpression = product => new { product.Price };
            x.SynchronizeSoftDeleteFormula = "IsSoftDeleted = 1";
        });

    await connection.BulkSynchronizeAsync(_originalList);
}

A two useful options i want to point out here are:

  • Partial synchronization: you can synchronize only a specific store by using the ColumnSynchronizeDeleteKeySubsetExpression or ColumnSynchronizeDeleteKeySubsetNames option
  • Soft delete synchronization: If you prefer performing a soft delete instead, you can use the SynchronizeSoftDeleteFormula

Bulk options

I listed most common options in corresponding methods, but that i just tip of the iceberg.
Dapper Plus over 100 extension methods to adjust your operations based on your needs.

Can I insert one or few records?

When this question arise in the development, you may think(at first) that Dapper is required. No, it's not. Dapper Plus got you covered with single extension methods. They offer same flexibility and advantages as their bulk counterparts and they are 100% free.

These single-entity methods operate on one record at a time:

  • SingleInsert
  • SingleUpdate
  • SingleDelete
  • SingleMerge
public async Task BulkSynchronizeAsync()
{
    await using SqlConnection connection = new(_connectionString);

    await connection.SingleInsertAsync(_originalList[0]);
}

Are the performance benefits true...or not?

I've mentioned a couple of times that Dapper Plus it's fast compared to Dapper.
But words without facts are just...words. So i take a look at benchmarks from official sites and i was surprised by the results:

  • Insert: 75x faster, reducing time by 99%
  • Update: 50x faster, reducing time by 98%
  • Delete: 150x faster, reducing time by 99%
  • Merge: 50x faster, reducing time by 98%

I also performed benchmarks on my local machine and got similar results, but I encourage you to try it on your machine(via .NET Fiddle or preferred IDE) because benchmarks can vary depending on your hardware and database provider.

Summary

While Dapper is famous for its speedy data querying, it tends to lag behind when it comes to bulk writes at scale. Dapper Plus steps in seamlessly to eliminate this bottleneck, transforming slow database operations into high-performance, rapid processes.