Making Dapper and JSON friends

Let's speak about improvements that we can bring to the Dapper. Improvements in the 'quality of life' category, to be precise. I think that everyone, who had some experience with SQL in the past, had a situation like that. You have a model that you want to show to the user. The data is stored in the SQL database. The model itself is straightforward from the C# perspective.

Here is it:

class User 
{
    public int Id { get; set;}
    public string Login { get; set;}
    public PhoneNumber[] Phones { get; set;}
}

class PhoneNumber 
{
    public string Number { get; set; }
    public PhoneNumberKind Kind { get; set; } 
}

enum PhoneNumberKind
{
    Office,
    Mobile,
    Home
}

It consists of a few fields and an array inside. As you may guess, the main problem will be the array because SQL doesn't have an easy way to put a list of items in the column. So, to fill the model, we will need to execute multiple queries or play around with a SQL reader. Still, both cases require additional manipulation with numerous rows to construct a model.

Sample with multiple requests:

var usersData = await connection.QueryAsync<(int Id, string Login)>("select Id, Login from Users");

var phoneNumbersData = await connection.QueryAsync<(int UserId, string Number, PhoneNumberKind Kind)>(
    "select UserId, Number, Kind from PhoneNumbers where UserId in @UserIds", new { UserIds = usersData.Select(o => o.Id)});

var phoneNumbersByUserId = phoneNumbersData
    .GroupBy(o => o.UserId)
    .ToDictionary(o => o.Key, o => o.ToArray());

var users = usersData
    .Select(o => 
        new User() 
        { 
            Id = o.Id, 
            Login = o.Login, 
            PhoneNumbers = phoneNumbersByUserId.ContainsKey(o.Id) ? 
                phoneNumbersByUserId[o.Id]
                    .Select(phone => new PhoneNumber 
                        { 
                            Number = phone.Number, 
                            Kind = phone.Kind 
                        })
                    .ToArray() :
                Array.Empty<PhoneNumber>()
        })
    .ToArray();

As for me, such a solution looks clunky and has huge potential for mistakes. Also, in this sample, we are keeping the whole result set in the memory. That is not great either. To avoid it, we need to introduce batching. At the same time, we may need to add more joins. All of that makes logic even more complex.

Making Dapper and JSON friends

At this point, we can remember the fact that the modern SQL databases have support for JSON. The MSSQL server, for example, can execute selectors and pack results into columns as a JSON. In our case, the query can be simplified to look like that:

select 
    Id,
    Login,
    (select UserId, Number, Kind from PhoneNumbers where UserId = u.Id FOR JSON PATH) as PhonesJson
from Users u

The result of the query will be next:

Id	Login	PhonesJson
16	Karita_Ringo	[{"UserId":16,"Number":"3377787","Kind":2}]
17	Chiquia_Dud	NULL
18	Caprice_Hugibert	[{"UserId":18,"Number":"8454027","Kind":0}]
19	Pat_Tedie	[{"UserId":19,"Number":"4646231","Kind":1}]
20	Vally_Fitzgerald	[{"UserId":20,"Number":"1836548","Kind":2}]
21	Jasmina_Xerxes	NULL
22	Kristy_Nathaniel	[{"UserId":22,"Number":"7018212","Kind":1}]
23	Farica_Ives	[{"UserId":23,"Number":"0874725","Kind":2}]
24	Clementine_Gun	NULL
25	Jerry_Whit	[{"UserId":25,"Number":"0013362","Kind":0}]

Now lets fill the User model based on the new query:

