[.NET] Dapper - Query multiple-mapping

 .NET    Dapper  

▌Introduction

---

In this article, we will learn how to use Dapper to make a query with multiple joins and map result to multiple objects.

▌Environment

---

▋Dapper 1.50.2

▌Implement

---

▋Tables

The relations of the 3 tables are as following.

And I create 3 DAOs for them.

 

Notice that SmRoleMenu contains SmRole and SmMenu objects inside.

public class SmRoleMenu {         public int SmRoleId { get; set; }         public int SmMenuId { get; set; }         public SmRole Role { get; set; }         public SmMenu Menu { get; set; } }

▋Query with multiple-mapping

using (var cn = SqlConnectionFactory.Create()) {                 string sql = @" select a.SmMenuId, a.SmRoleId, b.SmRoleId, b.Name, b.IsEnabled, c.* from SmRoleMenus a left join SmRoles b on a.SmRoleId=b.SmRoleId left join SmMenus c on a.SmMenuId=c.SmMenuId order by a.SmRoleId ";                 var roleMenus = cn.Query<SmRoleMenu, SmRole, SmMenu,SmRoleMenu>(                         sql,                         (smRoleMenu, smRole, smMenu) =>                         {                             smRoleMenu.Role = smRole;                             smRoleMenu.Menu = smMenu;                             return smRoleMenu;                         }, splitOn: "SmRoleId,SmMenuId");                 return roleMenus; }

So how to determine the splitOn columns?

Since our SQL is

select a.SmMenuId, a.SmRoleId, b.SmRoleId, b.Name, b.IsEnabled, c.* //c. SmMenuId, c. Name, … From …

SmRoleId and SmMenuId will be the splitOn columns in this sample SQL.

▋Result

 

▌Reference

---

▋Dapper Tutorials & Examples - C# Micro ORM