Map To a Dictionary Object From Database Results Using Dapper Dot Net

In this post, we are going to provide possible solutions How to map to a Dictionary from database using Dapper? basically, we want to Dictionary object from our query result from the database.

Mapping to a Dictionary Object from Database Results using Dapper Dot Net

Let's take an example with user table, working with Dapper in .NET, mapping database results to a Dictionary object from a User table with columns Id, Firstname, Lastname, EmailId, and CreateAt can be done in several ways:

1. Manual Mapping: In this we iterate over the database results and manually populate the Dictionary.
2. Using DynamicObject: Using Dapper's support for mapping to DynamicObjects.
3. Custom Type Mapper: In this technique we create a custom type mapper to map database results to a Dictionary.

1. Manual Mapping:

var dictionary = new Dictionary<int, User>();
foreach (var user in users)
{
    dictionary.Add(user.Id, user);
}

2. Using DynamicObject:

var dynamicObjects = connection.Query("SELECT Id, Firstname, Lastname, EmailId, CreateAt FROM Users");
var dictionary = new Dictionary<int, dynamic>();
foreach (dynamic user in dynamicObjects)
{
    dictionary.Add((int)user.Id, user);
}

3. Custom Type Mapper:

SqlMapper.SetTypeMap(
    typeof(User),
    new CustomPropertyTypeMap(
        typeof(User),
        (type, columnName) => type.GetProperties().FirstOrDefault(prop => prop.Name == columnName),
        (type, columnName) => type.GetProperties().FirstOrDefault(prop => prop.Name == columnName)
    )
);

var results = connection.Query<User>("SELECT Id, Firstname, Lastname, EmailId, CreateAt FROM Users");
var dictionary = results.ToDictionary(user => user.Id, user => user);

2

Using Non-Generic API in Dapper for Mapping to a Dictionary

I, personally, find the non-generic API approach quite straightforward and efficient. 

string sql = "SELECT Id, Fullname FROM Customer";
var dictionary = connection.Query(sql, args).ToDictionary(
    row => (string)row.Fullname,
    row => (int)row.Id);

In this way, we use a SQL query to fetch the required columns from the database and then we utilize the ToDictionary method to convert the query result into a dictionary, using the Fullname column as the key and the Id column as the value.

Our View on this Solution:

This solution offers simplicity and readability. By directly using the non-generic API, we avoid the need to define a specific type for the query result, making the code concise and easy to understand. However, it's important to ensure that the query returns the expected columns and data types to avoid runtime errors.

3

Mapping Database Results to a Dictionary with Unknown Table Structure at Runtime

If you are dealing with a table whose structure is unknown at runtime, we can still map the database results to a dictionary in Dapper. 

using var connection = new SqlConnection("connectionString");
var sql = "SELECT * FROM Product ORDER BY Id";

var queryResult = await connection.QueryAsync(sql); 
return queryResult
    .Cast<IDictionary<string, object>>()
    .Select(row => row.ToDictionary(row => row.Key, row => row.Value));

We establish a connection to the database using the provided connection string, then execute a SQL query to retrieve all columns and rows from the Product table, ordered by the Id column.

After fetching the results asynchronously, we cast each row to a dictionary of string keys and object values, allows us to handle the unknown structure of the table at runtime and then we use LINQ to convert each row dictionary into a more usable format, where each column name is mapped to its corresponding value.