How can I make Dapper.NET map unmapped columns to a dictionary?

Question

I have tables that will have some known, some unknown columns, by allowing some dynamic SQL for the select. I'm looking to have any columns that don't have a matching property to be added to a dictionary. Based on another question on How can I make Dapper.NET throw when result set has unmapped columns?, but instead of throwing an error I'd like to just map those columns to a dictionary that will be on the class.

public SqlMapper.IMemberMap GetMember( string columnName ) {
            var fallbackMappers = new List<SqlMapper.ITypeMap>();
            fallbackMappers.Add( _defaultTypeMap );

            var fallbackTypeMapper = new FallbackTypeMapper(fallbackMappers);

            var member = fallbackTypeMapper.GetMember(columnName);
            if(member == null ) {
                throw new Exception();
            }
            return member;
        }
//...
public static async Task<IEnumerable<T>> QueryAsyncOtherProps<T>(
            this IDbConnection cnn,
            string sql,
            object param = null,
            IDbTransaction transaction = null,
            int? commandTimeout = default,
            CommandType? commandType = default
        )
            where T : BaseSimpleType {
            lock ( _lock ) {
                if ( TypesThatHaveMapper.ContainsKey( typeof( T ) ) == false ) {
                    SqlMapper.SetTypeMap( typeof( T ), new NullTypeMapToOtherProperties<T>() );
                    TypesThatHaveMapper.Add( typeof( T ), null );
                }
            }
            return await cnn.QueryAsync<T>( sql, param, transaction, commandTimeout, commandType );
        }

My BaseSimpleType just contains a dictionary property called OtherProperties, so this only would get called on types that have that field and the name is consistent. Is there a different way to try to do this with Dapper?

score 1 · Answer 1 · answered Feb 18 '21 at 20:12

I've got the same requirement, I wanted to map all missing fields of a records into a IDictionary<string, object> field. I've searched for enough long that I decided to implement it myself.

I've created a Dapper Extension class. It contains only a new method : .QueryWithExtraFields() and pass as an argument an Action<T, Dictionary> function that will be called for every row, the function will receive the class T instance and a IDictionary<string,object> of every field not mapped.

With this Action<> argument you can control to either set it inside your class or do something else with unmapped fields.

Expected Dapper Return Class

private class ReturnTestClass
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IDictionary<string,object> ExtraFields { get; set; }
}

Usage

var results = dbConnection.QueryWithExtraFields<ReturnTestClass>(
    sql: "select 1 id, 'Test' as Name, 2 as FieldNumber2, 'other field' as FieldNumber3",
    unmappedFieldsAction: (o, unmappedFields) => o.ExtraFields= unmappedFields); //Here is where you place the dictionary inside you type `T` class

DapperExtension.cs class code:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using Dapper;
namespace Dapper {
   public static class DapperExtension {
        public static IReadOnlyList<T> QueryWithExtraFields<T>(
            this DbConnection connection,
            string sql,
            Action<T, IDictionary<string, object>> unmappedFieldsAction,
            object param = null,
            DbTransaction transaction = null,
            int? commandTimeout = null) where T : new()
        {
            Dictionary<string, PropertyInfo> propertySetters = typeof(T)
                .GetProperties().Where(p => p.CanRead && p.CanWrite)
                .ToDictionary(p => p.Name.ToLowerInvariant(), p => p);

            return connection.Query(sql: sql, param: param, transaction: transaction, commandTimeout: commandTimeout.GetValueOrDefault(_defaultCommandTimeout))
                .Select(row =>
                {
                    IDictionary<string, object> rowDict = (IDictionary<string, object>) row;
                    T instance = new T();
                    rowDict.Where(o => propertySetters.ContainsKey(o.Key.ToLowerInvariant()))
                        .ToList().ForEach(o => propertySetters[o.Key.ToLowerInvariant()].SetValue(instance, o.Value));
                    unmappedFieldsAction(instance,
                        rowDict.Where(o => !propertySetters.ContainsKey(o.Key.ToLowerInvariant()))
                            .ToDictionary(k => k.Key,
                                v => v.Value));
                    return instance;
                })
                .ToArray();
        }
    }
}

How can I make Dapper.NET map unmapped columns to a dictionary?

1 Answers1