Dapper Multi-map next level

问题:

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.

回答1:

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.