导航查询

前言

导航查询特点：

• 主要处理主对象里面有子对象这种层级关系查询
• 只需要配置特性和主键即可，不需要外键

1.导航查询用法 (关键字：Includes)

1.1 一对一

用到的实体类：

public class StudentA
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        //新版本修改了命名
        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一一 SchoolId是StudentA类里面的
        public SchoolA SchoolA { get; set; } //不能赋值只能是null
    }
    
    
    public class SchoolA
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
    }

代码：

List<StudentA> list = db.Queryable<StudentA>()
                .Includes(it => it.SchoolA)
                .ToList();

string jsonStr1 = JsonConvert.SerializeObject(list);
string Jsonstr2 = db.Utilities.SerializeObject(list);   //sugar提供的序列化方法

//转Json后 将内容粘贴到 https://www.json.cn/ 解析查看数据格式

1.2 一对多

用到的实体类：

public class StudentB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(BookB.studenId))]  //BookB表中的studenId
        public List<BookB> Books { get; set; }  //注意禁止给Books手动赋值
    }
    
    
    public class BookB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int BookId { get; set; }
        public string Name { get; set; }
        public int studenId { get; set; }
    }

1.2.1 简单用法

注意：

• 简单写法 .Includes(it => it.Books.ToList()) 中的 ToList() 可以省略

List<StudentB> list1 = db.Queryable<StudentB>()
                .Includes(it => it.Books)
                .ToList();

string jsonStr1 = JsonConvert.SerializeObject(list);
string Jsonstr2 = db.Utilities.SerializeObject(list);    //sugar提供的序列化方法

//转Json后 将内容粘贴到 https://www.json.cn/ 解析查看数据格式

1.2.2 对主主对象进行过滤

注意：

• 支持Any Count，对主对象进行过滤
• Any 只支持一个层级，第二层级目前不支持

List<StudentB> list = db.Queryable<StudentB>()
                .Includes(it => it.Books)
                .Where(it => it.Name.Equals("B"))
                .ToList();

//提前对主对象进行 条件筛选过滤
List<StudentB> list = db.Queryable<StudentB>()
                .Includes(it => it.Books)
                .Where(it => it.Books.Any(x=>x.Name.Equals("三国")))
                .ToList();

1.2.3 对子对象进行排序和过滤

注意：

• Includes 中结尾的 ToList() 方法一定不要少了
• 否则会报错 "need is ToList()"

List<StudentB> list = db.Queryable<StudentB>()
                .Includes(it => it.Books.Where(b => b.BookId > 3).OrderByDescending(b => b.BookId).ToList())
                .ToList();

1.2.4 子表加Select

注意：

• 子表 studenId 是必有字段，剩下字段看 Select 投影了哪个字段，就会在sql查询中显示哪个字段

var list2 = db.Queryable<StudentB>()
           .Includes(it => it.Books.Select(z => new BookB() { BookId = z.BookId }).ToList())
           .ToList();

// SELECT `BookId` AS `BookId` ,`studenId` FROM `BookB`  WHERE   `studenId` IN (1,2,3,4)

var list2 = db.Queryable<StudentB>()
           .Includes(it => it.Books.Select(z => new BookB() { Name = z.Name }).ToList())
           .ToList();

// SELECT `Name` AS `Name` ,`studenId` FROM `BookB`  WHERE   `studenId` IN(1, 2, 3, 4)

1.2.5 主表加Select

var list = db.Queryable<StudentB>()
                .Includes(x => x.Books.ToList())
                .ToList(it => new
                {
                    AAA = it.StudentId,
                    BBB = it.Name,
                    CCC = it.Books
                });
                
string Jsonstr = db.Utilities.SerializeObject(list);
//转Json后 将内容粘贴到 https://www.json.cn/ 解析查看数据格式

1.3 多对多

用到的实体类：

