dapper 多对多查询对象和对象列表

角色：

class Group
{
public string ID{get;set;}
public string Name{get;set;}
public List<Rights> RightsList{get;set;}
}

权限：

class Rights
{
public string ID{get;set;}
public string Name{get;set;}
}

角色-权限关系表：

CREATE TABLE `t_group_right` (
`rightId` varchar(50) NOT NULL,
`groupId` varchar(50) NOT NULL,
KEY `FK_group_rights_id` (`rightId`),
KEY `FK_rights_group_id` (`groupId`),
CONSTRAINT `FK_group_rights_id` FOREIGN KEY (`rightId`) REFERENCES `t_rights` (`id`),
CONSTRAINT `FK_rights_group_id` FOREIGN KEY (`groupId`) REFERENCES `t_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询角色列表:

public List<Group> GetAll()
{
string sql = "SELECT a.*,c.* FROM t_group a left join t_group_right b on a.id = b.groupid left join t_rights c on c.id=b.rightid ";
using (MySqlConnection conn = DapperFactory.CrateMySqlConnection())
{
var lookup = new Dictionary<string, Group>();
var list = conn.Query<Group, Rights, Group>(sql, (g, r) =>
{
Group tmp;
if (!lookup.TryGetValue(g.ID, out tmp))
{
tmp = g;
lookup.Add(g.ID, tmp);
}

tmp.RightsList.Add(r);

return g;
}
, splitOn: "id").ToList();
return lookup.Values.ToList();
}

}

splitOn参数：用来指定列为分隔列，之前的列为前一对象，之后的列为后一对象。
lookup 用来保存中间处理结果，可以理解为将结果归组出Group对象，并为其RightsList添加内容，
注意：lookup中添加的是临时定义的Group对象，并在循环中将此对象从lookup中取出，为其RightsList添加Rights

Operator对象：

class Operator{
public string ID{get;set;}
public string Name{get;set;}
[NoWrite]
public List<Group> GroupList { get; set; }
[NoWrite]
public List<Rights> RightsList { get; set; }
}

查询一个Operator对象：

public Operator Get(string id)
{
using (MySqlConnection conn = DapperFactory.CrateMySqlConnection())
{
string sql = "SELECT a.*,c.*,e.* FROM t_operator a left join t_operator_group b on a.id = b.operatorid left join t_group c on c.id=b.groupid " +
"left join t_operator_rights d on a.id = d.operatorid left join t_rights e on e.id = d.rightid where a.id=@id";
Operator lookup = null;
conn.Query<Operator, Group, Rights, Operator>(sql, (o, g, r) =>
{
if (lookup == null)
{
lookup = o;
}
Group tmp = lookup.GroupList.Find(f => f.ID == g.ID);
if (tmp == null)
{
tmp = g;
lookup.GroupList.Add(tmp);
}
tmp.RightsList.Add(r);
lookup.RightsList.Add(r);
return o;
},
param: new { id = id },
splitOn: "id");
return lookup;
}

}

获取Operator列表：

public List<Operator> GetAll()
{
using (MySqlConnection conn = DapperFactory.CrateMySqlConnection())
{
string sql = "SELECT a.*,c.*,e.* FROM t_operator a left join t_operator_group b on a.id = b.operatorid left join t_group c on c.id=b.groupid " +
"left join t_operator_rights d on a.id = d.operatorid left join t_rights e on e.id = d.rightid";
var lookup = new Dictionary<string,Operator>();
conn.Query<Operator, Group, Rights, Operator>(sql, (o, g, r) =>
{
Operator tmp;
if (!lookup.TryGetValue(o.ID,out tmp))
{
tmp = o;
lookup.Add(o.ID,tmp);
}
Group tmpG = tmp.GroupList.Find(f => f.ID == g.ID);
if (tmpG == null)
{
tmpG = g;
tmp.GroupList.Add(tmpG);
}
//角色权限列表
Rights tmpR = tmpG.RightsList.Find(f => f.ID == r.ID);
if (tmpR == null)
{
tmpG.RightsList.Add(r);
}
//用户权限列表
tmpR = tmp.RightsList.Find(f => f.ID == r.ID);
if (tmpR == null)
{
tmp.RightsList.Add(r);
}

return o;
},
splitOn: "id");
return lookup.Values.ToList();
}
}
————————————————
版权声明：本文为CSDN博主「wyljz」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wyljz/article/details/68926745

 

 

以下是自己写的另一种形式：

public List<User> GetUserAssemble(int id)
        {
            using (IDbConnection conn = GetSqlConnection())
            {
                string sql = @"select u.Id,u.Name,u.Password,r.Id,r.RoleName,m.Id,m.MenuName from tb_User u
                            left join tb_UserRole ur on u.id = ur.UserId
                            left join tb_Role r on ur.RoleId = r.Id
                            left join tb_RoleMenu rm on r.Id = rm.RoleId
                            left join tb_Menu m on rm.MenuId = m.Id 
                            where u.Id=@Id";
                //var lookup = new Dictionary<int, User>();
                User lookup = null;
                var result = conn.Query<User, Role, Menu, User>(sql, (user, role, menu) =>
                 {
                     //if (!lookup.TryGetValue(user.Id, out User tmpUser))
                     //{
                     //    tmpUser = user;
                     //    lookup.Add(user.Id, tmpUser);
                     //}
                     //if (role == null)
                     //    return user;
                     //Role tmpRole = tmpUser.RoleList.Find(_ => _.Id == role.Id);
                     //if (tmpRole == null)
                     //{
                     //    tmpRole = role;
                     //    tmpUser.RoleList.Add(role);
                     //}
                     //if (menu == null)
                     //    return user;
                     //Menu tmpMenu = tmpRole.MenuList.Find(_ => _.Id == menu.Id);
                     //if (tmpMenu == null)
                     //{
                     //    tmpRole.MenuList.Add(menu);
                     //}
                     // return user;
                     if (lookup == null || lookup.Id != user.Id)
                     {
                         lookup = user;
                     }
                     if (role != null)
                     {
                         Role tmpRole = lookup.RoleList.Find(_ => _.Id == role.Id);
                         if (tmpRole == null)
                         {
                             tmpRole = role;
                             lookup.RoleList.Add(role);
                         }
                         if (menu != null)
                         {
                             Menu tmpMenu = tmpRole.MenuList.Find(_ => _.Id == menu.Id);
                             if (tmpMenu == null)
                             {
                                 tmpRole.MenuList.Add(menu);
                             }
                         }
                     }
                     return lookup;
                 }, splitOn: "Id,Id", param: new { Id = id }).Distinct().ToList();
                return result;
            }
        }