How can I make Dapper.NET throw when result set has unmapped columns?

Question

Using the example code below as context... When I run this query I get the 'Id' field coming back as default value (which is 0 for an int). I would like to tell dapper to run in a manner where it would throw an exception if there is a column in the result set that does not get mapped to a property on my result object. (I understand that the issue is just that I need to remove the extra 'd' in the SQL query but I'm interested in having this expose itself more explicitly)

I've been unable to find anything on this topic. Please let me know if this is even possible with Dapper.

Thanks in advance (besides this issue, and for anyone who hasn't taken the plunge, Dapper really is the greatest thing since sliced bread!).

class CustomerRecord
{
    public int Id { get; set; }
    public string Name { get; set; }
}

CustomerRecord[] GetCustomerRecords()
{
    CustomerRecord[] ret;
    var sql = @"SELECT 
                 CustomerRecordId AS Idd, 
                 CustomerName as Name
                 FROM CustomerRecord";

    using (var connection = new SqlConnection(this.connectionString))
    {
        ret = connection.Query<CustomerRecord>(sql).ToArray();
    }

    return ret;
}

Answer 1

You could create your own type map where you use Dapper's DefaultTypeMap and throw an exception when it cannot find the member:

public class ThrowWhenNullTypeMap<T> : SqlMapper.ITypeMap
{
    private readonly SqlMapper.ITypeMap _defaultTypeMap = new DefaultTypeMap(typeof(T));

    public ConstructorInfo FindConstructor(string[] names, Type[] types)
    {
        return _defaultTypeMap.FindConstructor(names, types);
    }

    public ConstructorInfo FindExplicitConstructor()
    {
        return _defaultTypeMap.FindExplicitConstructor();
    }

    public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName)
    {
        return _defaultTypeMap.GetConstructorParameter(constructor, columnName);
    }

    public SqlMapper.IMemberMap GetMember(string columnName)
    {
        var member = _defaultTypeMap.GetMember(columnName);
        if (member == null)
        {
            throw new Exception();
        }
        return member;
    }
}

Downside of this, is that you have to configure all the type maps for every entity:

SqlMapper.SetTypeMap(typeof(CustomerRecord), typeof(ThrowWhenNullTypeMap<CustomerRecord>));

This could be configured using reflection, however.

Answer 2

I came here after I solved this same problem for the IEnumerable<dynamic> methods in Dapper. Then I found the proposal to solve the issue for Query<T>; but that doesn't seem to be going anywhere.

My answer builds on the answer proposed by @HenkMollema, and uses his class in the solution, so credit to him for that...

To solve the IEnumerable<dynamic> scenario, I had created a "SafeDynamic" class (follow the link above to see that). I refactored the static "Create" method into an extension method:

public static class EnumerableDynamicExtensions
{
    public static IEnumerable<dynamic> Safe(this IEnumerable<dynamic> rows)
    {
        return rows.Select(x => new SafeDynamic(x));
    }
}

and then I created a DapperExtensions class to provide 'Safe' versions of Query and Read (Read is used after QueryMultiple), to give me...

internal static class DapperExtensions
{
    public static IEnumerable<dynamic> SafeQuery(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    {
        return cnn.Query(sql, param, transaction, buffered, commandTimeout, commandType).Safe();
    }

    public static IEnumerable<dynamic> SafeRead(this SqlMapper.GridReader gridReader, bool buffered = true)
    {
        return gridReader.Read(buffered).Safe();
    }
}

So to solve this issue I added a "SafeQuery<T>" method to DapperExtensions, which takes care of setting up that type mapping for you:

    private static readonly IDictionary<Type, object> TypesThatHaveMapper = new Dictionary<Type, object>();

    public static IEnumerable<T> SafeQuery<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    {
        if (TypesThatHaveMapper.ContainsKey(typeof(T)) == false)
        {
            SqlMapper.SetTypeMap(typeof(T), new ThrowWhenNullTypeMap<T>());
            TypesThatHaveMapper.Add(typeof(T), null);
        }

        return cnn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
    }

So if the original poster changes the call to Query to become SafeQuery, it should do what he requested

Edit 25/1/17 Improvements to avoid threading issues on the static dictionary:

    private static readonly ConcurrentDictionary<Type, object> TypesThatHaveMapper = new ConcurrentDictionary<Type, object>();


    public static IEnumerable<T> SafeQuery<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    {
        TypesThatHaveMapper.AddOrUpdate(typeof(T), AddValue, UpdateValue);
        return cnn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
    }

    private static object AddValue(Type type)
    {
        SqlMapper.SetTypeMap(type, XXX); // Apologies... XXX is left to the reader, as my implementation has moved on significantly.
        return null;
    }

    private static object UpdateValue(Type type, object existingValue)
    {
        return null;
    }

Answer 3

I'd like to expand on @Richardissimo 's answer by providing a visual studio project that includes his "SafeQuery" extention to Dapper, wrapped up nice and neat and tested.

https://github.com/LarrySmith-1437/SafeDapper

I use this in all my projects now to help keep the DAL clean of mismapped data, and felt the need to share. I would have posted up a Nuget, but the dependency on Dapper itself makes it much easier to post the project where consumers can update the reference to the Dapper version they want. Consume in good health, all.