var usersData = await connection.QueryAsync<(int Id, string Login, string PhonesJson)>(@"
    select 
        Id,
        Login,
        (select UserId, Number, Kind from PhoneNumbers where UserId = u.Id FOR JSON PATH) as PhonesJson
    from Users u");

var users = usersData
    .Select(o => 
        new User 
        { 
            Id = o.Id, 
            Login = o.Login, 
            PhoneNumbers = !string.IsNullOrEmpty(o.PhonesJson) ? 
                JsonSerializer.Deserialize<PhoneNumber[]>(o.PhonesJson) :
                Array.Empty<PhoneNumber>()
        })
    .ToArray();

Done! The logic flow now is much simple. At the same time, this approach allows us to enable un-buffered mode and fetch rows in batches. In some scenarios, it can be a life-saving feature. We can start processing much earlier and free memory from processed rows. And all of this without the need to build complex logic.

Making Dapper and JSON even closer friends

Can we make our solution even better? Yes, we can. The Dapper has a way to add custom user-defined types. So, what if we can create a type like Json<T> and use it as a column type? It can achieve it via the SqlMapper.TypeHandler. Lets create our JsonTypeHandler<T>.

For System.Text.Json it will look like that:

public class JsonTypeHandler<T> : SqlMapper.TypeHandler<Json<T>>
{
    public override void SetValue(IDbDataParameter parameter, Json<T> value)
    {
        parameter.Value = JsonSerializer.Serialize(value.Value);
    }

    public override Json<T> Parse(object value)
    {
        if (value is string json)
        {
            return new Json<T>(JsonSerializer.Deserialize<T>(json));
        }

        return new Json<T>(default);
    }
}

If we want to use the Json<T> with PhoneNumber[] then we need to add it to SqlMapper like that:

SqlMapper.AddTypeHandler(new JsonTypeHandler<PhoneNumber[]>());

Now, lets have a look at updated sample:

var usersData = await connection.QueryAsync<(int Id, string Login, Json<PhoneNumber[]>? PhoneNumbers)>(@"
    select 
        Id,
        Login,
        (select UserId, Number, Kind from PhoneNumbers where UserId = u.Id FOR JSON PATH) as PhoneNumbers
    from Users u");

var users = usersData
    .Select(o => 
        new User 
        { 
            Id = o.Id, 
            Login = o.Login, 
            PhoneNumbers = o.PhoneNumbers?.Value ?? Array.Empty<PhoneNumber>()
        })
    .ToArray();

Such an approach allows us to expose our intention about the query more clearly. At the same time, keep the simplicity of the source code.

But there is an issue. If we want to use other Json<T> of different types like Json<User[]>, Json<EmailDetails>, Json<Role> and so on. We will need to manually add type handlers to SqlMapper for each of them. Like that:

SqlMapper.AddTypeHandler(new JsonTypeHandler<User[]>());
SqlMapper.AddTypeHandler(new JsonTypeHandler<EmailDetails>());
SqlMapper.AddTypeHandler(new JsonTypeHandler<Role>());

That's not great. It completely ruins the ease of use. I have tried to find a way to register all types at once, but the Dapper documentation didn't provide any way to achieve such behaviour. So, the only way is to add type handlers of each type. It would be a disaster, but it is the C#! It has a lot of tricks up his sleeves!

The trick

The trick that we will use is source generators! By itself, it is like an extension for the Roslyn compiler. It allows to analyze the existing source code and, based on it, generate a new one. So, the idea is simple. Let's look for usages of Json<T> and create a module initializer(another new trick) that will add all required types to SqlMapper auto-magically.

The source generator consists of the syntax receiver and the source generator. Our syntax receiver will be really stupid. Its single task will be to find the Json<T>.

It looks like that:

public class DapperSourceGeneratorSyntaxReceiver : ISyntaxReceiver
{
    public List<GenericNameSyntax> Types { get; } = new();

    public void OnVisitSyntaxNode(SyntaxNode syntaxNode)
    {
        // select all generic type usages of Json<>
        if (syntaxNode is GenericNameSyntax { Identifier.ValueText: "Json", } type)
        {
            Types.Add(type);
        }
    }
}

The source generator is a bit more complex, but the basic idea should be clear.

[Generator]
    public class DapperJsonSourceGenerator : ISourceGenerator
    {
        public void Initialize(GeneratorInitializationContext context)
        {
            // register our syntax reciver 
            context.RegisterForSyntaxNotifications(() => new DapperSourceGeneratorSyntaxReceiver());
        }

        public void Execute(GeneratorExecutionContext context)
        {
            if (context.SyntaxReceiver is not DapperSourceGeneratorSyntaxReceiver syntaxReceiver)
            {
                return;
            }

            try
            {
                // neccesary to make sure that it is our Json<T>
                var jsonType = context.Compilation.GetTypeByMetadataName("Dapper.Json.Json`1");

                var types = syntaxReceiver.Types
                    .Select(o =>
                    {
                        var semanticModel = context.Compilation.GetSemanticModel(o.SyntaxTree);
                        var type = semanticModel.GetTypeInfo(o);
                        if (type.Type is INamedTypeSymbol namedTypeSymbol &&
                            namedTypeSymbol.ConstructedFrom.Equals(jsonType))
                        {
                            // extracting the generic type
                            return namedTypeSymbol.TypeArguments.First();
                        }

                        return null;
                    })
                    .Where(o => o != null)
                    .Distinct(SymbolEqualityComparer.Default)
                    .ToArray();

                // create a module initializer and add all types to SqlMapper
                var source = @$"using System;

namespace Dapper.Json
{{
    public static class DapperJsonModuleInitializer
    {{
        [global::System.Runtime.CompilerServices.ModuleInitializer]
        public static void Init()
        {{
{types.Select(o => $@"            SqlMapper.AddTypeHandler(new JsonTypeHandler<{o.ToGlobalName()}>());").JoinWithNewLine()}
        }}
    }}
}}
";

                context.AddSource("Dapper.Json.g.cs", source.ToSourceText());
            }
            catch (Exception e)
            {
                // some error handeling to simplify source generator debuging experiance
                context.AddSource("Dapper.Json.Error.g.cs", @$"

namespace Dapper.Json
{{
    public static class DapperJsonError
    {{
        public static string Error = ""{e.Message}"";
    }}
}}");
            }
        }
    }

The final module initializer may look like that:

using System;

namespace Dapper.Json
{
    public static class DapperJsonModuleInitializer
    {
        [global::System.Runtime.CompilerServices.ModuleInitializer]
        public static void Init()
        {
            SqlMapper.AddTypeHandler(new JsonTypeHandler<global::PhoneNumber[]>());
            SqlMapper.AddTypeHandler(new JsonTypeHandler<global::User[]>());
            SqlMapper.AddTypeHandler(new JsonTypeHandler<global::EmailDetails>());
            SqlMapper.AddTypeHandler(new JsonTypeHandler<global::Role>());
        }
    }
}

Done! Whenever we use the Json<T>, it will automatically be added to SqlMapper.

How to use it

The complete instruction you can find at the repository on github. There is nothing complicated. You need to install two NuGet packages, and you are ready to go.

That is all for today. Feel free to leave a comment or create an issue on GitHub.

Thanks for your time!