How with Dapper to get data from a INNER JOIN sql?

c# dapper micro-orm orm

Question

I'm caught in a circumstance where I need to get data using an inner join while working on DB operations for one project and constructing a WinForms App in C# using Dapper. Eg. I've got two tables with authors and books in them.

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; }
}

I can simply get the data from SQL Server now by using the following query:

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

However, I am unsure of how to use Dapper Multi Mapping to do this. I have seen articles like This but was unable to grasp how splitting worked. If I can find the same answers for my class designs, it would be fantastic. Thank.

I want the following output: ResultSet

1
2
4/20/2018 7:29:54 AM

Accepted Answer

Using the outcome you've linked, you can simply 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");

A dynamic type is another option.

It's a different matter if you want a library of books that includes the authors. Then you would act as follows (selecting * as an 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");

It is recommended to think of the splitOn parameter as The values on the left belong to the first class, and the values on the right belong to the second class if all the columns are organized from left to right in the order in the query..

2
4/20/2018 1:23:10 PM


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow