Using Dapper, how to map a string column to a generic list?
dapper dapper-extensions sql-server string-split
Question
I have the following model:
public class Model {
public string Name { get; set; }
public List<int> Numbers { get; set; }
}
And an SQL query that returns the following dataset containing two nvarchar columns:
Name | Numbers
'foo' | '1,2,3,4'
'bar' | '4,17'
Is there a simple way to auto-assign the results of the query to a List<Model> using Dapper?
I know I could use multi-mapping and make the splitting myself in C# code, but I would rather get a simpler solution.
Accepted Answer
I'm not sure if you can call this "simpler", but something like this is an option:
public class Result
{
public string Name { get; set; }
public List<int> Numbers { get; set; }
}
public class DapperTests
{
[Test]
public void Test()
{
var conn = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=mydb");
conn.Open();
var result = conn.Query<string, string, Result>(
"select Name = 'Foo', Numbers = '1,2,3' union all select Name = 'Bar', Numbers = '4,5,6'", (a, b) => new Result
{
Name = a,
Numbers = b.Split(',').Select(Int32.Parse).ToList()
}, splitOn: "*").ToList();
Assert.That(result.Count, Is.EqualTo(2));
Assert.That(result.FirstOrDefault(x => x.Name == "Foo").Numbers.Count, Is.GreaterThan(0));
Assert.That(result.FirstOrDefault(x => x.Name == "Bar").Numbers.Count, Is.GreaterThan(0));
}
}
Popular Answer
An alternative option with multimapping... pretty ugly
public class Result
{
public string Name { get; set; }
public List<int> NumberList { get; set; }
public string Numbers { set { NumberList = value.Split(',').Select(Int32.Parse).ToList(); } }
}
public class DapperTests
{
[Test]
public void Test()
{
var conn = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=mydb");
conn.Open();
var sql = @"
select Name = 'Foo', Numbers = '1,2,3';
select Name = 'Bar', Numbers = '4,5,6';";
var expectedResults = 2;
var results = new List<Result>();
using (var multi = conn.QueryMultiple(sql))
{
for (int i = 0; i < expectedResults; i++)
{
results.Add(multi.Read<Result>().Single());
}
}
Assert.That(results.Count, Is.EqualTo(2));
Assert.That(results.FirstOrDefault(x => x.Name == "Foo").NumberList.Count, Is.GreaterThan(0));
Assert.That(results.FirstOrDefault(x => x.Name == "Bar").NumberList.Count, Is.GreaterThan(0));
}
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow