今天这篇文章分享一款好用简单的ORM框架 SqlSugar ，相比 EF Core的导航查询 更加简单 ，配置更加容易，几分钟就能上手
作用：主要处理主对象里面有子对象这种层级关系查询
其它ORM导航查询 需要 各种配置或者外键，而SqlSugar则开箱就用，无外键，只需配置特性和主键就能使用
查询 性能非常强悍
支持大数据分页导航查询
注意：多级查询时VS有时候没提示直接写就行了 ，相比 其他 .NET ORM语法要简单的多
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
//实体publicclass StudentA { [SugarColumn(IsPrimaryKey=true, IsIdentity = true)]publicint StudentId {get;set; }publicstring Name {get;set; }publicint SchoolId {get;set; } [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一public SchoolA SchoolA {get;set; } }publicclass SchoolA { [SugarColumn(IsPrimaryKey=true, IsIdentity = true)]publicint SchoolId {get;set; }publicstring SchoolName {get;set; } }//代码var list2 = db.Queryable() .Includes(x=> x.SchoolA) .Where(x=> x.SchoolA.SchoolName =="北大")//可以对一级导航进行过滤 .ToList();
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 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() .Includes(x => x.Books) .ToList(); //例2:支持Any和Count 对主表进行过滤 var list = db.Queryable() .Includes(x => x.Books) .Where(x=>x.Books.Any(z=>z.BookId==1)) .ToList(); //例3:对子对象进行排序和过滤 var list = db.Queryable() .Includes(x => x.Books.Where(y=>y.BookId >0).OrderBy(y=>y.BookId ).ToList()) .ToList(); //多对多publicclass ABMapping1 { [SugarColumn(IsPrimaryKey=true )]publicint AId {get;set; } [SugarColumn(IsPrimaryKey=true)]publicint BId {get;set; } }publicclass A1 { [SugarColumn(IsPrimaryKey=true, IsIdentity = true )]publicint Id {get;set; }publicstring Name {get;set; } [Navigate(typeof(ABMapping1),nameof(ABMapping1.AId),nameof(ABMapping1.BId))]public ListBList {get;set; } }publicclass B1 { [SugarColumn(IsPrimaryKey=true , IsIdentity = true)]publicint Id {get;set; }publicstring Name {get;set; } [Navigate(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和Countvar list3= db.Queryable ().Includes(x => x.BList) .Where(x=>x.BList .Any(z=>z.Id ==1)).ToList();
配置好实体类，我们可以多级查询 ，.NET 中轻松多级查询
var list=db.Queryable() .Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street)//有时候没提示 直接写 .Includes(x => x.ClassInfo)// 一级查询 .ToList();
适合一次性查询1000条以上的导航
var list = new List(); db.Queryable () .Includes(it => it.Child) .ForEach(it => list.Add(it), 300); //每次查询300条
更多用法：https://www.donet5.com/Home/Doc?typeId=2414
适合没有主键或者复杂的一些操作
结构: 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那么这个查询将会循环
了解原理后我们用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
喜欢的可以点个星星、点个关注