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

Question

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

c# dapper orm sql-server

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?

JamesFaix

View more on Stack Overflow

David L · Accepted Answer · 7/6/2016 10:10:42 PM

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.

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

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator