Map multiple columns to single property with Dapper

Question

I'm working with a database that is mildly inconsistent in places. For example, there are two stored procedures that return basically the exact same result set structure, yet, for whatever reason, the DBA named the first field differently in one than the other. I don't want to create two separate models to encapsulate this data.

Is it possible for me to setup Dapper to map both of these result sets to the same object?

For example, sp_GetSomeDataByQtr returns a result set like:

Qtr int,
Lines int,
Balance money

While, sp_GetSomeDataByMonth returns a result set like:

Mo int,
Lines int,
Balance money

I want to encapsulate both of these into an object like

public class MyData {
    public int Term { get; set; } //This would be Qtr OR Mo depending on which SP is called.
    public int Lines { get; set; }
    public decimal Balance { get; set; }
}

So, then with Dapper:

MyData data = connection.Query<MyData>("sp_GetSomeDataByQtr", ...).FirstOrDefault();

or:

MyData data = connection.Query<MyData>("sp_GetSomeDataByMonth", ...).FirstOrDefault();

We can't change the Stored Procedures directly because there are legacy applications also retrieving data from them, and depending on these column names.

Is there a way to do this with Dapper? I know you can setup custom type maps, but I'm not sure how to do it when trying to map multiple columns names to a single property of an object.

score 1 · Accepted Answer · answered Sep 25 '14 at 14:52

1

What about something along the lines of below? Dapper will populate Mo or Lines depending on the stored procedure but you can always read the value from the Term property.

public class MyData
{
    public int? Mo { get; set; }
    public int? Lines { get; set; }
    public decimal Balance { get; set; }

    public int Term
    {
        get
        {
            if (Mo == null && Lines != null)
            {
                return Lines.Value;
            }
            if (Mo != null && Lines == null)
            {
                return Mo.Value;
            }

            return default(int);
        }
    }
}

answered Sep 25 '14 at 14:52

Ryan Rodemoyer

5,548
12
44
54

That looks like it could get me moving forward in the short term, but I'd like to see a solution that deals with the type map if possible. Thanks – crush Sep 25 '14 at 15:35
Do you know if Dapper finds properties via reflection matching the column name, or does it look only for fields? If it matches properties, then I could make two setter only properties that set the value of `Term`. Still not the ideal solution because it requires me to change my POCO/DataContract. – crush Sep 25 '14 at 16:00
I used this solution to implement a custom type mapper for Dapper but I think it's an on/off solution. I'm not sure you add the ColumnName attribute twice to the same property but give it a shot and see how it works for you. http://stackoverflow.com/a/12615036/1444511 – Ryan Rodemoyer Sep 25 '14 at 16:06
I got here after independently trying as multiple ColumnName attributes to the same property. It does not compile. Which means for me not only do I have to put the attributes on the the class--which I prefer to be fully abstracted from db--but I am tied to one particular mapping. Which means the ColumnName mapper probably isn't so great after all. – Karl Kieninger Apr 30 '15 at 16:59

Map multiple columns to single property with Dapper

1 Answers1