- A+
所属分类：.NET技术
框架介绍
SqlSugar ORM是一款老牌国产ORM框架，生命力也比较顽强，从早期ORM不成熟阶段，一直存活到现在，我为什么要一直坚持，那是因为还有很多用户在使用，本来我能够较早推出新开源框架 ，可是用户还在不停的提新的需求和高的要求，所以我也尽我最大努力将SqlSugar更加完善
1、有人说不支持国产数据库 我支持了
2、有人说 PgSql MySql Oracle兼容性不好，经过努力我也让他成熟了
3、有人说SqlSugar异步是Task.RUN，我也将SqlSugar升级到NET 4.5支持了原生异步
4、有人说导航支持不给力 ，我也让他开始给力
5、有人说不支持多级插入，我也支持了
我不能保证6年来每个用户的需求都能够支持，但是我能保证多个用户提出了一样的需求，那我就需要好好设计并且满足他们
本文重点
SqlSugar查询非常的丰富多彩，且这些功能也有非常高的实用性，我将SqlSugar的查询做了一个整理和分类：
1、基础查询
2、联表查询
3、子查询
4、导航查询
5、查询结果
6、高级查询
一、基础查询
基础查询从字面意思 就是最常用最简单的查询
var getAll = db.Queryable<Student>().ToList();//查询所有 var top10= db.Queryable<Student>().Take(10).ToList();//查询前10 var getFirst = db.Queryable<Student>().First(it=>it.Id==1);//查询单条 var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();//SqlServer里面的withnolock var getByPrimaryKey = db.Queryable<Student>().InSingle(2);//根据主键查询 var sum = db.Queryable<Student>().Sum(it=>it.Id);//查询总和 var isAny = db.Queryable<Student>().Where(it=>it.Id==-1).Any();//是否存在 var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1); var getListByRename = db.Queryable<School>().AS("Student").ToList(); var getByWhere = db.Queryable<Student>().Where(it => it.Id == 1 || it.Name == "a").ToList(); var list= db.Queryable<Student>().AS("student2019").ToList();//select * from student2019 var list2 = db.Queryable<Order>().Where(it =>it.Name.Contains("jack")).ToList();//模糊查询 name like '%'+@name+'%'
分组查询
var list = db.Queryable<Student>() .GroupBy(it => new { it.Id, it.Name }) .Having(it => SqlFunc.AggregateAvg(it.Id) > 0)//不是聚合函数用Where就可以了 .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id), name = it.Name }) .ToList(); //SELECT AVG([Id]) AS[idAvg], [Name] AS[name] FROM[Student] GROUP BY[Name],[Id] HAVING(AVG([Id]) > 0 )
分页查询
//同步分页 int pageIndex = 1; int pageSize = 20; int totalCount=0; var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);
并集合查询
var q1 = db.Queryable<Student>().Select(it=>new Model{ name=it.Name }); var q2 = db.Queryable<School>().Select(it => new Model { name = it.Name }); var list = db.UnionAll(q1, q2).ToList(); SELECT * FROM (SELECT [Name] AS [name] FROM [STudent] UNION ALL SELECT [Name] AS [name] FROM [School] ) unionTable
二、联表查询
1.两表查询将结果返回匿名对象
var list = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id)) .Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name})
.ToList();
生成的Sql如下:
SELECT [st].[ID] AS [id] , [st].[Name] AS [name] , [sc].[Name] AS [schoolName] FROM [STudent] st Left JOIN School sc ON ( [st].[SchoolId] =[sc].[Id])
2、联表查询将结果返回到新类,实现 Select a.*, b.name as bname
var oneClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos( JoinType.Left, o.Id == i.OrderId, JoinType.Left, o.CustomId == c.Id )) .Select((o,i,c)=> new ViewOrder// 是一个新类 { Id=SqlFunc.GetSelfAndAutoFill(o.Id),// 等于 o.* CustomName=c.Name // 等于 [c].[Name] AS [CustomName] }).ToList()
3.按规则自动填充
需要注意的是 Select用的是自动填充这样使用方便，高并发的地方还是写成上面那种方式
public class ViewModelStudent : Student { public string SchoolName{get;set;}// 类名 + 属性名 } var list = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos( JoinType.Left,st.SchoolId==sc.Id,//可以用&&实现 on 条件 and JoinType.Left,st.Name==di.String )) //.Where((st,sc)=>sc.id>0) 多表条件用法 .Select<ViewModelStudent>().ToList(); //SELECT //sc.[Name] AS [SchoolName],--自动生成 SchoolName //st.[ID] AS [Id],st.[SchoolId] AS [SchoolId], //st.[Name] AS [Name],st.[CreateTime] AS [CreateTime] //FROM [STudent] st //Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) //Left JOIN [DataTestInfo] di ON ( [st].[Name] = [di].[String] )
4、简单联表
var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId) .Select<ViewOrder>() .ToList();
生成的Sql:
SELECT c.[Name] AS [CustomName], o.[Id] AS [Id],o.[Name] AS [Name], o.[Price] AS [Price], o.[CreateTime] AS [CreateTime], o.[CustomId] AS [CustomId] FROM [Order] o ,[OrderDetail] i ,[Custom] c WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))
三、子查询
SqlSugar的子查询也是非常的强大，很多人都问
1. 子查询查一列
var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id)) .Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)) .ToList();
生成的Sql如下
SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime` FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` ) WHERE ( `st`.`ID` =(SELECT `Id` FROM `School` WHERE ( `Id` = `st`.`ID` ) limit 0,1))
在select中也可以使用
var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id)) .Select(st => new{ name = st.Name, id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id) }).ToList();
同时Subquery也支持了Join
.LeftJoin<OrderItem>((cus,item)=>cus.Id==item.CustomId /* 用 && 追加条件 */)
2.IN和NOT IN的操作
var getAll7 = db.Queryable<Student>().Where(it => SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).Any()).ToList(); /*生成的SQL(等于同于it.id in(select id from school)只是写法不一样 SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) */ var getAll8 = db.Queryable<Student>().Where(it => SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList(); /*生成的SQL SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) */
3、联表子查询
当你要用一个表和一个联进行联查询的时候，或者2个联表在进行联表查询的时候都可以用这种方式实现
var query1 = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id)) .Where(st => st.Name == "jack"); var query2 = db.Queryable<DataTestInfo>(); db.Queryable(query1, query2, (p1, p2) => p1.Id == p2.Int1).Select<ViewModelStudent>().ToList(); //SELECT * FROM // (SELECT [st].[ID],[st].[SchoolId],[st].[Name],[st].[CreateTime] // FROM [STudent] st // Left JOIN [School] sc ON ([st].[SchoolId]=[sc].[Id] ) // WHERE ( [st].[Name] = @Name0Join0 )) p1 // // Inner JOIN // (SELECT [Int1],[Int2],[String],[Decimal1],[Decimal2], // [Datetime1],[Datetime2],[Image1],[Image2], // [Guid1],[Guid2],[Money1],[Money2],[Varbinary1], // [Varbinary2],[Float1],[Float2] FROM [DataTestInfo] )p2 // ON ( [p1].[ID] = [p2].[Int1] )
4、多合一子查询
一般多表查询后 动态使用搜索条件和排序 需要使用 st sc等前缀，多表合成一后变成了单表查询 所以都不需要加别名了
var pageJoin = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id)) .Select((st,sc) => new{ id = st.Id, name = sc.Name}) .MergeTable() .Where(it=>it.id==1).OrderBy("name asc").ToList();
生成的Sql如下：
SELECT * FROM (SELECT [st].[Id] AS [id] , [sc].[Name] AS [name] FROM [Student] st Left JOIN [School] sc ON ( [st].[CustomId] = [sc].[Id] ) ) MergeTable--将结果变成一个表 WHERE ( [id] = @id0 )ORDER BY name asc
5、一合一的子查询
我只是想单表外面在包一层 你可以这样实现，和MergetTable区别在于前者需要加上Select后者不需要 var listx=db.Queryable(db.Queryable<Order>()).ToList();
Sql代码如下：
SELECT t.* FROM (SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] ) t
四、导航查询
1、无配置开箱就用
对于其它ORM导航查询来说一般都需要实体配置实体的关联关系，而SqlSugar则开箱就用，除了给非数据库字段加上Ignore外没有任何配置
2、高性能
自动映射模式不会产生循环Sql读取的情况，比如一对多查询 查询1条记录 那么生成2条SQL，那么查询200条记录还是生成2条SQL，
有人说为什么是2条而不是1条呢，1条复杂的sql性能未必好过2条简单无聚合分组的sql，可以通在sqlserver界面把3条sql扔进去然后
看执行计划的开销比例，如果说1条Sql大于等于50%那么他的性能就低于2条SQL性能
1、一对一查询
使用Mapper一对一模式需要设置2-3个参数， Mapper( 1需要填充的子对象 ,2主表关联字段，3从表关联字段（默认为从表主键）)
第一个参数：it.Order是填充的子对象
第二个参数 it.OrderId是主表中关联字段
第三个参数 不填等于从表主键，也可以指定从表某个字段
其实就是 主表中的 it.OrderId 和从表中的主键进行关联 ，就能自动填充到 OrderItem.Order里面
var list= db.Queryable<OrderItem>().Mapper(it => it.Order, it => it.OrderId).ToList(); foreach(var item in list) { Console.WriteLine(item.Order.Name);//输出子对象中的值 } //新功能： 1对1模式中支持了条件过滤 var list= db.Queryable<OrderItem>() .Mapper(it => it.Order, it => it.OrderId) .Where(it=>it.Order.Name=="哈哈").ToList(); //我们可以使用2级对象属性进行筛选
public class OrderItem { [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)] public int ItemId { get; set; } public string ItemCode { get; set; } public decimal Pirce { get; set; } public int OrderId { get; set; } [SugarColumn(IsIgnore =true)] public Order Order{ get; set; } } public class Order { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } }
2、一对多查询
var list= db.Queryable<Order>().Mapper(it => it.Items, it => it.Items.First().OrderId).ToList();
public class Order { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } [SugarColumn(IsIgnore = true)] public List<OrderItem> Items { get; set; } }
3、 多对多查询
public class ABMapping { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int AId { get; set; } public int BId { get; set; } [SugarColumn(IsIgnore = true)] public A A { get; set; } [SugarColumn(IsIgnore = true)] public B B { get; set; } } public class A { [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)] public int Id { get; set; } public string Name { get; set; } } public class B { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } } var list= db.Queryable<ABMapping>() .Mapper(it => it.A,it=>it.AId) .Mapper(it => it.B, it => it.BId).ToList();
4、 相同字段名映射
子表对象=子表对象.where(it=>it.OrgId==主表对象中的.OrgId)
var list= db.Queryable<A>().Mapper(it => it.B,it=>it.OrgId).ToList();
五、查询结果
支持常用的 类、匿名类、数组、字典、字典集合、JSON、DataTable、树
//返回List List<Student> list = db.Queryable<Student>().ToList(); //返回DataTable DataTable dataTable = db.Queryable<Student>().Select(it => it.Id).ToDataTable(); //返回Json var json = db.Queryable<Student>().ToJson(); //返回数组 List<int> listInt=db.Queryable<Student>().Select(it => it.Id).ToList(); //返回匿名对象 var dynamic = db.Queryable<Student>().Select<dynamic>().ToList(); //表达式返回字典 Dictionary<string,object> dc = db.Queryable<Order>().ToDictionary(it=>it.Id,it=>it.Name); //返回字典集合 List<Dictionary<string, object>> dcList = db.Queryable<Order>().ToDictionaryList();;
Tree树结果
var tree = db.Queryable<Tree>().ToTree(it=>it.Child,it=>it.ParentId,0); //第三个参数为0代表 parent为0是第一级 //实体 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 List<Tree> Child { get; set; } }
六、高级查询
二缓缓存是将结果集进行缓存，当SQL和参数没发生变化的时候从缓存里面读取数据，减少数据库的读取操作
ICacheService myCache = new HttpRuntimeCache(); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = Config.ConnectionString, DbType = DbType.SqlServer, IsAutoCloseConnection = true, ConfigureExternalServices = new ConfigureExternalServices() { DataInfoCacheService = myCache //配置我们创建的缓存类 } }); db.Queryable<Student>().Where(it => it.Id > 0).WithCache().ToList();//设置缓存默认一天 db.Queryable<Student>().WithCache(1000).ToList();//设置具体过期时间
异步查询
var task1=db.Queryable<Order>().FirstAsync(); var task2 = db.Queryable<Order>().Where(it=>it.Id==1).ToListAsync(); //分页需要特别注意用法 RefAsync<int> total = 0; Db.Queryable<Order>().ToPageListAsync(1, 2, total);
Json类型查询
public class UnitJsonTest { [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } [SqlSugar.SugarColumn(ColumnDataType ="varchar(2000)", IsJson = true)] public Order Order { get; set; } public string Name{get;set;} } Db.Insertable(new UnitJsonTest() { Name="json1",Order = new Order { Id = 1, Name = "order1" } }).ExecuteCommand(); var list = Db.Queryable<UnitJsonTest>().ToList();
动态表达式查询
var exp= Expressionable.Create<Student>() .And(it=>it.Id==1) .Or(it =>it.Name.Contains("jack")).ToExpression(); var list=db.Queryable<Student>().Where(exp).ToList();
动态条件查询
var conModels = new List<IConditionalModel>(); conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1"}); var student = db.Queryable<Student>().Where(conModels).ToList();
SqlSugar源码下载：
https://github.com/donet5/SqlSugar
没点关注的点一波关注，我不能保证SqlSugar现在就是最好，便是未来会越来越好 ，谢谢
新的开源框架WebFirst也在开发中，预计春节前发布,将支持 web和exe
