Correct Use of Multimapping in Dapper

Correct use of multimapping in Dapper

I just ran a test that works fine:

var sql = "select cast(1 as decimal) ProductId, 'a' ProductName, 'x' AccountOpened, cast(1 as decimal) CustomerId, 'name' CustomerName";

var item = connection.Query<ProductItem, Customer, ProductItem>(sql,
    (p, c) => { p.Customer = c; return p; }, splitOn: "CustomerId").First();

item.Customer.CustomerId.IsEqualTo(1);

The splitOn param needs to be specified as the split point, it defaults to Id. If there are multiple split points, you will need to add them in a comma delimited list.

Say your recordset looks like this:

ProductID | ProductName | AccountOpened | CustomerId | CustomerName 
---------------------------------------   -------------------------

Dapper needs to know how to split the columns in this order into 2 objects. A cursory look shows that the Customer starts at the column CustomerId, hence splitOn: CustomerId.

There is a big caveat here, if the column ordering in the underlying table is flipped for some reason:

ProductID | ProductName | AccountOpened | CustomerName | CustomerId  
---------------------------------------   -------------------------

splitOn: CustomerId will result in a null customer name.

If you specify CustomerId,CustomerName as split points, dapper assumes you are trying to split up the result set into 3 objects. First starts at the beginning, second starts at CustomerId, third at CustomerName.

Dapper Multi Mapping

Looks like you just need to remove the 'Code' from splitOn: "Id, Code". Your query renames it to "Id".

Dapper also uses "Id" as a default, so no need to specify.

Dapper is able to split the returned row by making an assumption that
your Id columns are named Id or id. If your primary key is different
or you would like to split the row at a point other than Id, use the
optional splitOn parameter.

Below is a quick test to verify:

using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
{
    var result = conn.Query<Species, SpeciesCategory, WetlandIndicator, Species>(
        "select Id = 11, Name = 'Foo', Id = 22, Name = 'Bar', Id = 33, Designation = 'House Cat' ", 
        (species, speciesCategory, wetlandIndicator) =>
    {
        species.Category = speciesCategory;
        species.WetlandIndicator = wetlandIndicator;
        return species;
    }).First();

    Assert.That(result.Id, Is.EqualTo(11));

    Assert.That(result.Category.Id, Is.EqualTo(22));
    Assert.That(result.Category.Name, Is.EqualTo("Bar"));

    Assert.That(result.WetlandIndicator.Id, Is.EqualTo(33));
    Assert.That(result.WetlandIndicator.Designation, Is.EqualTo("House Cat"));
}

Update to demo split by different fields and types

public class Species
{
    public int Id { get; set; }
    public string Name { get; set; }
    public SpeciesCategory Category { get; set; }
    public WetlandIndicator WetlandIndicator { get; set; }
}

public class SpeciesCategory
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class WetlandIndicator
{
    public string Code { get; set; }
    public string Designation { get; set; }
    public bool Status { get; set; }
}

using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
{
    var result = conn.Query<Species, SpeciesCategory, WetlandIndicator, Species>(
        "select Id = 11, Name = 'Foo', Id = 22, Name = 'Bar', Code = 'X', Designation = 'House Cat' ", 
        (species, speciesCategory, wetlandIndicator) =>
    {
        species.Category = speciesCategory;
        species.WetlandIndicator = wetlandIndicator;
        return species;
    }, splitOn: "Id, Code").First();

    Assert.That(result.Id, Is.EqualTo(11));

    Assert.That(result.Category.Id, Is.EqualTo(22));
    Assert.That(result.Category.Name, Is.EqualTo("Bar"));

    Assert.That(result.WetlandIndicator.Code, Is.EqualTo("X"));
    Assert.That(result.WetlandIndicator.Designation, Is.EqualTo("House Cat"));
}

I don't understand Dapper's mapping, multimapping and QueryMultiple

I think that your main problem with the Dapper querying of joined table queries is thinking that the second argument in the list is always the "param" argument. Consider the following code:

var productsWithoutCategories = conn.Query<Product>(
    "SELECT * FROM Products WHERE ProductName LIKE @nameStartsWith + '%'",
    new { nameStartsWith = "a" }
);

Here, there are two arguments "sql" and "param" - if we used named arguments then the code would look like this:

var productsWithoutCategories = conn.Query<Product>(
    sql: "SELECT * FROM Products WHERE ProductName LIKE @nameStartsWith + '%'",
    param: new { nameStartsWith = "a" }
);

In your example, you have

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});

The second argument there is actually an argument called "map" which tells Dapper how to combine entities for cases where you've joined two tables in your SQL query. If we used named arguments then it would look like this:

var data = connection.Query<Post, User, Post>(
    sql: sql,
    map: (post, user) => { post.Owner = user; return post;}
);

