多级查询、导航查询

1、导航查询特点 

作用:主要处理主对象里面有子对象这种层级关系查询

1.1 无外键开箱就用

其它ORM导航查询 需要 各种配置或者外键,而SqlSugar则开箱就用,无外键,只需配置特性和主键就能使用

1.2 高性能优 

 查询 性能非常强悍   5.0.8.1preview02版本进行了性能优化 

 支持大数据分页导航查询

3.3 语法超级爽

 var list=db.Queryable<Test>()
           .Includes(t=> t.Provinces, pro=>pro.Citys ,cit=>cit.Street) //多层级
           .Includes(t=> t.ClassInfo)// 一个层级查询
           .ToList();              
               
 //具体用法看下面文档介绍
 //多层级可以看2.5


2、新导航查询5.0.6.8

适合有主键的常规操作, 请升级到5.0.6.8

2.1 一对一 ( one to one )

//实体
public class StudentA
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int StudentId { get; set; }
    public string Name { get; set; }
    public  string SexCode { get;set;}
    public int SchoolId { get; set; }
    
    //用例1:主键模式 StudentA(主表)表中的 SchoolId 和SchoolA(子表)中的主键关联 
    [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是StudentA类里面的
    public SchoolA SchoolA { get; set; } //不能赋值只能是null
    
    //用例2:反向导航,2个字段匹配关系 
    //[Navigate(NavigateType.OneToOne, nameof(SchoolId),nameof(SchoolA.Id))] 
    //第一个主表字段,第二从表字段  顺序不要错了
    
    //用例3: 字典导航 多了个SQL条件参数  
    //[SqlSugar.Navigate(NavigateType.OneToOne,nameof(SexId),nameof(DataDictionary1.Code),"type='sex'")]
    //具体用法可以看配置查询 https://www.donet5.com/Home/Doc?typeId=2309

}
public class SchoolA
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id{ get; set; }
    public string SchoolName { get; set; }
} 

//导航+主表过滤  导航属性过滤
var list = db.Queryable<StudentA>()
         .Includes(x => x.SchoolA) //填充子对象 (不填充可以不写)
         .Where(x =>x.SchoolA.SchoolName=="北大") 
         .ToList();


//导航+主表过滤  只查有导航数据 (新功能:5.1.2.8)
var list = db.Queryable<StudentA>()
         .Includes(x => x.SchoolA) //填充子对象 (不填充可以不写)
         .Where(x => SqlFunc.Exists(x.SchoolA.主键))
         .ToList();            
                         
         
//导航+子表过滤 5.0.9.4-preview06 请注意升级 
//创建一个扩展函数,默认是Class不支持Where
public static List<T> Where<T>(this T thisValue, Func<T,bool> whereExpression ) where T:class,new()
{
   return new List<T>() { thisValue };
} 
var list = db.Queryable<Student_003>()
          .Includes(x => x.school_001.Where(z=>z.Name=="a").ToList())//扩展的Where对子表进行过滤
          .ToList(); //5.0.9.4-preview06 才支持 请注意升级 请注意升级

//导航 指定字段 5.1.3.38
var list = db.Queryable<StudentA>()
         .Includes(x => x.SchoolA.ToList(it=>new SchoolA(){ Name =it.Name,id=it.Id})) 
         .ToList();                    
          
          
//导航如果只查一个字段         
var list = db.Queryable<StudentA>()
             .Where(x => x.id>1)  //Where和Select中别名要写一样
             .Select(x =>new { 
                x=x,
                SchoolName= x.SchoolA.SchoolName
             }).ToList();

非标准1对1 看文档2.4

2.2 一对多 ( one to many)

BookA(子表)中的studenId和StudentA(主表)中的主键关联

//实体
public class StudentA
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id{ get; set; }
    public string Name { get; set; }
    public int SchoolId { get; set; }
    [Navigate(NavigateType.OneToMany, nameof(BookA.studenId))]//BookA表中的studenId
    public List<BookA> Books { get; set; }//注意禁止给books手动赋值
    
    //支持 StudentA没有主键
    //[Navigate(NavigateType.OneToMany, nameof(BookA.studenId),nameof(Id))] 
    //与一对一相反 第一个 从表字段,第二个主表字段

}
public class BookA
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int BookId { get; set; }
    public string Name { get; set; }
    public int studenId { get; set; }
}

//例1:简单用法
var list = db.Queryable<StudentA>()
.Includes(x => x.Books)
.ToList();

//例2:支持Any和Count 对主表进行过滤 (子对象过滤看下面)
var list = db.Queryable<StudentA>()
.Includes(x => x.Books)
.Where(x => x.Books.Any())
//带条件的
//.Where(x => x.Books.Any(z=>z.Name=="jack")))
.ToList();

