Custom mapping in Dapper

Question

I'm attempting to use a CTE with Dapper and multi-mapping to get paged results. I'm hitting an inconvenience with duplicate columns; the CTE is preventing me from having to Name columns for example.

I would like to map the following query onto the following objects, not the mismatch between the column names and properties.

Query:

WITH TempSites AS(
    SELECT
        [S].[SiteID],
        [S].[Name] AS [SiteName],
        [S].[Description],
        [L].[LocationID],
        [L].[Name] AS [LocationName],
        [L].[Description] AS [LocationDescription],
        [L].[SiteID] AS [LocationSiteID],
        [L].[ReportingID]
    FROM (
        SELECT * FROM [dbo].[Sites] [1_S]
        WHERE [1_S].[StatusID] = 0
        ORDER BY [1_S].[Name]
        OFFSET 10 * (1 - 1) ROWS
        FETCH NEXT 10 ROWS ONLY
    ) S
        LEFT JOIN [dbo].[Locations] [L] ON [S].[SiteID] = [L].[SiteID]
),
MaxItems AS (SELECT COUNT(SiteID) AS MaxItems FROM Sites)

SELECT *
FROM TempSites, MaxItems

Objects:

public class Site
{
    public int SiteID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public List<Location> Locations { get; internal set; }
}

public class Location
{
    public int LocationID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid ReportingID { get; set; }
    public int SiteID { get; set; }
}

For some reason I have it in my head that a naming convention exists which will handle this scenario for me but I can't find mention of it in the docs.

Solution 1

There are more than one issues, let cover them one by one.

CTE duplicate column names:

CTE does not allow duplicate column names, so you have to resolve them using aliases, preferably using some naming convention like in your query attempt.

For some reason I have it in my head that a naming convention exists which will handle this scenario for me but I can't find mention of it in the docs.

You probably had in mind setting the DefaultTypeMap.MatchNamesWithUnderscores property to true, but as code documentation of the property states:

Should column names like User_Id be allowed to match properties/fields like UserId?

apparently this is not the solution. But the issue can easily be solved by introducing a custom naming convention, for instance "{prefix}{propertyName}" (where by default prefix is "{className}_") and implementing it via Dapper's CustomPropertyTypeMap. Here is a helper method which does that:

public static class CustomNameMap
{
    public static void SetFor<T>(string prefix = null)
    {
        if (prefix == null) prefix = typeof(T).Name + "_";
        var typeMap = new CustomPropertyTypeMap(typeof(T), (type, name) =>
        {
            if (name.StartsWith(prefix, StringComparison.OrdinalIgnoreCase))
                name = name.Substring(prefix.Length);
            return type.GetProperty(name);
        });
        SqlMapper.SetTypeMap(typeof(T), typeMap);
    }
}

Now all you need is to call it (one time):

CustomNameMap.SetFor<Location>();

apply the naming convention to your query:

WITH TempSites AS(
    SELECT
        [S].[SiteID],
        [S].[Name],
        [S].[Description],
        [L].[LocationID],
        [L].[Name] AS [Location_Name],
        [L].[Description] AS [Location_Description],
        [L].[SiteID] AS [Location_SiteID],
        [L].[ReportingID]
    FROM (
        SELECT * FROM [dbo].[Sites] [1_S]
        WHERE [1_S].[StatusID] = 0
        ORDER BY [1_S].[Name]
        OFFSET 10 * (1 - 1) ROWS
        FETCH NEXT 10 ROWS ONLY
    ) S
        LEFT JOIN [dbo].[Locations] [L] ON [S].[SiteID] = [L].[SiteID]
),
MaxItems AS (SELECT COUNT(SiteID) AS MaxItems FROM Sites)

SELECT *
FROM TempSites, MaxItems

and you are done with that part. Of course you can use shorter prefix like "Loc_" if you like.

Mapping the query result to the provided classes:

