dapper mysql 多参数查询_dapper 多对多查询对象和对象列表

角色：

class Group

{

public string ID{get;set;}

public string Name{get;set;}

public List 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 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();

var list = conn.Query(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 GroupList { get; set; }

[NoWrite]

public List 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(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 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();

conn.Query(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 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();

User lookup = null;

var result = conn.Query(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;

}

}