Dapper with Attributes mapping
I try to map my Id fields with the Column Attributes but for some reason this doesn't seem to work and I can't figure out why. I set up a test project to demonstrate what I am trying.
First, I got my 2 entities:
Entity Table1
using System.Data.Linq.Mapping;
namespace DapperTestProj
{
public class Table1
{
[Column(Name = "Table1Id")]
public int Id { get; set; }
public string Column1 { get; set; }
public string Column2 { get; set; }
public Table2 Table2 { get; set; }
public Table1()
{
Table2 = new Table2();
}
}
}
and entity Table2
using System.Data.Linq.Mapping;
namespace DapperTestProj
{
public class Table2
{
[Column(Name = "Table2Id")]
public int Id { get; set; }
public string Column3 { get; set; }
public string Column4 { get; set; }
}
}
In my database I got 2 tables, also named Table1 and Table2. Both tables got their columns named equal to the entities with the exception that Table1 has a column named Table2Id and there is also a foreign key between Table1.Table2Id and Table2.Id.
Also there is 1 record each in both tables and those got both the Id 2.
What I try next is to execute a query with dapper and it should return a object of type Table1. This works, but both the property Table1.Id and Table1.Table2.Id remains 0 (default integer). I expect the column attributes would map the Id fields but clearly this isn't happing.
This is the query and mapping I am executing in code:
private Table1 TestMethod(IDbConnection connection)
{
var result = connection.Query<Table1, Table2, Table1>(
@"SELECT
T1.Id as Table1Id,
T1.Column1 as Column1,
T1.Column2 as Column2,
T2.Id as Table2Id,
T2.Column3 as Column3,
T2.Column4 as Column4
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Table2Id = T2.Id",
(table1, table2) =>
{
table1.Table2 = table2;
return table1;
},
splitOn: "Table2Id"
).SingleOrDefault();
return result;
}
Now I could rename the both Id property fields in the entities to Table1Id and Table2Id but I prefer Id instead cause of the more logic code like Table1.Id instead of Table1.Table1Id. So I was wondering, is it possible what I want here and if so, how?
Edit:
I found this topic: Manually Map column names with class properties
And with the code in the first post of Kaleb Pederson it is possible to use attributes when needed with the FallBackTypeMapper class and the ColumnAttributeTypeMapper class. All that is needed is to add the required classes to the typemapping with:
SqlMapper.SetTypeMap(typeof(Table1), new ColumnAttributeTypeMapper<Table1>());
SqlMapper.SetTypeMap(typeof(Table2), new ColumnAttributeTypeMapper<Table2>());
But with many entities this list will grow long. Also you need to add every class manually to the list and I was wondering if this could be done automaticly en more generic with Reflection. I found a code fragment that is able to get all the types:
const string @namespace = "DapperTestProj.Entities";
var types = from type in Assembly.GetExecutingAssembly().GetTypes()
where type.IsClass && type.Namespace == @namespace
select type;
And looping through all the types, I can do this, only problem I have now is what code fragment do I need to have or need to put on the place where the questionmarks are right now?
typeList.ToList().ForEach(type => SqlMapper.SetTypeMap(type,
new ColumnAttributeTypeMapper</*???*/>()));
Edit:
After more searching, I found the solution for my last problem:
typeList.ToList().ForEach(type =>
{
var mapper = (SqlMapper.ITypeMap)Activator.CreateInstance(
typeof(ColumnAttributeTypeMapper<>)
.MakeGenericType(type));
SqlMapper.SetTypeMap(type, mapper);
});
Answer
For the completion of the solution, I want to share the code I found and put together with those who are interested.
Instead of (ab)using the System.Data.Linq.Mapping.ColumnAttribute, it might be more logic (and probably save, although the chance will be very small that Microsoft will change the linq to sql ColumnAttribute class) to create our own ColumnAttribute class:
ColumnAttribute.cs
using System;
namespace DapperTestProj.DapperAttributeMapper //Maybe a better namespace here
{
[AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
public class ColumnAttribute : Attribute
{
public string Name { get; set; }
public ColumnAttribute(string name)
{
Name = name;
}
}
}
Found in the topic I mentioned earlier, the FallBackTypeMapper and the ColumnAttributeTypeMapper classes:
FallBackTypeMapper.cs
using System;
using System.Collections.Generic;
using System.Reflection;
using Dapper;
namespace DapperTestProj.DapperAttributeMapper
{
public class FallBackTypeMapper : SqlMapper.ITypeMap
{
private readonly IEnumerable<SqlMapper.ITypeMap> _mappers;
public FallBackTypeMapper(IEnumerable<SqlMapper.ITypeMap> mappers)
{
_mappers = mappers;
}
public ConstructorInfo FindConstructor(string[] names, Type[] types)
{
foreach (var mapper in _mappers)
{
try
{
var result = mapper.FindConstructor(names, types);
if (result != null)
{
return result;
}
}
catch (NotImplementedException nix)
{
// the CustomPropertyTypeMap only supports a no-args
// constructor and throws a not implemented exception.
// to work around that, catch and ignore.
}
}
return null;
}
public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName)
{
foreach (var mapper in _mappers)
{
try
{
var result = mapper.GetConstructorParameter(constructor, columnName);
if (result != null)
{
return result;
}
}
catch (NotImplementedException nix)
{
// the CustomPropertyTypeMap only supports a no-args
// constructor and throws a not implemented exception.
// to work around that, catch and ignore.
}
}
return null;
}
public SqlMapper.IMemberMap GetMember(string columnName)
{
foreach (var mapper in _mappers)
{
try
{
var result = mapper.GetMember(columnName);
if (result != null)
{
return result;
}
}
catch (NotImplementedException nix)
{
// the CustomPropertyTypeMap only supports a no-args
// constructor and throws a not implemented exception.
// to work around that, catch and ignore.
}
}
return null;
}
}
}
ColumnAttributeTypeMapper.cs
using System.Linq;
using Dapper;
namespace DapperTestProj.DapperAttributeMapper
{
public class ColumnAttributeTypeMapper<T> : FallBackTypeMapper
{
public ColumnAttributeTypeMapper()
: base(new SqlMapper.ITypeMap[]
{
new CustomPropertyTypeMap(typeof(T),
(type, columnName) =>
type.GetProperties().FirstOrDefault(prop =>
prop.GetCustomAttributes(false)
.OfType<ColumnAttribute>()
.Any(attribute => attribute.Name == columnName)
)
),
new DefaultTypeMap(typeof(T))
})
{
}
}
}
and finally, the TypeMapper.cs to initialize the mapping.
using System;
using System.Linq;
using System.Reflection;
using Dapper;
namespace DapperTestProj.DapperAttributeMapper
{
public static class TypeMapper
{
public static void Initialize(string @namespace)
{
var types = from assem in AppDomain.CurrentDomain.GetAssemblies().ToList()
from type in assem.GetTypes()
where type.IsClass && type.Namespace == @namespace
select type;
types.ToList().ForEach(type =>
{
var mapper = (SqlMapper.ITypeMap)Activator
.CreateInstance(typeof(ColumnAttributeTypeMapper<>)
.MakeGenericType(type));
SqlMapper.SetTypeMap(type, mapper);
});
}
}
}
At start up, TypeMapper.Initialize needs to be called:
TypeMapper.Initialize("DapperTestProj.Entities");
And you can start using attributes for the entity properties
using DapperTestProj.DapperAttributeMapper;
namespace DapperTestProj.Entities
{
public class Table1
{
[Column("Table1Id")]
public int Id { get; set; }
public string Column1 { get; set; }
public string Column2 { get; set; }
public Table2 Table2 { get; set; }
public Table1()
{
Table2 = new Table2();
}
}
}