//例3: 没有Includes也可以使用过滤
var list2 = db.Queryable<StudentA>()
.Where(x => x.Books.Any())//Any中可以加条件 Any(z=>z.BookId==1)
.ToList();                         


//例4:Where子对象进行排序和过滤 (支持WhereIF)
var list = db.Queryable<StudentA>()
  .Includes(x => x.Books.Where(y => y.BookId > 0).OrderBy(y => y.BookId).ToList())
  .ToList();
  
//例5:Select指定字段
var list= db.Queryable<StudentA>()
           .Includes(x => x.Books.Select(z=>new BookA() { Names = z.Names }).ToList()) 

//例5-1:Select多层级  (结构:StudentAt->books->BookItems)      
var list= db.Queryable<StudentA>()
     .Includes(x => x.Books.Select(z=>new BookA(){Names=z.Name}.ToList(),it=>BookItems)) 
    .ToList();、//Includes中的Select只能是字段 ,不能导航对象           
           
           
//例6:OrderBy指定字段 (Skip Take可以分页)
var list= db.Queryable<StudentA>()
           .Includes(x => x.Books.OrderBy(z=>z.Id).ToList()) 
           .ToList();
           
//例7:Take取前几条
var list= db.Queryable<StudentA>()
           .Includes(x => x.Books.Take(10).ToList()) 
           .ToList();          
                      
           
//例8:DTO支持进行了强化          
var SelectDto= db.Queryable<StudentA>()
           .Includes(x => x.Books) 
           .ToList(x=>new 
           { 
               StudentName=x.Name,
               Book=x.Books 
           
           });
           //5.0.8.2-preview01支持了分页 查询DTO
           //.ToPageList(1,2,ref xxx1, it=>new { .... });
           
           
//例9:一对多后还可用追加字段映射MappingField 如果以前是1个字关联,现在追加后就成了1+1       
db.Queryable<StudentA>().Includes(x => x.Books.MappingField(z=>z.字段,()=>x.字段).ToList() )
                .ToList();
//MappingField 和 Where区别
//MappingField MappingField用来指定2个对象的关系,Where只能当前表过滤不能和主表进行关联 
//MappingField 可以多个也可以和Where一起始用

非标准1对多 看文档2.4

2.3 多对多 ( many to many)

//实体
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; }//只能是null不能赋默认值
}
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; }//只能是null不能赋默认值
}  
//例1:简单用法 直接填充B的集合,只要配置好特性非常简单
var list3= db.Queryable<A1>().Includes(x => x.BList).ToList(); 

 //例2:支持子对象排序和过滤 (支持WhereIF)
var list3= db.Queryable<A1>().Includes(x => x.BList.Where(z=>z.Id>0).ToList()).ToList(); 

 //例3:支持主表过滤  Any和Count
var list3= db.Queryable<A1>().Includes(x => x.BList)
                .Where(x=>x.BList.Any())//Any里面可以加条件 Any(z=>z.xxxx>0)
                .ToList();
//不使用Includes一样可以过滤              
var list3= db.Queryable<A1>() 
                .Where(x=>x.BList.Any()) //可以加条件.Where(x=>x.BList.Any(z=>z.xxx==x.yyy))
                .ToList();                
                
//多对多后还可用追加字段映射MappingField 如果以前是2个字段关联,现在追加后就成了2+1       
db.Queryable<A1>().Includes(x => x.BList.MappingField(z=>z.字段,()=>x.字段).ToList()  )
                .ToList();
//MappingField 和 Where区别
//MappingField MappingField用来指定2个对象的关系,Where只能当前表过滤不能和主表进行关联 
//MappingField 可以多个也可以和Where一起始用

2.4 手动、指定字段、多字段

支持多个字段关联  5.0.7.8

 //指定关系一对一和指定关一对多用法一样的
 //自定义一对一
 //it.SchoolA.MappingField(z=>z.SchoolId,()=>it.SchoolId).ToList()
 //自定义一对多
 //it.SchoolList.MappingField(z=>z.SchoolId,()=>it.SchoolId).ToList()
 
 
 //具体用例
 var list=db.Queryable<StudentA>()
                .Includes(it => it.Books
                           .MappingField(z=>z.studenId,()=>it.StudentId)
                           //可以多个多字段 .MappingField(..)
                           .Where(z=>z.BookId==1)//还能在加条件过滤
                           .ToList()
                    )
                .ToList();
  public class StudentA
  {
            [SugarColumn(IsPrimaryKey = true)]
            public int StudentId { get; set; }
            public string Name { get; set; }
            [Navigate(NavigateType.Dynamic, null)] //自定义关系映射
            public List<BookA> Books { get; set; } //只能是null 不能赋默认值
            
            //如果用到:自定义 一对一 请级到 5.0.7.8-preview02版本用法一样
            //用法和一对多一模一样升级就行了
 
 }
 
 
 //请升级到5.0.7.8 preview02版本  
 //自定义导航也支持多个层级 结构:StudentA[i].Books[i].bookChilds 
 var list=db.Queryable<StudentA>()
        .Includes(it => 
                        it.Books.MappingField(z=>z.studenId,()=>it.StudentId).ToList(),
                    c=>  
                        c.bookChilds .MappingField(z=>z.BookId,()=>c.BookId).ToList()
                    )
        .ToList();

