How to name columns to support multiple display in Dapper?

Question

var sql = @"SELECT a.id AS `Id`, a.thing AS `Name`, b.id AS `CategoryId`, b.something AS `CategoryName` FROM .."; var products = connection.Query<Product, Category, Product>(sql, (product, category) => { product.Category = category; return product; }, splitOn: "CategoryId"); foreach(var p in products) { System.Diagnostics.Debug.WriteLine("{0} (#{1}) in {2} (#{3})", p.Name, p.Id, p.Category.Name, p.Category.Id); } 

Results in:

 'First (#1) in (#0)' 'Second (#2) in (#0)' 

CategoryId and CategoryName are relevant since the following

 var products = connection.Query(sql).Select<dynamic, Product>(x => new Product { Id = x.Id, Name = x.Name, Category = new Category { Id = x.CategoryId, Name = x.CategoryName } }); 

Results in:

 'First (#1) in My Category (#10)' 'Second (#2) in My Category (#10)' 

I am connecting to a MySQL database if this has anything to do with it.

Answer 1

The easiest way is to name all Id (case insensitive, so just a.id and b.id are fine); then you can use:

  public void TestMultiMapWithSplit() { var sql = @"select 1 as Id, 'abc' as Name, 2 as Id, 'def' as Name"; var product = connection.Query<Product, Category, Product>(sql, (prod, cat) => { prod.Category = cat; return prod; }).First(); // assertions product.Id.IsEqualTo(1); product.Name.IsEqualTo("abc"); product.Category.Id.IsEqualTo(2); product.Category.Name.IsEqualTo("def"); } 

If you cannot do this, there is an optional splitOn ( string ) parameter that processes the list of columns separated by commas for processing in split.