Dapper多表关联

 实体类

class Users
    {
        public int ID;
        public string Name;
        public string ChineseName;
        public int DeptID;

        public Depts Dept; //非数据库字段
    }

    class Depts
    {
        public Depts()
        {
            this.DeptUsers = new List<Users>(); //一定要加上这个初始化，不然 查询2 哪里会报错，DeptUsers为null
        }

        public int ID;
        public string Name;
        public int ParentID;

        public virtual IList<Users> DeptUsers { get; private set; } //非数据库字段
}

using (var cn = conn())
            {
                //查询1  以User为主查部门
                var us = cn.Query<Users, Depts, Users>("SELECT a.ID,a.Name,a.ChineseName,a.DeptID,b.ID,b.Name,b.ParentID from Users a join depts b on a.deptid = b.id",
                    (user, dept) => { user.Dept = dept; return user; }, splitOn: "ID");
                Console.WriteLine(JN(us));
                Console.WriteLine(Environment.NewLine);
  
结果：

[{"ID":3,"Name":"user0","ChineseName":"童光喜","DeptID":2,"Dept":{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[]}},{"ID":4,"Name":"user2","ChineseName":"方原柏","DeptID":2,"Dept":{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[]}},{"ID":5,"Name":"user4","ChineseName":"祝春亚","DeptID":2,"Dept":{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[]}}] 

/////////////////////////以下是以部门为主查部门用户//////////////////////////////////////////////////////////////////////////////
               //查询2  结果和 查询4相同
                var us1 = cn.Query<Users, Depts, Depts>("SELECT a.ID,a.Name,a.ChineseName,a.DeptID,b.ID,b.Name,b.ParentID from Users a join depts b on a.deptid = b.id",
                  (user, dept) => { dept.DeptUsers.Add(user); return dept; }, splitOn: "ID");
                Console.WriteLine(JN(us1));
                Console.WriteLine(Environment.NewLine);
结果：

[{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[{"ID":3,"Name":"user0","ChineseName":"童光喜","DeptID":2,"Dept":null}]},{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[{"ID":4,"Name":"user2","ChineseName":"方原柏","DeptID":2,"Dept":null}]},{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[{"ID":5,"Name":"user4","ChineseName":"祝春亚","DeptID":2,"Dept":null}]}]

 

                //查询3 是我想要的结果
                var lookup = new Dictionary<int, Depts>();
                var us11 = cn.Query<Users, Depts, IList<Depts>>("SELECT a.ID,a.Name,a.ChineseName,a.DeptID,b.ID,b.Name,b.ParentID from Users a join depts b on a.deptid = b.id",
                 (user, dept) =>
                 {

                     if (!lookup.Keys.Contains(dept.ID))
                     {
                         lookup.Add(dept.ID, dept);
                         lookup[dept.ID].DeptUsers.Add(user);
                     }
                     else
                     {
                         lookup[dept.ID].DeptUsers.Add(user);
                     }
                     return lookup.Values.ToList();
                 }, splitOn: "ID");
                Console.WriteLine(JN(lookup.Values.ToList()));
                Console.WriteLine(Environment.NewLine);
结果：

[{"ID":2,"Name":"开发部","ParentID":1,"DeptUsers":[{"ID":3,"Name":"user0","ChineseName":"童光喜","DeptID":2,"Dept":null},{"ID":4,"Name":"user2","ChineseName":"方原柏","DeptID":2,"Dept":null},{"ID":5,"Name":"user4","ChineseName":"祝春亚","DeptID":2,"Dept":null}]}]

 

                //查询4 和查询2相同
                var us2 = cn.Query<dynamic>("SELECT a.ID as UserID,a.Name,a.ChineseName,a.DeptID,b.ID as DeptID,b.Name as DeptName,b.ParentID from Users a join depts b on a.deptid = b.id");
                Console.WriteLine(JN(us2));
结果：

[{"UserID":3,"Name":"user0","ChineseName":"童光喜","DeptID":2,"DeptID":2,"DeptName":"开发部","ParentID":1},{"UserID":4,"Name":"user2","ChineseName":"方原柏","DeptID":2,"DeptID":2,"DeptName":"开发部","ParentID":1},{"UserID":5,"Name":"user4","ChineseName":"祝春亚","DeptID":2,"DeptID":2,"DeptName":"开发部","ParentID":1}]

            }