Custom Type Handling with Dapper in .NET

Dapper is an opensource performant ORM (Object Relational Mapping) library used extensively in the .NET world

If you want to learn how we can use it in .NET, read this article :

How to use Dapper in .NET 6.0 for CRUD Operations

• Adding an entity for the demo

• Introducing value type

• Handling conversion in configuration

• Querying data with Dapper

• Create type handler

Basic User Entity

We can take an example of a basic user entity, which contains a couple of properties:

public sealed class User
{
    public Guid Id { get; private set; }

    public string Name { get; private set; } = string.Empty;

    public string Email { get; private set; }

    public string PasswordSalt { get; private set; } = string.Empty;

    public string PasswordHash { get; private set; } = string.Empty;
}

Introducing Value Types

After a couple of time, we have decided to introduce value types in our project for example in the current situation we could add an Email value type like this :

public sealed record Email(string Value)
{
    public static Email Empty => new(string.Empty);
}

Handling Conversion in Configuration

We need to add configuration so that type can be handled automatically in the configuration class of our entity like this :

internal class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.Property(user => user.Email)
            .HasMaxLength(400)
            .HasConversion(email => email.Value, value => new Email(value))
            .IsRequired();
    }
}

Querying data with Dapper

Suppose this is our example where we are trying to retrieve a user using Dapper.

Let’s ignore the discussion that I could have used EF Core, let’s suppose this query is much more complex and I am displaying this for the sake of how we can solve complex types of problems.

public async Task<User?> GetUserAsync(
    Email email,
    string password,
    CancellationToken cancellationToken = default)
{
    using var connection = _sqlConnectionFactory.CreateConnection();
    var sql = """
        SELECT
            u."Id",
            u."Name",
            u."Email",
            u."PasswordSalt",
            u."PasswordHash"
        FROM users AS u
        WHERE u."Email" = @Email
        AND u."IsActive" = true;
    """;
    var parameters = new
    {
        Email = email.Value
    };
    User? user = await connection
        .QueryFirstOrDefaultAsync<User>(sql, parameters, cancellationToken);
    return user;
}

This code will throw an error right something similar to this :

System.Data.DataException: Error parsing column 
(Email=test-user@gmail.com - String)
System.InvalidCastException: Invalid cast from 'System.String' to
'Domain.Common.ValueTypes.Email'.

Error is fine because the database Email type is a string, not the Email type we defined. This is where the type handler comes to the rescue.

How to create a type handler in .NET?

Type handlers handle complex types and parse them as per needs.

We can utilize SqlMapper.TypeHandler from dapper for such a purpose.

Let’s create our Email type handler to resolve our issue :

public class EmailTypeHandler : SqlMapper.TypeHandler<Email>
{
    public override Email Parse(object value)
    {
        return new Email((string)value);
    }
    public override void SetValue(IDbDataParameter parameter, Email value)
    {
        parameter.Value = value.Address;
    }
}

This will take care of the conversion for us, you can add a debugger to make sure, it works.

The last thing is to register this type of handler with DI :

private static IServiceCollection RegisterTypeHandlers
(this IServiceCollection services)
{
    SqlMapper.AddTypeHandler(new EmailTypeHandler());
    return services;
}