public class ABMapping1
{
    [SugarColumn(IsPrimaryKey = true)]  //中间表可以不是主键
    public int AId { get; set; }
    [SugarColumn(IsPrimaryKey = true)]  //中间表可以不是主键
    public int BId { get; set; }
}


public class A1
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    [Navigate(typeof(ABMapping1), nameof(ABMapping1.AId), nameof(ABMapping1.BId))]  //注意顺序
    public List<B1> BList { get; set; }
}


public class B1
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    [Navigat(typeof(ABMapping1), nameof(ABMapping1.BId), nameof(ABMapping1.AId))]   //注意顺序
    public List<A1> AList { get; set; }
}  

1.3.1 简单用法

直接填充子对象B集合，前提是配置好特性

List<A1> list = db.Queryable<A1>()
                .Includes(x => x.BList)
                .ToList();
string Jsonstr = db.Utilities.SerializeObject(list);

1.3.2 子对象的过滤和排序

支持 WhereIF

List<A1> list = db.Queryable<A1>()
                .Includes(it => it.BList.Where(x=>x.Id>3).ToList())
                .ToList();
string Jsonstr = db.Utilities.SerializeObject(list);

1.3.3 主对象过滤

List<A1> list = db.Queryable<A1>()
                .Includes(a1 => a1.BList)
                .Where(a2 => a2.BList.Any(b => b.Id>2))
                .ToList();

string Jsonstr = db.Utilities.SerializeObject(list);

1.4 手动

用到的实体类：

public class StudentC
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        [Navigate(NavigateType.Dynamic, null)] //自定义关系映射
        public List<BookB> Books { get; set; }
    }

代码：

List<StudentC> list = db.Queryable<StudentC>()
                .Includes(it => it.Books.MappingField(z => z.studenId, () => it.StudentId).ToList())
                .ToList();
                
string Jsonstr = db.Utilities.SerializeObject(list);

1.5 多级导航

1.5.1 三层以下的写法

注意：

• 一对多 多对多 一对多 只要配好了都可以多层级使用

用到的实体类：

public class StudentB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一
        public SchoolB SchoolB { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(BookB.studenId))]//BookB表中的studenId 一对多
        public List<BookB> Books { get; set; }//注意禁止给Books手动赋值
    }
    
public class SchoolB
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(RoomB.SchoolId))]//一对多
        public List<RoomB> RoomList { get; set; }
    }
    
public class RoomB
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int RoomId { get; set; }
        public string RoomName { get; set; }
        public int SchoolId { get; set; }
    }
    
public class BookB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int BookId { get; set; }
        public string Name { get; set; }
        public int studenId { get; set; }
    }

代码：

var list = db.Queryable<StudentB>()
                .Includes(st => st.Books)//一级
                .Includes(st => st.SchoolB, sch => sch.RoomList)// 一级 + 两级
                .ToList();

string Jsonstr = db.Utilities.SerializeObject(list);

1.5.2 三层以上的写法

注意：

• 超过3个层级需要.AsNavQueryable()
• 缺点VS提示会消失，直接写不要在乎意提示不出来，VS关掉在开就行了，只要不改这个代码提示就不会有问题
• 示例： db.Queryable().AsNavQueryable().Includes(it=>it.1,it=>it.2,it=>it.3,it=>it.4，it=>it.5..)
• .AsNavQueryable()能不用尽量不要用，正常Includes(+3)重载完全够用了

用到的实体类：

    //省份
    public class Province
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Pid { get; set; }
        public string Pname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(City.pid))]
        public List<City> cityList { get; set; }
    }


    //市级
    public class City
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Cid { get; set; }
        public string Cname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(District.cid))]
        public List<District> districtList { get; set; }
        public int pid { get; set; }
    }


    //区级
    public class District
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Did { get; set; }
        public string Dname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(Town.did))]
        public List<Town> townList { get; set; }
        public int cid { get; set; }
    }


    //镇
    public class Town
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Tid { get; set; }
        public string Tname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(Village.tid))]
        public List<Village> villageList { get; set; }
        public int did { get; set; }
    }


    //村
    public class Village
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Vid { get; set; }
        public string Vname { get; set; }
        public int tid { get; set; }
    }

