9

I'm using multiple mapping for a current query and now I need to map another object on the initial query.

For example:

public class Part { public int Id { get; set; } public string Name { get; set; } public Address Address { get; set; } } public class Address { public int Id { get; set; } public string Street { get; set; } public SiteOu Ou { get; set; } } public class SiteOu public int Id { get; set; } public string Name { get; set; } }

Dapper: 

 connection.Query<Part, Address, Part>(sql, (part, address) => { part.Address = address; });

How do I get the Address class to have the SiteOu information?

This example isn't what I'm actually doing because I've actually got 

Query<T1,T2,T3,T4,T5,TResult>();

I'm doing 1 select and 5 joins in my query. So hopefully I don't need more overloads of Query.

Improve this question

1 Answer 1

Reset to default
12

Dapper allows you to map a single row to multiple objects, so you can just map SiteOu as part of the same query.

[Test] public void TestSplitOn() { var conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=db"); conn.Open(); const string sql = "select Id = 1, Name = 'My Part', " + "Id = 2, Street = 'My Street', " + "Id = 3, Name = 'My Site'"; var result = conn.Query<Part, Address, SiteOu, Part>(sql, (part, address, siteOu) => { part.Address = address; address.Ou = siteOu; return part; }, commandType: CommandType.Text ).FirstOrDefault(); Assert.That(result, Is.Not.Null); Assert.That(result.Address, Is.Not.Null); Assert.That(result.Address.Ou, Is.Not.Null); }

Important Note: Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.

If you have more that 5 types to map, another out of the box option is to use QueryMultiple extension. Here is an example from the Dapper docs.

var sql = @" select * from Customers where CustomerId = @id select * from Orders where CustomerId = @id select * from Returns where CustomerId = @id"; using (var multi = connection.QueryMultiple(sql, new {id=selectedId})) { var customer = multi.Read<Customer>().Single(); var orders = multi.Read<Order>().ToList(); var returns = multi.Read<Return>().ToList(); ... }

Also check out this thread.

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

7 Comments

I was afraid that was the answer. The problem is I have 5 joins and, so the Query<T1,T2,T3,T4,T5,TResult> override is one short for my scenario. The example I have as I mentioned was a simplified version of what I really have. My part object really has a few more properties, vendor, model, etc...
Yeah, unfortunately Dapper only supports up-to 5 types. Another out of the box option would be to use QueryMultiple extension. I added an example from Dapper’s docs to the answer.
@KyleRogers have you updated dapper to the current version? I see Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> ?
@MarcGravell I must not have. Thank you very much!
How can you handle a case where you have two SiteOu properties in an Address?
|