I'm going to use the class NORTHWND database in a complete example. Say we have the classes

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public bool Discontinued { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
}

and we want to build a list of Products, with the nested Category type populated, we'd do the following:

using (var conn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
{
    var productsWithCategories = conn.Query<Product, Category, Product>(
        "SELECT * FROM Products INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID,
        map: (product, category) =>
        {
            product.Category = category;
            return product;
        },
        splitOn: "CategoryID"
    );
}

This goes through all the rows of JOIN'd Product and Category data and generates a list of unique Products but can't be sure how to combine the Category data with it, so it requires a "map" function which takes a Product instance and a Category instance and which must return a Product instance which has the Category data combined with it. In this example, it's easy - we just need to set the Category property on the Product instance to the Category instance.

Note that I've had to specify a "splitOn" value. Dapper presumes that the key columns of tables will simply be called "Id" and, if they are, then it can deal with joins on those columns automatically. However, in this case, we're joining on a column called "CategoryID" and so we have to tell Dapper to split the data back up (into Products and into Categories) according to that column name.

If we also wanted to specify "param" object to filter down the results, then we could do something like the following:

var productsWithCategories = conn.Query<Product, Category, Product>(
    "SELECT * FROM Products INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID WHERE ProductName LIKE @nameStartsWith + '%'",
    map: (product, category) =>
    {
        product.Category = category;
        return product;
    },
    param: new { nameStartsWith = "a" },
    splitOn: "CategoryID"
);

To answer your final question, QueryMultiple simply executes multiple queries in one go and then allows you to read them back separately. For example, instead of doing this (with two separate queries):

using (var conn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
{
    var categories = conn.Query("SELECT * FROM Categories");
    var products = conn.Query("SELECT * FROM Products");
}

You could specify a single SQL statement that includes both queries in one batch, but you would then need to read them separately out of the combined result set that is returned from QueryMultiple:

using (var conn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
{
    var combinedResults = conn.QueryMultiple("SELECT * FROM Categories; SELECT * FROM Products");
    var categories = combinedResults.Read<Category>();
    var products = combinedResults.Read<Product>();
}

I think that the other examples I've seen of QueryMultiple are a little confusing as they are often returning single values from each query, rather than full sets of rows (which is what is more often seen in simple Query calls). So hopefully the above clears that up for you.

Note: I haven't covered your question about the [Table] attribute - if you're still having problems after you've tried this out then I would suggest creating a new question for it. Dapper uses the "splitOn" value to decide when the columns for one entity end and the next start (in the JOIN example above there were fields for Product and then fields for Category). If you renamed the Category class to something else then the query will still work, Dapper doesn't rely upon the table name in this case - so hopefully you won't need the [Table] at all.

Multimapping in Dapper Without Custom SQL

Don't do this. Don't even think this way! Databases are long lasting and normalized. Objects are perishable and frequently denormalized, and transitioning between the two is something to do thoughtfully, when you're writing your SQL. This is really not a step to automate. Long, painful experience has convinced many of us that database abstractions (tables and joins) should not just be sucked into (or generated out of) code. If you're not yet convinced, then use an established ORM.

If, on the other hand, you absolutely want to be in control of your SQL, but its the "embedding" in string literals in C# that bugs you, then I couldn't agree more. Can I suggest QueryFirst, a visual studio extension that generates the C# wrapper for your queries. Your SQL stays in a real SQL file, syntax validated, DB references checked, and at each save, QueryFirst generates a wrapper class with Execute() methods, and a POCO for the results.

By multi-mapping, I presume you want to fill a graph of nested objects. A nice way to do this is to use one QueryFirst .sql per class in your graph, then in the partial class of the parent, add a List of children. (QueryFirst generated POCOs are split across 2 partial classes, you control one of them, the tool generates the other.)

So, for a graph of Customers and their orders...
In the parent sql

select * from customers where name like @custName

The child sql

select * from orders where customerId = @customerId

In the parent partial class, for eager loading...

    public List<Orders> orders;
    public void OnLoad()
    {
        orders = new getOrders().Execute(customerId); // property of the parent POCO
    }

or for lazy loading...

    private List<Orders> _orders;
    public List<Orders> orders
    {
        get
        {                
            return _orders ?? _orders = new GetOrders().Execute(customerId);                
        }
    }

5 lines of code, not counting brackets, and you have a nested graph, lazy loaded or eager loaded as you prefer, the interface discoverable in code (intellisense for the input parameter and result). Their might be hundreds of columns in those tables, whose names you will never need to re-type, and whose datatypes are going to flow transparently into your C#.

Clean separation of responsibilities. Total control. Disclaimer : I wrote QueryFirst :-)

---

Related Topics