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?
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:
Value
property, nor does it check that there is actually a value present before calling .ToString()
on it.