首页 > 其他分享 >导航查询

导航查询

时间：2022-08-24 13:57:54浏览次数：82  
标签：set get int true db 查询 导航 public

前言

导航查询特点：

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

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

文档参考：导航查询

标签：set,get,int,true,db,查询,导航,public
From： https://www.cnblogs.com/DotNeter-Hpf/p/16619629.html

相关文章

  • linux查询进程被kill的日志 oom killer
    OOMKilledLinux内核有个机制叫OOMkiller(OutOfMemorykiller)，该机制会监控那些占用内存过大，尤其是瞬间占用内存很快的进程，然后防止内存耗尽而自动把该进程杀掉,内核检......
  • django中聚合函数查询和分组聚合查询
    聚合函数：Max，Min，Count首字母都要大写，且后面的参数加‘’号，不然会报错，还有就是，如果是Count(')的话，需要加个别名，比如(m=Count(''))，不然会报错，所以为了记住，我们平时MaxMin的......
  • 同样的代码逻辑，同样的表结构，由于数据量的不同导致查询没有走索引，而引起请求超时案例分
    问题描述：预发环境目前加载全部变更会超时，线上正常。需要看一下是什么问题避免预发上的问题带到线上      分析解决过程：EXPLAINSELECT i.damand_manager......
  • 基于MFC和C++的校园导航系统
    基于MFC和C++的校园导航系统基于MFC和C++实现校园导航系统项目简介设计一款面向广大师生和外来办公或参观人员的校园导航系统，为校外人员来校办事提供便利。校园导航系......
  • nebula 查询节点数、边数
    SHOWSTATS-NebulaGraphDatabase手册(nebula-graph.com.cn)1#选择图空间。2nebula>USEbasketballplayer;34#执行SUBMITJOBSTATS。5nebula>SU......
  • springboot+mybatis-plus-join+mysql实现连表查询
    1.简介  Mybatis是目前比较主流的持久层框架，使用非常广泛。Mybatis-Plus是基于Mybatis增强工具包，越来越受到开发人员的喜爱。  在使用Mybatis-Plus开发时，简单的crud......
  • go 查询es数据神器
    可以通过关键字和时间间隔进行查询，关键字可以像kibana上的查询语句填写，代码搞起//搜索返回的内容typeResultstruct{ Messagestring`json:"message"` Sourcestr......
  • mysql知识总结-连接查询
    Join链接方式Leftjoin左边的表为左表，rightjoin右边的表为右表1、左链接1.1、是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息......
  • postgresql从库查询被终止怎么办
    一，问题描述：PG流复制场景下，默认配置下，如果在PG从库执行长时间的查询，会出现查询的报错。提示ERROR:cancelingstatementduetoconflictwithrecoveryDETAIL:Userqu......
  • Mybatis下@Select注解下使用like模糊查询
    Mybatis下@Select注解使用模糊查询要使用concat方法拼接%百分号和关键词，案例如下：packagecom.xzit.mapper;importcom.xzit.entity.Emp;importorg.apache.ibatis.ann......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99