Dapper. Map to SQL Column with spaces in column names

Question

I've managed to get something up and running today as small sandbox/POC project, but have seemed to bump my head on one issue...

Question:

Is there a way to get dapper to map to SQL column names with spaces in them.

I have something to this effect as my result set.

For example:

SELECT 001 AS [Col 1], 
       901 AS [Col 2],
       00454345345345435349 AS [Col 3],
       03453453453454353458 AS [Col 4] 
FROM [Some Schema].[Some Table]

And my class would look like this

    public class ClassA
    {          
        public string Col1 { get; set; }    

        public string Col2 { get; set; }

        ///... etc
     }

My implementation looks like this at the moment

 public Tuple<IList<TClass>, IList<TClass2>> QueryMultiple<TClass, TClass2>(object parameters)
 {
      List<TClass> output1;
      List<TClass2> output2;

      using (var data = this.Connection.QueryMultiple(this.GlobalParameter.RpcProcedureName, parameters, CommandType.StoredProcedure))
      {
           output1 = data.Read<TClass>().ToList();
           output2 = data.Read<TClass2>().ToList();
      }

      var result = new Tuple<IList<TClass>, IList<TClass2>>(output1, output2);
      return result;
  }

Note: The SQL cant be modified in any way.

Currently I'm going through the dapper code, and my only foreseeable solution is to add some code to "persuade" the column comparison, but not having much luck so far.

I've seen on StackOverflow that there are things like dapper extensions, but I'm hoping I can get this done without adding an extention, if not. I'll take whatever is quickest to implement.

Answer 1

There's a nuget package Dapper.FluentMap that allows you to add column name mappings (including spaces). It's similar to EntityFramework.

// Entity class.
public class Customer
{
    public string Name { get; set; }
}

// Mapper class.
public class CustomerMapper : EntityMap<Customer>
{
    public CustomerMapper()
    {
        Map(p => p.Name).ToColumn("Customer Name");
    }
}

// Initialise like so - 
FluentMapper.Initialize(a => a.AddMap(new CustomerMapper()));

see https://github.com/henkmollema/Dapper-FluentMap for more.

Answer 2

One option here would be to go via the dynamic / non-generic API, and then fetch the values out via the IDictionary<string,object> API per row, but that might be a bit tedious.

As an alternative, you can create a custom mapper, and tell dapper about it; for example:

SqlMapper.SetTypeMap(typeof(ClassA), new RemoveSpacesMap());

with:

class RemoveSpacesMap : Dapper.SqlMapper.ITypeMap
{

    System.Reflection.ConstructorInfo SqlMapper.ITypeMap.FindConstructor(string[] names, Type[] types)
    {
        return null;
    }

    SqlMapper.IMemberMap SqlMapper.ITypeMap.GetConstructorParameter(System.Reflection.ConstructorInfo constructor, string columnName)
    {
        return null;
    }

    SqlMapper.IMemberMap SqlMapper.ITypeMap.GetMember(string columnName)
    {
        var prop = typeof(ClassA).GetProperty(columnName.Replace(" ", ""));
        return prop == null ? null : new PropertyMemberMap(columnName, prop);
    }
    class PropertyMemberMap : Dapper.SqlMapper.IMemberMap
    {
        private string columnName;
        private PropertyInfo property;
        public PropertyMemberMap(string columnName, PropertyInfo property)
        {
            this.columnName = columnName;
            this.property = property;
        }
        string SqlMapper.IMemberMap.ColumnName
        {
            get { throw new NotImplementedException(); }
        }

        System.Reflection.FieldInfo SqlMapper.IMemberMap.Field
        {
            get { return null; }
        }

        Type SqlMapper.IMemberMap.MemberType
        {
            get { return property.PropertyType; }
        }

        System.Reflection.ParameterInfo SqlMapper.IMemberMap.Parameter
        {
            get { return null; }
        }

        System.Reflection.PropertyInfo SqlMapper.IMemberMap.Property
        {
            get { return property; }
        }
    }
}