dapper 多對多查詢對象和對象列表

標籤：span   body   mysql   factor   factory   nec   utf8   serve   title   

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 [email protected]";                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 多對多查詢對象和對象列表