使用Dapper的内部联接获取数据 [英] Get data using inner join from Dapper

问题描述

我正在一个项目上进行DB操作，并正在开发WinForms App C#，并且我正在使用Dapper从DB中获取数据，但是我陷入了需要使用内部联接来检索数据的情况.例如.我有两个表格Authors和Book，如下所示:

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

现在在SQL Server中，我可以使用以下查询轻松地从中获取数据:

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

但是我不知道如何使用dapper多重映射来做到这一点，我还看到了类似 ，但无法理解其工作原理和拆分方式.如果我能在课堂设计中获得相同的解决方案，那将是很棒的. 谢谢.

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.

这是我想要的输出:结果集

推荐答案

使用链接的结果，您可以执行以下操作:

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");

splitOn参数应理解为以下内容:如果所有列按查询顺序从左到右排列，则左侧的值属于第一类，右侧的值属于到第二堂课.

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.

这篇关于使用Dapper的内部联接获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！