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

Question

Title says it all, I'm trying to use it but I don't understand it. It's possible that the problem is a lack of knowledge due to that I'm an amateur, but I've read a dozen questions about this thing and googled for three days, and I still don't understand it.

I have SO many questions that I'm not sure that I should write it all in only one Question, or even if someone would read it all. If someone have other solution or think I should split it in different questions... well, I'm open to suggestions.

I was going to write an example, but again I read dozen of examples for days and didn't help me.

I just can't make my mind to understand how work something like the example at github:

var sql = 
@"select * from #Posts p 
left join #Users u on u.Id = p.OwnerId 
Order by p.Id";

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

So, Post have a property of type User and that property is called Owner, right? Something like:

public class Post
{
    ...
    public User Owner { get; set;}
}

Therefore Query<Post, User, Post> will return a Post instance with all the properties and what not, AND will create a User instance and assign it to Post.Owner property? How would simple parameters be added to that query, for example if someone wanted to pass the id as a int parameter like ...WHERE Id = @Id", new {Id = id}, where should the parameter be added given that the parameter right now is (post, user) => { post.Owner = user; return post;}? The parameter always refer to the types given, you can only use the simple typical parameters for the dynamic query, both can be used simultaneously? How?

Also, how does it to differentiate what DB field goes to what object? It makes something like class name=DB table name? What happens if the classes don't have the same name as the DB table and I want to use the [Table] attribte, will it work or the attribute is only for Dapper.Contrib.Extensionsmethods? Would it work with objects that share the same DB table?

Regarding same table for different objects question, f.i. lets say I have a Person object that have a BankAccount object:

public class Person
{
    ...
    public BankAccount Account {get; set;}
    ...
}

public class BankAccount
{
    private string _Account;

    public string Account
    {
        get { return _Account; }
        set
        {
            if(!CheckIfIBANIsCorrect(value))
                throw new Exception();

            _Account = value;
        }
    }

    private bool CheckIfIBANIsCorrect(string IBAN)
    {
        //...
        //Check it
    }
}

I could store the string account at the same table than Person, since every person would have a single account referred by the person's Id. How should I map something like that? Is there even a way, should I simply load the result in a dynamic object and then create all the objects, will Query create the rest of the Person object and I should bother to create the nested object myself?

And by the way, how is splitOnsupposedly be used in all this? I understand that it should split the result into various "groups" so you can split the results by Ids f.i. and take what you need, but I don't understand how should I retrieve the info from the different "groups", and how it return the different "groups", lists, enumerables, what?.

QueryMultiple is other thing that is FAR beyond my understanding regardles how much questions and answers I read. You know... how the * does that .Read thing work? All I read here or googling assumes that Read is some sort of automagic thing that can miracly discern between objects. Again, do it divide results by class names so I just have to be sure every object have the correct table name? And again what happens with [Table] attribute in this case?

I think the problem I'm having is that I can't find(I suppose it doesn't exist) a single web page that describes it all(the examples at GitHub are very scarce), and I only still finding answers to concrete cases that doesn't answer exactly what I'm trying to understand but only that concrete cases, which are confusing me more and more while I read them, since everyone seems to use a bunch of different methods without explaining WHY or HOW.

Answer 1

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.