Get data using inner join from Dapper

问题:

I am working on DB operations on one project and developing WinForms App C# and I am using Dapper to get data out of DB, I am stuck at situation where I need to retrieve data using inner join. Eg. I've two tables Authors and Book as Follow :

public class Authors
{
    public int AuthorId { get; set; }
    public string AuthorName { get; set; }
}
public class Book
{
    public int BookId { get; set; }
    public string AuthorId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int Price { get; set; }
}

Now in SQL Server I can easily get the data out of it using following query:

select B.Title,b.Description,b.Price,A.AuthorName from author A inner join book B on A.AuthorId = B.Authorid

But I dont know how to do this with dapper multi mapping, I also saw articles like This but could not understand how it works and splitting. I will be great if i can get same solutions with my class designs. Thank.

This is ouput I want : ResultSet

回答1:

With the result you have linked you can just do this:

public class Result
{
    public string Title { get; set; }
    public string Description { get; set; }
    public int Price { get; set; }
    public string AuthorName { get; set; }
}

connection.Query<Result>("SELECT B.Title,B.Description,B.Price,A.AuthorName FROM Author A INNER JOIN Book B on A.AuthorId = B.Authorid");

Or you can use a dynamic type.

If you want to have a collection of Books with their Authors it's another story. Then you would do like this (selecting * for the sake of the example):

var sql = "SELECT * FROM Author A INNER JOIN Book B on A.AuthorId = B.Authorid";
var result = connection.Query<Book, Author, Book>(sql,
(b, a) => { b.Author = a; return b; }, splitOn: "AuthorId");

The splitOn parameter should be understood something like this: If all columns are arranged from left to right in the order in the query, then the values on the left belong to the first class and the values on the right belong to the second class.