【查询函数】.NET开源ORM框架 SqlSugar 系列

🎯本章的主角是SqlFunc，通过它调用各种sql函数，在应对各种复杂查询语句发挥着重要的作用。

一、基本用法 💎

我们可以使用 SqlFunc 这个类调用 Sql 函数，用法如下：


db.Queryable<Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList();

📌其他的函数用法基本一致，下面将要介绍常用的函数用法。

二、C#函数 🔎

🧮支持一些常用的C#函数：

. ToString  .Contains  .Length.ToLower  .ToUpper  .ToSubstring 

.Equals    .HasValue  .Replace .EndsWith  .StartsWith  .Trim

.HasValue  .Value  .AddDays(AddHours等)  和 常用的   Convert.ToInt32 等

三元    xx??0     时间.DayOfWeek    时间.Date    时间.Day等   (时间-时间).TotalDays

三、逻辑函数 🎡

🎭三元判段，相当于 it.id==1?1:2

SqlFunc.IIF(it.Id == 1, 1, 2)

3.1 case when

 SqlFunc.IF(st.Id > 1)
     .Return(st.Id)
     .ElseIF(st.Id == 1)
     .Return(st.SchoolId).End(st.Id)
      
//注意:End因为泛型原因没办传null,如果需要传null这么写.End<int>();
//等于同 case when

3.2 IsNulll

®️4.6.0.1+ 支持。

SqlFunc.IsNull(it.Id, 0)//如果是null则是0
 
//it.Id??0
 
//如果是等于处理NULL看下面的 EqualsNull
//ifnull

四、时间函数 🕰️

⏱️时间格式化，®️请升级到5.0.51+,更好的支持了时间格式化。

//都支持常用yyyy-MM-dd
it.CreateTime.ToString("yyyy-MM-dd")
 
//1、SqlServer 2012及以上 用SQL查看版本 ：select @@version  
//5.0.6+ ToString完美支持了所有 yyyy mmm ddd这种格式
  
  
//SqlServer2012以下  
// 5.0.6+  只支持（ yyyy-MM-dd 、yyyy-MM 和 yyyy-MM-dd hh:mm:ss ）  3种格式 
// ToString("23") 带数字写法生成convert( varchar(20) ,时间, 23)
 
 
//2、MySql和Sqlite  
//支持 yyyy-MM-dd 、 yyyy-MM 和 yyyy-MM-dd hh:mm:ss
//可以使用原生转换: it.CreateTime.ToString("%Y-%m")//只要有%号就会进入原生转换
//https://www.cnblogs.com/shuilangyizu/p/8036620.html对照表
 
//3、PgSql和Oracle 完美支持所有C#  yyyy mmm ddd 格式转换

⏱️获取数据库时间 ®️4.6.0.1


SqlFunc.GetDate(); //数据库服务器时间  
注意 ：Sqlfunc方法只能用在表达式中，如果非表达式中用 db.GetDate()

4.1 是否是同一天

SqlFunc.DateIsSame(DateTime date1, DateTime date2)
 
//例子：查询今天 今日 当天
SqlFunc.DateIsSame(it.CreateTime,DateTime.Now)

4.2 是否是同一月


it.CreateTime.ToString("yyyy-MM")==DateTime.Now.ToString("yyyy-MM") //日期格式化

4.3 是否是同一年


it.CreateTime.Year==DateTime.Now.Year

4.4 是否是同一时间

🔖dataType 可以是年、月、天、小时、分钟、秒和毫秒

SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType)
//本月 本年  本天 都可以用这个函数

4.5 在当前时间加一定时间

🔖dataType 可以是年、月、天、小时、分钟、秒和毫秒

SqlFunc.DateAdd(DateTime date, int addValue, DateType dataType)

4.6 在当前时间加N天


SqlFunc.DateAdd(DateTime date, int addValue)
 
//也可以用
it.CreateTime.AddDasy(1)

4.7 获取当前时间的年、月、天、小时、分钟、秒或者毫秒

SqlFunc.DateValue(DateTime date, DateType dataType)
 
//也可以用
it.CreateTime.Day

4.8 DateDiff 计算时间差


 SqlFunc.DateDiff(type,DateTime.Now,DateTime.Now.AddDays(1)) 
 //结果等于1 ，相差1天，小的时间在前面，大的时间在后面
 //生成的SQL
 DATEDIFF(day,@MethodConst1, (DATEADD(Day,@MethodConst4,@MethodConst3)) )
  
  
 //也可以
 (时间-时间).TotalDays

4.9 周数、周几

//周几
 SqlFunc.DateValue(DateTime.Now, DateType.Weekday);
 //一年中第几周
 SqlFunc.WeekOfYear

五、聚合函数 🥏


