比较爽的导航查询 功能 .NET ORM / SqlSugar

.NET ORM 新概念导航

今天这篇文章分享一款好用简单的ORM框架 SqlSugar ,相比 EF Core的导航查询 更加简单 ,配置更加容易,几分钟就能上手

1、导航查询特点

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

1.1 无外键开箱就用

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

1.2 高性能优 

 查询 性能非常强悍  

 支持大数据分页导航查询

3.3 语法超级爽

注意:多级查询时VS有时候没提示直接写就行了 ,相比 其他 .NET ORM语法要简单的多

 var list=db.Queryable<Test>()
           .Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street)//多级查询 有时候VS没提示手写 
           .Includes(x => x.ClassInfo)// 一级查询
           .ToList();
                 
                 
 var list=db.Queryable<Test>()
        //多级查询  加排序过滤
        .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();

  

2、新导航查询 ORM

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

2.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))]//一对一
            public SchoolA SchoolA { get; set; }
  
        }
        public class SchoolA
        {
            [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
            public int SchoolId { get; set; }
            public string SchoolName { get; set; } 
        }
//代码
 var list2 = db.Queryable<StudentA>()
           .Includes(x => x.SchoolA)
           .Where(x => x.SchoolA.SchoolName == "北大")//可以对一级导航进行过滤
           .ToList();

2.2 一对多

    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.OneToMany, nameof(BookA.studenId))]
     public List<BookA> Books { get; set; }
 
     }
    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(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, 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; }
            [Navigate(typeof(ABMapping1), nameof(ABMapping1.BId), nameof(ABMapping1.AId))]
            public List<A1> AList { get; set; }
        }
 //例1:简单用法
var list3= db.Queryable<A1>().Includes(x => x.BList).ToList(); 
 
 //例2:支持子对象排序和过滤
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(z=>z.Id ==1)).ToList();

 

2.4 多级查询

配置好实体类,我们可以多级查询 ,.NET 中轻松多级查询

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

  

2.5 大数据分页导航 

适合一次性查询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、ORM无配置映射(高性能)

适合没有主键或者复杂的一些操作

 

 3.1 无配置映射实现二层

结构:  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();
});
// SetContext不会生成循环操作,高性能  和直接Where性能是不一样的

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

3.2  无配置映射无限级

了解原理后我们用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性能是不一样的

 

4 、.NET ORM 未来计划

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

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

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

 

 

GitHUB 源码:

https://github.com/donet5/SqlSugar

 

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

  

posted @ 2022-04-14 01:10  阿妮亚  阅读(7361)  评论(33编辑  收藏  举报