注意:MappingField 也可以附加在一对多和多对多上使用作为追加条件

2.5 多级导航(例如:省>市>区)

配置好实体类,我们可以多级查询(一对多、一对多、多对多都支持只要配好类就可以使用)

public class StudentA
{
    [SugarColumn(IsPrimaryKey = true)]
    public int StudentId { get; set; }
    public string Name { get; set; }
    public int SchoolId { get; set; }
    [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一
    public SchoolA SchoolA { get; set; }
    [Navigate(NavigateType.OneToMany, nameof(BookA.studenId))]//一对多
    public List<BookA> Books { get; set; }//只能是null不能赋默认值

}
public class SchoolA
{
    [SugarColumn(IsPrimaryKey = true)]
    public int SchoolId { get; set; }
    public string SchoolName { get; set; }
    [Navigate(NavigateType.OneToMany, nameof(RoomA.SchoolId))]//一对多
    public List<RoomA> RoomList { get; set; }//只能是null不能赋默认值
}

public class RoomA
{
    [SugarColumn(IsPrimaryKey = true)]
    public int RoomId { get; set; }
    public string RoomName { get; set; }
    public int SchoolId { get; set; }
}
public class BookA
{
    [SugarColumn(IsPrimaryKey = true)]
    public int BookId { get; set; }

    public string Name { get; set; }
    public int studenId { get; set; }
}       

var list2 = db.Queryable<StudentA>()
             //查2层
             .Includes(st => st.SchoolA, sch=> sch.RoomList)//查询2级(等于EF ThenInclude)
             //查1层
             .Includes(st=> st.Books)  
             .ToList()
//说明: 一对多 多对多 一对多 只要配好了都可以多层级使用
 
             
//如果想超过3个层级需要.AsNavQueryable()
//缺点VS提示会消失,直接写不要在乎意提示不出来,VS关掉在开就行了,只要不改这个代码提示就不会有问题
db.Queryable<Order>().AsNavQueryable().Includes(it=>it.1,it=>it.2,it=>it.3,it=>it.4,it=>it.5..)
//.AsNavQueryable()能不用尽量不要用,正常Includes(+3)重载完全够用了


2.6 性能优化

1、升级 如果搜索不到勾选预览版本

5.0.8.1 preview02版本针对大数据导航有了很好的性能优化

2、分页导航

底层分批量查询 适合一次性查询1000条以上的导航

    var list = new List<Tree1>();

    db.Queryable<Tree1>()
        .Includes(it => it.Child)
        .ForEach(it => list.Add(it), 300); //每次查询300条

更多用法:https://www.donet5.com/Home/Doc?typeId=2414

3、关联字段推荐用主键,如果非主键导航加索引为佳

2.7 转DTO

1. 手动转DTO

//新功能  5.0.9.5对DTO支持进行了强化           
var SelectDto= db.Queryable<StudentA>()
           .Includes(x => x.Books) 
           .ToList(it=>
                    
                   new BooksDto{    
                         Book=x.Books 
                });
                