SqlFunc.AggregateSum<TResult>(TResult thisValue) //求和
SqlFunc.AggregateSumNoNull<TResult>(TResult thisValue) //求和（新 支持过滤null）
SqlFunc.AggregateAvg<TResult>(TResult thisValue)//平均值
SqlFunc.AggregateMin(TResult thisValue) //最小
SqlFunc.AggregateMax<TResult>(TResult thisValue) //最大
SqlFunc.AggregateCount<TResult>(TResult thisValue)//统计数量
SqlFunc.AggregateDistinctCount<TResult>(TResult thisValue) //去重统计数量

六、格式转换截取替换等 🪡

不包含时间转换，时间转换看标题【时间函数】88888

6.1 四舍五入精度 (5.0.4)

SqlFunc.Round(it.Price,2) //两位小数

6.2 绝对值函数 (5.0.4)

 SqlFunc.Abs(it.Price)

6.3 截取字符串


SqlFunc.Substring(object value, int index, int length)//字符串截取

6.4 替换字符串

SqlFunc.Replace(object value, string oldChar, string newChar)

6.5 转小写


SqlFunc.ToLower(object thisValue)

6.6 转大写

SqlFunc.ToUpper(object thisValue)

6.7 去前后空格


SqlFunc.Trim(object thisValue)

6.8 类型转换

SqlFunc.ToInt32(object value) 
SqlFunc.ToInt64(object value)
SqlFunc.ToDate(object value) 
SqlFunc.ToString(object value) 
SqlFunc.ToDecimal(object value) 
SqlFunc.ToGuid(object value) 
SqlFunc.ToDouble(object value) 
SqlFunc.ToBool(object value)

6.9 指定位置替换

SqlFunc.Stuff(string sourceString, int start, int length, string AddString)

七、BOOL返回值函数 🎭

7.1 字符串比大小，字符串比较，字符串对比

(int)(object)it.str1>(int)(object)str2
//生成的sql
//it.str1>@str2
 
//或者用函数 较高版本支持
SqlFunc.GreaterThan
SqlFun.LessThan

7.2 判段是NULL或者空

SqlFunc.IsNullOrEmpty(object thisValue)
//可以取反
!SqlFunc.IsNullOrEmpty(object thisValue)

7.3 判段不是NULL

SqlFunc.HasValue(object thisValue)
//或者
it.xx!=null // xx is not null
it.xx==null //xx is null

7.4 按位与按位或

SqlFunc.BitwiseAnd  &
SqlFunc.BitwiseInclusiveOR |

7.5 判段大于0并且不等于NULL

SqlFunc.HasNumber(object thisValue)

7.6 模糊查询 like %@p%

SqlFunc.Contains(string thisValue, string parameterValue)
//也可以使用 .Where(it=>it.Name.Contains("a"));

7.7 模糊查询 not like %@p%

!SqlFunc.Contains(string thisValue, string parameterValue) //前面加个!

In单列操作

thisValue=new string[]{1,2,3}

paramterValie =it.Id

生成的Sql就是 id in (1,2,3)

//非参数化in 支持数量无上限
SqlFunc.ContainsArray(object[] thisValue, string parameterValue)
//参数化in ，特殊类型适合用这个兼容性好
SqlFunc.ContainsArrayUseSqlParameters(object[] thisValue, string parameterValue)
 
//原生方法语法糖：
 .Where(it=>数组变量.Contains(it.Id));  // in 
 .Where(it=>!Array.Contains(it.Id));// not in
  
//字符串类型 varchar和nvarchar (默认varchar来保证性能)
 NameList.Contains(it.Name,true) //true和false来控制是varchar还是nvarchar

In多列操作 51.4.67-preview04

Where(it => list.Any(s => s.Id == it.Id && s.Name == it.Name))

模糊查询 like @p%

SqlFunc.StartsWith(object thisValue, string parameterValue) //查询字符串开头

模糊查询 like %@p

SqlFunc.EndsWith(object thisValue, string parameterValue)

等于


SqlFunc.Equals(object thisValue, object parameterValue)

等于(支持 is null 的参数)


//用例：1
SqlFunc.EqualsNull(it.Name,null) 
//SQL: it.Name is null
 
//用例：2
SqlFunc.EqualsNull(it.Name,"a") 
//SQL:  it.Name='a'

范围判段

SqlFunc.Between(object value, object start, object end)

字符串逗号分割后是否存在提定的值 (5.1.3.51-preview01)

SqlFunc.SplitIn("1,2,3,4","5") //不存在5返回false
SqlFunc.SplitIn("1,2,3,4","1") //存在1返回true
 
//相关功能：
//如果是合并用子查询
//Names=SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")

八、子查询( IN、Exists、列逗号拼接 ) 📯

文档：子查询 - SqlSugar 5x - .NET

九、导航函数 🔗