插入数据：

INSERT INTO `province` VALUES (1, '山东省');

INSERT INTO `city` VALUES (1, '烟台市', 1);
INSERT INTO `city` VALUES (2, '青岛市', 1);
INSERT INTO `city` VALUES (3, '威海市', 1);

INSERT INTO `district` VALUES (1, '开发区', 1);
INSERT INTO `district` VALUES (2, '莱山区', 1);
INSERT INTO `district` VALUES (3, '崂山区', 2);

INSERT INTO `town` VALUES (1, '大新店镇', 1);
INSERT INTO `town` VALUES (2, '刘家沟镇', 1);
INSERT INTO `town` VALUES (3, '北沟镇', 1);
INSERT INTO `town` VALUES (4, '解甲庄', 2);

INSERT INTO `village` VALUES (1, '大呼家村', 1);
INSERT INTO `village` VALUES (2, '回家村', 1);
INSERT INTO `village` VALUES (3, '刘家沟村', 2);
INSERT INTO `village` VALUES (4, '安乡刘家村', 2);
INSERT INTO `village` VALUES (5, '北沟一村', 3);
INSERT INTO `village` VALUES (6, '北沟二村', 3);

代码：

//普通查询，三层
 var list1 = db.Queryable<Province>()
                .Includes(p => p.cityList, c => c.districtList, d => d.townList)
                .ToList();
string Jsonstr1 = db.Utilities.SerializeObject(list1);


//三层以上
var list2 = db.Queryable<Province>()
                .AsNavQueryable()
                .Includes(p => p.cityList, c => c.districtList, d => d.townList, t => t.villageList)
                .ToList();

string Jsonstr2 = db.Utilities.SerializeObject(list2);

1.6 大数据导航

暂时没有可测试的环境
文档参考：分批处理

2.内存对象导航 (关键字：SetContext)

• 手动映射适合没有主键或者复杂的一些操作

用到的实体类：

    public class StudentX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        [SugarColumn(IsIgnore = true)]
        public SchoolX SchoolX { get; set; }
    }

    public class SchoolX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<RoomX> RoomList { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<TeacherX> TeacherList { get; set; }
    }
    
    public class TeacherX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Id { get; set; }
        public int SchoolId { get; set; }
        public string Name { get; set; }
    }
    
    public class RoomX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int RoomId { get; set; }
        public string RoomName { get; set; }
        public int SchoolId { get; set; }
    }

插入数据：

            db.Insertable(new RoomX() { RoomId = 1, RoomName = "北大001室", SchoolId = 1 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 2, RoomName = "北大002室", SchoolId = 1 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 3, RoomName = "北大003室", SchoolId = 1 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 4, RoomName = "清华001厅", SchoolId = 2 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 5, RoomName = "清华002厅", SchoolId = 2 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 6, RoomName = "清华003厅", SchoolId = 2 }).ExecuteCommand();


            db.Insertable(new SchoolX() { SchoolId = 1, SchoolName = "北大" }).ExecuteCommand();

            db.Insertable(new SchoolX() { SchoolId = 2, SchoolName = "清华" }).ExecuteCommand();


            db.Insertable(new StudentX() { StudentId = 1, SchoolId = 1, Name = "北大jack" }).ExecuteCommand();

            db.Insertable(new StudentX() { StudentId = 2, SchoolId = 1, Name = "北大tom" }).ExecuteCommand();

            db.Insertable(new StudentX() { StudentId = 3, SchoolId = 2, Name = "清华jack" }).ExecuteCommand();

            db.Insertable(new StudentX() { StudentId = 4, SchoolId = 2, Name = "清华tom" }).ExecuteCommand();


            db.Insertable(new TeacherX() { SchoolId = 1, Id = 1, Name = "北大老师01" }).ExecuteCommand();

            db.Insertable(new TeacherX() { SchoolId = 1, Id = 2, Name = "北大老师02" }).ExecuteCommand();

            db.Insertable(new TeacherX() { SchoolId = 2, Id = 3, Name = "清华老师01" }).ExecuteCommand();

            db.Insertable(new TeacherX() { SchoolId = 2, Id = 4, Name = "清华老师02" }).ExecuteCommand();