 //5.0.8.2-preview01支持了分页 查询DTO
 //.ToPageList(1,2,ref xxx1, it=>new { .... });

2.Mapster 工具映射 (推荐) 比AutoMapper方便不需要配置

Nuget直接安装就行了

var list=db.Queryable<StudentA>()
           .Includes(x => x.Books).ToList();
var dtoList=list.Adapt<List<StudentDTO>>()

2.8 导航方法

一对多和多对多

在我们一对多和多对多对象我们可以用导航方法Any()和导航方法Count

//注意:不需 Includes 就可以使用
Where(it=>it.导航对象.Any())
Where(it=>it.导航对象.Any(z=>z.id==1))
Where(it=>it..导航对象.Any(List<IConditionalModel>)//5.1 

//Count用法类似

一对一函数  5.1.2.9

//注意:不需 Includes 就可以使用
Where(x=>SqlFunc.Exists(x.SchoolA.Id))//查询存在一对一的主表数据
Where(x=>SqlFunc.Exists(x.SchoolA.Id,List<IConditionalModel>))//查询存在一对一的主表数据

2.9 一级导航下面一个有2个导航

如果Books下面有2个导航

.Includes(x => x.Books,x=>x.A) 
.Includes(x => x.Books,x=>x.B)


2.91 跨库导航

https://www.donet5.com/Home/Doc?typeId=2244


3、支持联表的导航

 手动映射适合没有主键或者复杂的一些操作,该功能和Includes文档 2.4比较接近

3.1 创建测试数据

创建类

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

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

创建测试数据

db.CodeFirst.InitTables<StudentA, RoomA, SchoolA,TeacherA>();
db.DbMaintenance.TruncateTable<StudentA>();
db.DbMaintenance.TruncateTable<RoomA>();
db.DbMaintenance.TruncateTable<SchoolA>();
db.DbMaintenance.TruncateTable<TeacherA>();
db.Insertable(new RoomA() { RoomId = 1, RoomName = "北大001室", SchoolId = 1 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 2, RoomName = "北大002室", SchoolId = 1 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 3, RoomName = "北大003室", SchoolId = 1 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 4, RoomName = "清华001厅", SchoolId = 2 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 5, RoomName = "清华002厅", SchoolId = 2 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 6, RoomName = "清华003厅", SchoolId = 2 }).ExecuteCommand();


db.Insertable(new SchoolA() { SchoolId = 1, SchoolName = "北大" }).ExecuteCommand();
db.Insertable(new SchoolA() { SchoolId = 2, SchoolName = "清华" }).ExecuteCommand();

db.Insertable(new StudentA() { StudentId = 1, SchoolId = 1, Name = "北大jack" }).ExecuteCommand();
db.Insertable(new StudentA() { StudentId = 2, SchoolId = 1, Name = "北大tom" }).ExecuteCommand();
db.Insertable(new StudentA() { StudentId = 3, SchoolId = 2, Name = "清华jack" }).ExecuteCommand();
db.Insertable(new StudentA() { StudentId = 4, SchoolId = 2, Name = "清华tom" }).ExecuteCommand();

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

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

3.2 手动实现二层

结构:  Student->SchoolA

var list = db.Queryable<StudentA>().ToList();//这儿也可以联表查询
db.ThenMapper(list, stu =>
{
  //如果加Where不能带有stu参数,stu参数写到 SetContext
  stu.SchoolA=db.Queryable<SchoolA>().SetContext(scl=>scl.SchoolId,()=>stu.SchoolId,stu).FirstOrDefault();
  
  //可以联查询的
  //stu.xxxx=db.Queryable<SchoolA>().LeftJoin<XXX>().Select(xxxx).SetContext(....).ToList();
});
// SetContext不会生成循环操作,高性能  和直接Where性能是不一样的

注意:1、如果没有SetContext那么这个查询将会循环

          2、db.ConextId外面和里面需要是同一个

3.3 实现无限层

了解原理后我们用ThenMapper想映射哪层就映射哪层

var treeRoot=db.Queryable<Tree>().Where(it => it.Id == 1).ToList();
//第一层
db.ThenMapper(treeRoot, item =>
{
    item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
});
//第二层
db.ThenMapper(treeRoot.SelectMany(it=>it.Child), it =>
{
    it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//第三层
db.ThenMapper(treeRoot.SelectMany(it => it.Child).SelectMany(it=>it.Child), it =>
{
    it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//这儿只是用树型结构来证明可以实现无限级别导航查询 ,实际开发中树型结构用ToTree实现
public class Tree
{
[SqlSugar.SugarColumn(IsPrimaryKey =true)]
public int Id { get; set; }
public string Name { get; set; }
public int ParentId { get; set; }
[SqlSugar.SugarColumn(IsIgnore = true)]
public Tree Parent { get; set; }
[SqlSugar.SugarColumn(IsIgnore = true)]
public List<Tree> Child { get; set; }
}
// SetContext不会生成循环操作,高性能  和直接Where性能是不一样的

新功能 : 请升级到5.0.6.7 预览版本 及以上 


4、树型查询

https://www.donet5.com/Home/Doc?typeId=2311


5、老版导航查询

如果使用Mapper的用户可以看这个

https://www.donet5.com/ask/9/15831


6、不加特性使用导航

通过实体AOP方法实现,具体用法看实体配置

EntityService= (type, columnInfo) =>
 {
   if(columnInfo.EntityName == "Order" && columnInfo.PropertyName == "OrderMerchandiseResources")
   {
       //手动设置导航属性
      columnInfo.Navigat = new Navigate(NavigateType.OneToMany,nameof(OrderMerchandiseResource.OrderId));
      columnInfo.IsIgnore=true;//这个一定要加上
   }
 }

7、更多导航操作

导航更新: https://www.donet5.com/home/Doc?typeId=2432

导航删除: https://www.donet5.com/home/Doc?typeId=2431

导航插入: https://www.donet5.com/home/Doc?typeId=2430

导航查询: https://www.donet5.com/home/Doc?typeId=1188


文档:SqlSugar5.0