首页 > 移动平台 > 详细

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

时间:2018-01-14 21:02:14      阅读:100      评论:0      收藏:0      [点我收藏+]

标签:tor   fin   creat   conn   new   get   roc   tro   获取   


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


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









class Group { public string ID{get;set;} public string Name{get;set;} public List<Rights> RightsList{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(); } }




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

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

标签:tor   fin   creat   conn   new   get   roc   tro   获取   

原文:https://www.cnblogs.com/yyzyou/p/8284118.html

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号