比較爽的導航查詢 功能 - SqlSugar ORM

果糖大資料科技發表於2022-04-14
SqlSugarORM

1、導航查詢特點

作用：主要處理主物件裡面有子物件這種層級關係查詢

1.1 無外來鍵開箱就用

其它ORM導航查詢 需要 各種配置或者外來鍵，而SqlSugar則開箱就用，無外來鍵，只需配置特性和主鍵就能使用

1.2 高效能優 

 查詢 效能非常強悍  

 支援大資料分頁導航查詢

3.3 語法超級爽

注意：多級查詢時VS有時候沒提示直接寫就行了

 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、新導航查詢 

適合有主鍵的常規操作, 請升級到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 多級查詢

配置好實體類，我們可以多級查詢

 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、無配置對映（高效能）

適合沒有主鍵或者複雜的一些操作

 

 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 、未來計劃

Json to sql  正在開發中 ，未來將打造一套直接由前端運算元據庫的API

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

將支援 許可權過濾 ，驗證，多表查詢，層級導航查詢 等　　

 

GitHUB 原始碼：

https://github.com/donet5/SqlSugar

 

喜歡的可以點個星星、點個關注 

　　

相關文章