This is Part 9 of a series on using Dapper to simplify data access with ADO.NET

In our last post, we looked at controlling database timeouts with Dapper.

This post will look at how to deal with dynamic types.

Throughout this series and this blog in general, I have exclusively used strong types. The ability to have the compiler and the runtime verify your types is brilliant.

However, strong typing is unavailable in some languages (and circumstances), and C# (and .NET) can operate under those circumstances.

Let us take our usual example:

public sealed class Spy
{
    public int SpyID { get; }
    public string Name { get; } = null!;
    public DateTime DateOfBirth { get; }
}

We know our type and it’s properties well, so we can tell Dapper exactly how to bind it.

app.MapGet("/Info/{spyID:int}", async (SqlConnection cn, int spyID) =>
{
    const string query = "SELECT * FROM Spies WHERE SpyID = @SpyID";
    var result = await cn.QuerySingleAsync<Spy>(query, new { SpyID = spyID });

    return result;
});

This returns the expected result.

Dapper9Typed

The IDE tells us at all times what types we are operating with.

IntellisenseTyped

However, there are circumstances when you cannot or don’t wish to use strong types. You may be doing some rapid prototyping. Or the source of data is in flux.

Dapper can still help under these circumstances.

We simply don’t use the generic methods for querying data.

In this case:

app.MapGet("/Info/Dynamic/{spyID:int}", async (SqlConnection cn, int spyID) =>
{
    const string query = "SELECT * FROM Spies WHERE SpyID = @SpyID";
    var result = await cn.QuerySingleAsync(query, new { SpyID = spyID });

    return result;
});

The magic is taking place here

var result = await cn.QuerySingleAsync(query, new { SpyID = spyID });

This returns the following:

DynamicResponse

Notice that all the properties from the query are returned in the response.

The IDE tells us we are no longer dealing with a type, but a dynamic object.

IDEDynamic

This further means you can interact with the returned object and access properties you know to be there. At runtime, the values will be fetched.

The problem is what happens if you try to access a property that doesn’t exist - you will get null.

In other words, the compiler will assume you know what you are doing when interacting with your object.

app.MapGet("/Info/Interact/{spyID:int}", async (SqlConnection cn, int spyID) =>
{
    const string query = "SELECT * FROM Spies WHERE SpyID = @SpyID";
    var result = await cn.QuerySingleAsync(query, new { SpyID = spyID });

    // Create an anonymous object
    var spy = new
    {
        result.SpyID,
        result.Name,
        result.DateOfBirth,
        // This property does not exist
        result.ThisDoesNotExist
    };
    
    return spy;
});

If we run this endpoint we get the following response:

DynamicMappedResponse

You can see the fake property ThisDoesNotExist returned a null.

This flexibility is a double-edged sword. Changes to the database schema and query will lead to silently unmapped and/or discarded data in query responses.

The methods for fetching collections also work with dynamic objects.

app.MapGet("/Info/", async (SqlConnection cn) =>
{
    const string query = "SELECT TOP 5 *  FROM Spies";
    var result = await cn.QueryAsync(query);

    return result;
});

This returns the following:

DynamicCollections

TLDR

Dapper can also work in situations where you need dynamic typing.

The code is in my GitHub.

Happy hacking!