Hymma.DapperIdentity 1.0.0

DapperIdentity

A high-performance, Dapper-based implementation of ASP.NET Core Identity stores for SQL Server. This package provides a lightweight alternative to Entity Framework-based Identity stores.

Features

• Full implementation of ASP.NET Core Identity stores using Dapper
• Support for users, roles, claims, logins, and tokens
• Customizable table names
• Compatible with existing ASP.NET Identity database schemas
• Significantly faster than Entity Framework for most operations

Installation

dotnet add package DapperIdentity

Quick Start

1. Create the Database Tables

Run the SQL script included in the package to create the required tables:

-- See Scripts/CreateTables.sql for the full script

Or if you already have an existing ASP.NET Identity database (created by EF), this package is compatible with those tables.

2. Configure Services

In your Program.cs or Startup.cs:

using DapperIdentity.Extensions;

var builder = WebApplication.CreateBuilder(args);

// Add Dapper Identity with default options
builder.Services.AddDapperIdentity(
    builder.Configuration.GetConnectionString("DefaultConnection")!
);

// Or with custom table names
builder.Services.AddDapperIdentity(
    builder.Configuration.GetConnectionString("DefaultConnection")!,
    options =>
    {
        options.UsersTableName = "MyUsers";
        options.RolesTableName = "MyRoles";
        options.UserClaimsTableName = "MyUserClaims";
        options.UserRolesTableName = "MyUserRoles";
        options.UserLoginsTableName = "MyUserLogins";
        options.UserTokensTableName = "MyUserTokens";
        options.RoleClaimsTableName = "MyRoleClaims";
    }
);

3. Alternative: Use with Existing Identity Configuration

If you need more control over Identity configuration:

using DapperIdentity.Extensions;
using DapperIdentity.Models;

builder.Services.AddIdentity<DapperIdentityUser, DapperIdentityRole>(options =>
{
    // Configure Identity options
    options.Password.RequireDigit = true;
    options.Password.RequiredLength = 8;
    options.Lockout.MaxFailedAccessAttempts = 5;
})
.AddDapperStores(builder.Configuration.GetConnectionString("DefaultConnection")!)
.AddDefaultTokenProviders();

Configuration Options

Implemented Interfaces

DapperUserStore

• IUserStore<DapperIdentityUser>
• IUserEmailStore<DapperIdentityUser>
• IUserPasswordStore<DapperIdentityUser>
• IUserPhoneNumberStore<DapperIdentityUser>
• IUserTwoFactorStore<DapperIdentityUser>
• IUserSecurityStampStore<DapperIdentityUser>
• IUserClaimStore<DapperIdentityUser>
• IUserLoginStore<DapperIdentityUser>
• IUserRoleStore<DapperIdentityUser>
• IUserLockoutStore<DapperIdentityUser>
• IUserAuthenticationTokenStore<DapperIdentityUser>
• IQueryableUserStore<DapperIdentityUser>

DapperRoleStore

• IRoleStore<DapperIdentityRole>
• IRoleClaimStore<DapperIdentityRole>
• IQueryableRoleStore<DapperIdentityRole>

Usage Examples

Creating a User

public class AccountController : Controller
{
    private readonly UserManager<DapperIdentityUser> _userManager;
    private readonly SignInManager<DapperIdentityUser> _signInManager;

    public AccountController(
        UserManager<DapperIdentityUser> userManager,
        SignInManager<DapperIdentityUser> signInManager)
    {
        _userManager = userManager;
        _signInManager = signInManager;
    }

    [HttpPost]
    public async Task<IActionResult> Register(RegisterModel model)
    {
        var user = new DapperIdentityUser
        {
            UserName = model.Email,
            Email = model.Email
        };

        var result = await _userManager.CreateAsync(user, model.Password);

        if (result.Succeeded)
        {
            await _signInManager.SignInAsync(user, isPersistent: false);
            return RedirectToAction("Index", "Home");
        }

        foreach (var error in result.Errors)
        {
            ModelState.AddModelError(string.Empty, error.Description);
        }

        return View(model);
    }
}

Adding Roles to a User

// Create a role
var role = new DapperIdentityRole { Name = "Admin" };
await _roleManager.CreateAsync(role);

// Add user to role
await _userManager.AddToRoleAsync(user, "Admin");

// Check if user is in role
var isAdmin = await _userManager.IsInRoleAsync(user, "Admin");

Adding Claims

await _userManager.AddClaimAsync(user, new Claim("Department", "Engineering"));

var claims = await _userManager.GetClaimsAsync(user);

Performance

DapperIdentity is designed for high performance. Below are benchmark results comparing DapperIdentity against Entity Framework Core Identity stores.

Benchmark Results

BenchmarkDotNet v0.14.0
.NET 8.0.22, X64 RyuJIT AVX2

Summary

Note: EF Core performance degrades over time due to change tracking overhead, which is especially visible in write operations. Dapper maintains consistent performance.

Running Benchmarks

To run the benchmarks yourself:

cd DapperIdentity.Benchmarks
dotnet run -c Release

Database Schema

The package uses the standard ASP.NET Identity schema. If you're migrating from EF Identity, no schema changes are required.

-- Users table
CREATE TABLE AspNetUsers (
    Id NVARCHAR(450) PRIMARY KEY,
    UserName NVARCHAR(256),
    NormalizedUserName NVARCHAR(256),
    Email NVARCHAR(256),
    NormalizedEmail NVARCHAR(256),
    EmailConfirmed BIT NOT NULL,
    PasswordHash NVARCHAR(MAX),
    SecurityStamp NVARCHAR(MAX),
    ConcurrencyStamp NVARCHAR(MAX),
    PhoneNumber NVARCHAR(MAX),
    PhoneNumberConfirmed BIT NOT NULL,
    TwoFactorEnabled BIT NOT NULL,
    LockoutEnd DATETIMEOFFSET,
    LockoutEnabled BIT NOT NULL,
    AccessFailedCount INT NOT NULL
);