9.1 一对多和多对多

需要实体配置导航

注意：不需要 .Includes 就能使用导航函数

//Count可以用到Where或者Select中
//x.导航对象.Count()//返回导航数量
//x.导航对象.Count(z=>z.id==1) //可以加条件
Where(x=>x.books.Count()>1)
Select(it=>new { count =>x.books.Count()})//5.0.9.8-preview01支持了在Select中用Count
 
//Any  
//x.导航对象.Any()//是否存在
//x.导航对象.Any(z=>z.id==1) //可以加条件
Where(x=>x.books.Any())

9.2 一对一

需要实体配置导航

//注意：不需 Includes 就可以使用
Where(x=>SqlFunc.Exists(x.SchoolA.Id))//查询存在一对一的主表数据
Where(x=>SqlFunc.Exists(x.SchoolA.Id,List<IConditionalModel>))//查询存在一对一的主表数据

十、开窗函数🕹️

5.1.1版本支持，也要数据库支持


  count = SqlFunc.RowCount(),// count (1) over() 
  max= SqlFunc.RowMax(it.num),// max(num) over() 
  min= SqlFunc.RowMin(it.num),// min(num) over() 
  avg= SqlFunc.RowAvg(it.num),// avg(num) over() 
  index = SqlFunc.RowNumber(it.Id), // row_number() over( order by a.`Id`)
  index = SqlFunc.RowNumber(it.Id,it.Name)//  row_number() over( partition by name order by a.`Id`)
  index = SqlFunc.Rank //和rownumber类似用法
   
  //倒序用法        
  index = SqlFunc.RowNumber(SqlFunc.Desc(it.Id)) //DESC  
   
  //多字段 
  //partition by id,name
  //orderby id asc name desc 
  index = SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc " ,$"{it.Id},{it.Name}");
   
  //如果要用到Where中一般要结合MegerTable进行使用，用例如下
  Select(it=>new 
  {
       index=SqlFunc.RowNumber(it.Id),
       name=it.name
   })
 .MergeTable()
 .Where(it=>it.Index=1).ToList()

十一、JSON函数/数组函数📋

支持JObject JArray ，实体，集合等类型
函数名说明兼容
SqlFunc.JsonLike

模糊查询，兼容所有库，性能一般适合小数据处理

SqlFunc.JsonLike(it.JsonObj,"a")

等于Like '%a%'
支持所有数据库
SqlFunc.JsonField

注意：大小写要一样

(1)查询Id的值{id:1}

