最近项目中用到Dapper,其中有个多表是查询,写了个测试
public class Book { public int ID; public string Name; public string Description; public BookComment bookComment; public BookPic bookPic; }
public class BookPic { public int ID; public int BookID; public string Name; public string PicDesc; }
public class BookComment { public int ID; public int BookId; public string Name; public string Comment; }
查询用到splitOn,匹配是从结果列最后往前找,如下找到BookID到最后列映射到C表中,再往前找到Name,再映射到B表中,再前面的列则映射A表中,查找分隔匹配是不区分大小写的。
如果是跨库查询,配置的user需要有open其它库的权限。
public List<Book> GetBookList() { List<Book> bList = null; try { using (var t = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString)) { bList= t.Query<Book, BookComment, BookPic, Book>(@"select a.ID,a.Name,a.Description, b.Name,b.ID,b.BookId,b.Comment, c.BookID,c.ID,c.Name,c.PicDesc from [dbo].[Book] a inner join [dbo].[BookComment] b on a.id=b.bookid inner join [dbo].[BookPic] c on a.id =c.bookid", (book, bookcomment, bookpic) => { book.bookComment = bookcomment; book.bookPic = bookpic; return book; }, splitOn: "Name,BookID" ).ToList(); } } catch (Exception ex) { nlog.Error(ex.ToString()); } return bList; }