-- See Scripts/CreateTables.sql for complete schema

Extending the User Model with Custom Fields

DapperIdentity allows you to add custom fields to your user model by inheriting from DapperIdentityUser and creating a custom user store.

Step 1: Create Your Custom User Class

using DapperIdentity.Models;

public class ApplicationUser : DapperIdentityUser
{
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public string? ProfilePictureUrl { get; set; }
}

Step 2: Update the Database Schema

Add the new columns to your AspNetUsers table:

ALTER TABLE AspNetUsers ADD
    FirstName NVARCHAR(100) NULL,
    LastName NVARCHAR(100) NULL,
    DateOfBirth DATE NULL,
    ProfilePictureUrl NVARCHAR(500) NULL;

Step 3: Create a Custom User Store

Inherit from DapperUserStore and override the methods that need to handle your custom fields:

using System.Data;
using Dapper;
using DapperIdentity.Stores;
using Microsoft.AspNetCore.Identity;

public class ApplicationUserStore : DapperUserStore<ApplicationUser>
{
    public ApplicationUserStore(IDbConnection connection, DapperIdentityOptions options)
        : base(connection, options)
    {
    }

    public override async Task<IdentityResult> CreateAsync(
        ApplicationUser user,
        CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();

        var sql = $@"
            INSERT INTO {Options.UsersTableName}
            (Id, UserName, NormalizedUserName, Email, NormalizedEmail,
             EmailConfirmed, PasswordHash, SecurityStamp, ConcurrencyStamp,
             PhoneNumber, PhoneNumberConfirmed, TwoFactorEnabled,
             LockoutEnd, LockoutEnabled, AccessFailedCount,
             FirstName, LastName, DateOfBirth, ProfilePictureUrl)
            VALUES
            (@Id, @UserName, @NormalizedUserName, @Email, @NormalizedEmail,
             @EmailConfirmed, @PasswordHash, @SecurityStamp, @ConcurrencyStamp,
             @PhoneNumber, @PhoneNumberConfirmed, @TwoFactorEnabled,
             @LockoutEnd, @LockoutEnabled, @AccessFailedCount,
             @FirstName, @LastName, @DateOfBirth, @ProfilePictureUrl)";

        await Connection.ExecuteAsync(sql, user);
        return IdentityResult.Success;
    }

    public override async Task<IdentityResult> UpdateAsync(
        ApplicationUser user,
        CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();

        var sql = $@"
            UPDATE {Options.UsersTableName} SET
                UserName = @UserName,
                NormalizedUserName = @NormalizedUserName,
                Email = @Email,
                NormalizedEmail = @NormalizedEmail,
                EmailConfirmed = @EmailConfirmed,
                PasswordHash = @PasswordHash,
                SecurityStamp = @SecurityStamp,
                ConcurrencyStamp = @ConcurrencyStamp,
                PhoneNumber = @PhoneNumber,
                PhoneNumberConfirmed = @PhoneNumberConfirmed,
                TwoFactorEnabled = @TwoFactorEnabled,
                LockoutEnd = @LockoutEnd,
                LockoutEnabled = @LockoutEnabled,
                AccessFailedCount = @AccessFailedCount,
                FirstName = @FirstName,
                LastName = @LastName,
                DateOfBirth = @DateOfBirth,
                ProfilePictureUrl = @ProfilePictureUrl
            WHERE Id = @Id";

        await Connection.ExecuteAsync(sql, user);
        return IdentityResult.Success;
    }
}

Step 4: Create a Generic DapperUserStore Base Class (Optional)

If you want a cleaner inheritance pattern, the package provides a generic base class. You'll need to ensure the DapperUserStore<TUser> class exists:

// This may already be in the package - check DapperIdentity.Stores namespace
public class DapperUserStore<TUser> : DapperUserStore
    where TUser : DapperIdentityUser, new()
{
    public DapperUserStore(IDbConnection connection, DapperIdentityOptions options)
        : base(connection, options)
    {
    }

    // Override methods to use TUser instead of DapperIdentityUser
}

Step 5: Register Your Custom Store

using DapperIdentity.Extensions;
using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection")!;

// Register the database connection
builder.Services.AddScoped<IDbConnection>(_ =>
    new SqlConnection(connectionString));

// Configure DapperIdentity options
builder.Services.Configure<DapperIdentityOptions>(options =>
{
    // Optionally customize table names
});

// Register Identity with your custom user type
builder.Services.AddIdentity<ApplicationUser, DapperIdentityRole>(options =>
{
    options.Password.RequireDigit = true;
    options.Password.RequiredLength = 8;
})
.AddUserStore<ApplicationUserStore>()
.AddRoleStore<DapperRoleStore>()
.AddDefaultTokenProviders();

Alternative: Use Claims for Custom Data

If you only need a few extra fields and don't want to modify the schema, consider using claims:

// Adding custom data as claims
await _userManager.AddClaimAsync(user, new Claim("FirstName", "John"));
await _userManager.AddClaimAsync(user, new Claim("LastName", "Doe"));
await _userManager.AddClaimAsync(user, new Claim("Department", "Engineering"));

// Retrieving custom data
var claims = await _userManager.GetClaimsAsync(user);
var firstName = claims.FirstOrDefault(c => c.Type == "FirstName")?.Value;

This approach:

• Requires no schema changes
• Works with the default DapperIdentityUser
• Is flexible and extensible
• Claims are automatically included in authentication tokens

License

MIT License

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.