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

1.1 无外键开箱就用

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

1.2 高性能优

查询 性能非常强悍

支持大数据分页导航查询

3.3 语法超级爽

注意：多级查询时VS有时候没提示直接写就行了

var list=db.Queryable()
.Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street)//多级查询 有时候VS没提示手写
.Includes(x => x.ClassInfo)// 一级查询
.ToList();

var list=db.Queryable()
//多级查询 加排序过滤
.Includes(x =>x.Provinces.Where(z=>z.Id>0).OrderBy(z=>z.Id).ToList(),x=>x.Citys,x=>x.Street)
// 一级查询
.Includes(x =>x.ClassInfo)
.ToList();

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

2.1 一对一

//实体
public class StudentA
{
[SugarColumn(IsPrimaryKey = true)]
public int StudentId { get; set; }
public string Name { get; set; }
public int SchoolId { get; set; }
[Navigat(NavigatType.OneToOne, nameof(SchoolId))]//一对一
public SchoolA SchoolA { get; set; }

    }  
    public class SchoolA  
    {  
        \[SugarColumn(IsPrimaryKey = true)\]  
        public int SchoolId { get; set; }  
        public string SchoolName { get; set; }  
    }  

//代码
var list2 = db.Queryable()
.Includes(x => x.SchoolA)
.Where(x => x.SchoolA.SchoolName == "北大")//可以对一级导航进行过滤
.ToList();

2.2 一对多

public class StudentA  
{  
 \[SugarColumn(IsPrimaryKey = true)\]  
 public int StudentId { get; set; }  
 public string Name { get; set; }  
 public int SchoolId { get; set; }  
 \[Navigat(NavigatType.OneToMany, nameof(BookA.studenId))\]  
 public List<BookA> Books { get; set; }

 }  
public class BookA  
{  
   \[SugarColumn(IsPrimaryKey = 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(z=>z.BookId==1))  
.ToList();

//例3:对子对象进行排序和过滤  
 var list = db.Queryable<StudentA>()  
   .Includes(x => x.Books.Where(y=>y.BookId >0).OrderBy(y=>y.BookId ).ToList())  
   .ToList();

2.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 )]
public int Id { get; set; }
public string Name { get; set; }
[Navigat(typeof(ABMapping1),nameof(ABMapping1.AId),nameof(ABMapping1.BId))]
public List BList { get; set; }
}
public class B1
{
[SugarColumn(IsPrimaryKey = true )]
public int Id { get; set; }
public string Name { get; set; }
[Navigat(typeof(ABMapping1), nameof(ABMapping1.BId), nameof(ABMapping1.AId))]
public List AList { get; set; }
}
//例1:简单用法
var list3= db.Queryable().Includes(x => x.BList).ToList();

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

//例3:支持主表过滤 Any和Count
var list3= db.Queryable().Includes(x => x.BList)
.Where(x=>x.BList .Any(z=>z.Id ==1)).ToList();

2.4 多级查询

配置好实体类，我们可以多级查询

var list=db.Queryable()
.Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street)//有时候没提示 直接写
.Includes(x => x.ClassInfo)// 一级查询
.ToList();

2.5 大数据分页导航

适合一次性查询1000条以上的导航

var list = new List();

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

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

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

3.1 手动实现二层

结构:  Student->SchoolA

var list = db.Queryable().ToList();
db.ThenMapper(list, stu =>
{
//如果加Where不能带有stu参数，stu参数写到 SetContext
stu.SchoolA=db.Queryable().SetContext(scl=>scl.SchoolId,()=>stu.SchoolId,stu).FirstOrDefault();
});
// SetContext不会生成循环操作，高性能 和直接Where性能是不一样的

如果没有SetContext那么这个查询将会循环

3.2 实现无限层注意：

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

var treeRoot=db.Queryable().Where(it => it.Id == 1).ToList();
//第一层
db.ThenMapper(treeRoot, item =>
{
item.Child = db.Queryable().SetContext(x => x.ParentId, () => item.Id, item).ToList();
});
//第二层
db.ThenMapper(treeRoot.SelectMany(it=>it.Child), it =>
{
it.Child = db.Queryable().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//第三层
db.ThenMapper(treeRoot.SelectMany(it => it.Child).SelectMany(it=>it.Child), it =>
{
it.Child = db.Queryable().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 Child { get; set; }
}
// SetContext不会生成循环操作，高性能 和直接Where性能是不一样的

Json to sql  正在开发中 ，未来将打造一套直接由前端操作数据库的API

{
"Queryable":"order",
Select:[ [{SqlFunc_AggregateMin:["id"]},"id"], [{SqlFunc_GetDate:[]},"Date"] ]
}

将支持 权限过滤 ，验证，多表查询，层级导航查询 等　　

https://github.com/donet5/SqlSugar

喜欢的可以点个星星、点个关注