程序地带

Net/NetCore/.NET5 ORM 六大查询体系 - SqlSugar 高级篇


框架介绍

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 "%"[email protected]+"%"

分组查询


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 


 


 


  


  


版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/sunkaixuan/p/14222937.html

随机推荐

坚持写原生SQL

1.根据订单表记录,找到已付款用户,批量更新用户表开通会员状态updatefa_usersetis_vip=1whereidin(selectuser_idfromfa_vip_orderwh...

花小东 阅读(453)

Java 实例 - 数字求和运算

以下实例演示了使用do…while结构求0~100的整数数字之和:Main.java文件publicclassMain{publicstaticvoidmain(String[]args...

for you is love 阅读(163)

edit控件自动换行 mfc_第二节课,了解控件

edit控件自动换行 mfc_第二节课,了解控件

mfc最主要的优点就是可视化,界面上的许多功能及控件都可以进行拖动。首先打开vs2010,可以最近使用的项目中找到昨天新建的项目,如图点击打开项目࿰...

weixin_39905695 阅读(890)

xdebug v3.x.x配置变化

给新装的虚拟机安装lamp环境,安装到xdebug的时候突然怎么也断不下来,怎么回事???原来是xdebug新版本的配置文件写法有...

Drom 阅读(989)

如何使用Photos App从Mac删除照片?

如何使用Photos App从Mac删除照片?

想要在Mac上快速删除照片吗?使用“照片”应用程序即可删除。如果您特别想删除照片而不是在Mac的硬盘上搜索,这通常是一种更直观的方法。这是在Mac上通过“照片”应用删除所有...

由土土 阅读(113)

hydra针对各类服务弱口令探测及利用

hydra针对各类服务弱口令探测及利用

hydra是黑客组织thc的一款开源的暴力密码破解工具。以kali自带的hydra为例进行演示,linux版和windows版的hydra,在使用上几乎是一样的࿰...

晓镁 阅读(355)

SQL语句---多表联查、改、删

SQL语句—多表联查、改、删1.多表联查select*fromStudentt1innerjoinTeachert2ont1.no=t2.nowheret2.Tname='Mi...

Mimo_YY 阅读(706)