In this particular case you need to use the Query method overload that allows you to pass Func<TFirst, TSecond, TReturn> map delegate and unitilize the splitOn parameter to specify LocationID as a split column. However that's not enough. Dapper's Multi Mapping feature allows you to split a single row to a several single objects (like LINQ Join) while you need a Site with Location list (like LINQ GroupJoin).

It can be achieved by using the Query method to project into a temporary anonymous type and then use regular LINQ to produce the desired output like this:

var sites = cn.Query(sql, (Site site, Location loc) => new { site, loc }, splitOn: "LocationID")
    .GroupBy(e => e.site.SiteID)
    .Select(g =>
    {
        var site = g.First().site;
        site.Locations = g.Select(e => e.loc).Where(loc => loc != null).ToList();
        return site;
    })
    .ToList();

where cn is opened SqlConnection and sql is a string holding the above query.

Solution 2

You can map a column name with another attribute using the ColumnAttributeTypeMapper.

See my first comment on the Gist for further details.

You can do the mapping like

public class Site
{
    public int SiteID { get; set; }
    [Column("SiteName")]
    public string Name { get; set; }
    public string Description { get; set; }
    public List<Location> Locations { get; internal set; }
}

public class Location
{
    public int LocationID { get; set; }
    [Column("LocationName")]
    public string Name { get; set; }
    [Column("LocationDescription")]
    public string Description { get; set; }
    public Guid ReportingID { get; set; }
    [Column("LocationSiteID")]
    public int SiteID { get; set; }
}

Mapping can be done using either of the following 3 methods

Method 1

Manually set the custom TypeMapper for your Model once as:

Dapper.SqlMapper.SetTypeMap(typeof(Site), new ColumnAttributeTypeMapper<Site>());
Dapper.SqlMapper.SetTypeMap(typeof(Location), new ColumnAttributeTypeMapper<Location>());

Method 2

For class libraries of .NET Framework >= v4.0, you can use PreApplicationStartMethod to register your classes for custom type mapping.

using System.Web;
using Dapper;

[assembly: PreApplicationStartMethod(typeof(YourNamespace.Initiator), "RegisterModels")]

namespace YourNamespace
{
    public class Initiator
    {
        private static void RegisterModels()
        {
             SqlMapper.SetTypeMap(typeof(Site), new ColumnAttributeTypeMapper<Site>());
             SqlMapper.SetTypeMap(typeof(Location), new ColumnAttributeTypeMapper<Location>());
             // ...
        }
    }
}

Method 3

Or you can find the classes to which ColumnAttribute is applied through reflection and set type mappings. This could be a little slower, but it does all the mappings in your assembly automatically for you. Just call RegisterTypeMaps() once your assembly is loaded.

    public static void RegisterTypeMaps()
    {
        var mappedTypes = Assembly.GetAssembly(typeof (Initiator)).GetTypes().Where(
            f =>
            f.GetProperties().Any(
                p =>
                p.GetCustomAttributes(false).Any(
                    a => a.GetType().Name == ColumnAttributeTypeMapper<dynamic>.ColumnAttributeName)));

        var mapper = typeof(ColumnAttributeTypeMapper<>);
        foreach (var mappedType in mappedTypes)
        {
            var genericType = mapper.MakeGenericType(new[] { mappedType });
            SqlMapper.SetTypeMap(mappedType, Activator.CreateInstance(genericType) as SqlMapper.ITypeMap);
        }
    }

Comments

[Ant Swift]

Thanks for you're answer but the changes you made to the query have completely ignored the need for pagination and the requirement to also return the maximum number of items sites available.

[Joe]

I'm trying method 1, but I get error CS0535: 'FallbackTypeMapper' does not implement interface member 'SqlMapper.ITypeMap.FindExplicitConstructor()'. Any suggestions?

[Carvell Wakeman]

For future travelers, search for FallbackTypeMapper in this project: gist.github.com/senjacob/8539127

[Sen Jacob]

@CarvellWakeman It is the same link to my gist, given in first line of this answer.