SqlFunc.JsonField(it.JsonObj,

"id"

//返回1

(2)多层级查询，查询id 的值 {obj:{id:"a"}}

SqlFunc.JsonField(it.JsonObj,

"obj"

"id"

//返回a

如果不支持请升级

支持 PostgreSQL

支持 SqlServer2017+

支持 MySql (只能字段)

支持 Oracle (只能字段)

Sqlite升级到：5.1.4.148

支持 Sqlite

SqlFunc.JsonIndex

需要升级到:5.1.4.113-preview2+

获取json数组的索引对象

SqlFunc.JsonIndex(it.JsonArray,0)

如果是['a','c'] 那么返回a

PostgreSQL

MySql

SqlServer2017
SqlFunc.JsonParse转成JSON类型支持PostgreSQL
SqlFunc.JsonContainsFieldName

第一层是否存在id （如果多层级需要结合JsonField）

SqlFunc.JsonField(

"{id:1}"

"id"

//true

支持PostgreSQL
SqlFunc.JsonArrayAny(it.Json, "a")

需要升级到：5.1.3.36

["a","b","c"] 这种数组里面是否存在字符串 a

注意：如果是数字可以 1或者"1" 都试一下

也就是 [1,2] 参数用int类型

也就是 ["1","2"] 参数用string类型

注意：如果是多个值可以结合【动态表达式】

SQLSUGAR JSON 函数多值过滤用例 - SqlSugar - .Net果糖网

支持PostgreSQL

支持MySql

支持SqlServer(最新版本)

SqlFunc.JsonListObjectAny(it.json, "Name", "a")

需要升级到：5.1.3.36

[{"name":"a"},{"name":"b"}] 集合中是否存在 name=a的一项

注意：数字和字符串差异

注意：如果是多个值可以结合【动态表达式】

SQLSUGAR JSON 函数多值过滤用例 - SqlSugar - .Net果糖网

支持PostgreSQL

支持MySql

支持SqlServer(最新版本)
SqlFunc.JsonArrayLength(it.Json)[1,2,3]获取数组长度
支持PostgreSQL

SqlSugar 5.1.4.115

支持MySql

支持 SqlServer

十二、其他函数📇

12.1 字符串相加函数

除了SqlServer外很多库对+不友好可以使用

SqlFunc.MergeString
//字符串拼接 字段拼接
//MYSQL会生成  (concat(@MethodConst0,`Name`)

12.2 随机数函数


SqlFunc.GetRandom

12.3 获取字符串长度

SqlFunc.Length(object value)

12.4 获取指定字符在字符串中的位置


 SqlFunc.CharIndex
 // SQL :(CHARINDEX ([Name],@MethodConst0)

12.5 函数中用SQL

 var s2 = db.Queryable<Student>()
 .Select(it => new { 
  
               id = it.Id, 
               rowIndex=SqlFunc.MappingColumn(default(int), " row_number() over(order by id)") 
                             
             }).ToList();
 
 //SELECT  
         [ID] AS [id] , 
         row_number() over(order by id) AS [rowIndex]  
                       
    FROM [STudent]
     
 //rowIndex= 变成了 AS [rowIndex] 
 //SqlFunc.MappingColumn(it.Id, " row_number() over(order by id)")  变成了  row_number() over(order by id)

如果还有不支持的可以写字符串


db.Queryable<Student>().Where("id=@id",new{id=1}).Select("id,name").ToList()

12.6 补全PadLeft

SqlFunc.PadLeft(..)

12.7 向上取整和向下取整

SqlFunc.Floor(..)
SqlFunc.Ceil(..)

** .NET开源 ORM 框架 SqlSugar 系列**

【开篇】.NET开源 ORM 框架 SqlSugar 系列
【入门必看】.NET开源 ORM 框架 SqlSugar 系列
【实体配置】.NET开源 ORM 框架 SqlSugar 系列
【Db First】.NET开源 ORM 框架 SqlSugar 系列
【Code First】.NET开源 ORM 框架 SqlSugar 系列
【数据事务】.NET开源 ORM 框架 SqlSugar 系列
【连接池】.NET开源 ORM 框架 SqlSugar 系列
【查询目录】.NET开源 ORM 框架 SqlSugar 系列
【查询基础】.NET开源 ORM 框架 SqlSugar 系列
【排序用法】.NET开源 ORM 框架 SqlSugar 系列
【分组去重】.NET开源 ORM 框架 SqlSugar 系列
【联表查询】.NET开源 ORM 框架 SqlSugar 系列
【导航查询】.NET开源 ORM 框架 SqlSugar 系列
【子查询】.NET开源 ORM 框架 SqlSugar 系列
【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列
【配置查询】.NET开源 ORM 框架 SqlSugar 系列
【并集查询】.NET开源 ORM 框架 SqlSugar 系列
【树型查询】.NET开源 ORM 框架 SqlSugar 系列
【表格查询】.NET开源 ORM 框架 SqlSugar 系列

❤️关注、👍点赞、🔶收藏 💞

标签： .net 开源

本文转载自: https://blog.csdn.net/mss359681091/article/details/144356680
版权归原作者 Microi风闲 所有，如有侵权，请联系我们删除。

【查询函数】.NET开源ORM框架 SqlSugar 系列

一、基本用法 💎

二、C#函数 🔎

三、逻辑函数 🎡

3.1 case when

3.2 IsNulll

四、时间函数 🕰️

4.1 是否是同一天

4.2 是否是同一月

4.3 是否是同一年

4.4 是否是同一时间

4.5 在当前时间加一定时间

4.6 在当前时间加N天

4.7 获取当前时间的年、月、天、小时、分钟、秒或者毫秒

4.8 DateDiff 计算时间差

4.9 周数、周几

五、聚合函数 🥏

六、格式转换截取替换等 🪡

6.1 四舍五入 精度 (5.0.4)

6.2 绝对值函数 (5.0.4)

6.3 截取字符串

6.4 替换字符串

6.5 转小写

6.6 转大写

6.7 去前后空格

6.8 类型转换

6.9 指定位置替换

七、BOOL返回值函数 🎭

7.1 字符串比大小，字符串比较，字符串对比

7.2 判段是NULL或者空

7.3 判段不是NULL

7.4 按位与 按位或

7.5 判段大于0并且不等于NULL

7.6 模糊查询 like %@p%

7.7 模糊查询 not like %@p%

八、子查询( IN、Exists、列逗号拼接 ) 📯

九、导航函数 🔗

9.1 一对多和多对多

9.2 一对一

十、开窗函数🕹️

十一、JSON函数/数组函数📋

十二、其他函数📇

12.1 字符串相加函数

12.2 随机数函数

12.3 获取字符串长度

12.4 获取指定字符在字符串中的位置

12.5 函数中用SQL

12.6 补全PadLeft

12.7 向上取整和向下取整

发表评论

“【查询函数】.NET开源ORM框架 SqlSugar 系列”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

6.1 四舍五入精度 (5.0.4)

7.4 按位与按位或