2.1 实现两层

注意：

• 结构：StudentX → SchoolX
• 如果没有SetContext那么这个查询将会循环
• db.ConextId外面和里面需要是同一个

var list = db.Queryable<StudentX>().Where(it=>it.StudentId==2 || it.StudentId==3).ToList();
            db.ThenMapper(list, stu =>
            {
                stu.SchoolX = db.Queryable<SchoolX>()
                .Where(scl => scl.SchoolId > 1)
                .SetContext(scl => scl.SchoolId, () => stu.SchoolId, stu).FirstOrDefault();
            });

string Jsonstr = db.Utilities.SerializeObject(list);

2.2 实现无线层

用到的实体类：

    //省份
    public class Province
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Pid { get; set; }
        public string Pname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<City> cityList { get; set; }
    }

    //市级
    public class City
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Cid { get; set; }
        public string Cname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<District> districtList { get; set; }
        public int pid { get; set; }
    }

    //区级
    public class District
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Did { get; set; }
        public string Dname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<Town> townList { get; set; }
        public int cid { get; set; }
    }

    //镇
    public class Town
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Tid { get; set; }
        public string Tname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<Village> villageList { get; set; }
        public int did { get; set; }
    }

    //村
    public class Village
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Vid { get; set; }
        public string Vname { get; set; }
        public int tid { get; set; }
    }

插入数据：

NSERT INTO `province` VALUES (1, '山东省');

INSERT INTO `city` VALUES (1, '烟台市', 1);
INSERT INTO `city` VALUES (2, '青岛市', 1);
INSERT INTO `city` VALUES (3, '威海市', 1);

INSERT INTO `district` VALUES (1, '开发区', 1);
INSERT INTO `district` VALUES (2, '莱山区', 1);
INSERT INTO `district` VALUES (3, '崂山区', 2);

INSERT INTO `town` VALUES (1, '大新店镇', 1);
INSERT INTO `town` VALUES (2, '刘家沟镇', 1);
INSERT INTO `town` VALUES (3, '北沟镇', 1);
INSERT INTO `town` VALUES (4, '解甲庄', 2);

INSERT INTO `village` VALUES (1, '大呼家村', 1);
INSERT INTO `village` VALUES (2, '回家村', 1);
INSERT INTO `village` VALUES (3, '刘家沟村', 2);
INSERT INTO `village` VALUES (4, '安乡刘家村', 2);
INSERT INTO `village` VALUES (5, '北沟一村', 3);
INSERT INTO `village` VALUES (6, '北沟二村', 3);

代码：

var list = db.Queryable<Province>().ToList();
db.ThenMapper(list, pro =>
{
    pro.cityList = db.Queryable<City>().SetContext(c => c.pid, () => pro.Pid, pro).ToList();
});
db.ThenMapper(list.SelectMany(it => it.cityList), city =>
{
    city.districtList = db.Queryable<District>().SetContext(d => d.cid, () => city.Cid, city).ToList();
});
db.ThenMapper(list.SelectMany(it => it.cityList).SelectMany(it => it.districtList), dis =>
{
    dis.townList = db.Queryable<Town>().SetContext(t => t.did, () => dis.Did, dis).ToList();
});
db.ThenMapper(list.SelectMany(it => it.cityList).SelectMany(it => it.districtList).SelectMany(it => it.townList), tow =>
{
    tow.villageList = db.Queryable<Village>().SetContext(v => v.tid, () => tow.Tid, tow).ToList();
});

string Jsonstr = db.Utilities.SerializeObject(list);

文档参考：导航查询