Dapper - Map result set with unknown number of columns to object with array property

Question

I am using Dapper to map objects coming out of a SQL Server database to C# classes. One of the stored procedures I need to pull results from can have a variable number of columns depending on the context. This result set will always have a primary key column, and then 0 to 30 additional columns that will all be of the same data type.

The result set columns are basically like this:

CustomerID | Email1 | Email2 | Email3 | ...

Where the number of Email* columns depends on the highest number of email addresses on file for a customer in the query.

The natural way to capture this in C# is with a class like this:

class Customer {
    int ID { get; set; }
    string[] EmailAddresses { get; set; }
}

Is there a way to use Dapper to map my result set to an object like this?

David L · Accepted Answer · 2016-07-06 22:10:42Z

6

It is possible if you do a bit of magic via dynamic.

var user = connection.Query("spGetUser", commandType: CommandType.StoredProcedure)
        .Select(x =>
        {
            var result = new Customer { ID = x.Id };
            foreach (var element in x)
            {
                if (element.Key.Contains("Email"))
                    result.EmailAddresses.Add(element.Value.ToString());
            }
            return result;
        }).FirstOrDefault();

public class Customer
{
    public int ID { get; set; }
    public List<string> EmailAddresses { get; set; } = new List<string>();
}

A few key points:

The array is changed to a list so that we can easily and dynamically add data to it.
Dynamic will always have a small performance hit in comparison to deserializing directly to a strong type.
This does not check the type of the data held in the Value property, nor does it check that there is actually a value present before calling .ToString() on it.

answered Jul 6, 2016 at 22:10

David L

34.1k8 gold badges70 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JamesFaix Over a year ago

Thank you. I don't think the dynamic and List caveats will cause any issues in my scenario. I will try this at work tomorrow.

David L Over a year ago

The List will be perfectly performant in comparison to an array. The hit from dynamic is small but at least worth noting.

JamesFaix Over a year ago

I think the time saved not manually manipulating DataTables will more than make up for it.

David L Over a year ago

Absolutely :). it's a wonderful tool. The flexibility and power is astounding.

David L Over a year ago

Well, in that case, welcome to the other side :). It's a fantastic place to live.

|

Collectives™ on Stack Overflow

Dapper - Map result set with unknown number of columns to object with array property

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related