SqlSugar的查询函数SqlFunc

用法

我们可以使用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)

 

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

 

IsNulll4.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()

 

是否是同一天

SqlFunc.DateIsSame(DateTime date1, DateTime date2)

 

//例子：查询今天 今日 当天

SqlFunc.DateIsSame(it.CreateTime,DateTime.Now)

 

是否是同一月

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

 

是否是同一年

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

 

是否是同一时间 （dataType 可以是年、月、天、小时、分钟、秒和毫秒）

SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType)

//本月 本年  本天 都可以用这个函数

 

在当前时间加一定时间（dataType 可以是年、月、天、小时、分钟、秒和毫秒）

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

 

在当前时间加N天

SqlFunc.DateAdd(DateTime date, int addValue)

 

//也可以用

it.CreateTime.AddDasy(1)

 

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

SqlFunc.DateValue(DateTime date, DateType dataType)

 

//也可以用

it.CreateTime.Day

 

DateDiff 计算时间差( 已知MSSQL支持)

 SqlFunc.DateDiff(type,DateTime.Now,DateTime.Now.AddDays(1)) 

 //结果等于1 ，相差1天，小的时间在前面，大的时间在后面

 //生成的SQL

 DATEDIFF(day,@MethodConst1, (DATEADD(Day,@MethodConst4,@MethodConst3)) )

  

  

 //也可以

 (时间-时间).TotalDays

 

周数、周几

 //周几

 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) //去重统计数量

 

 

格式转换截取替换等

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

 

 

四舍五入 精度  (5.0.4)

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

 

绝对值函数 (5.0.4)

 SqlFunc.Abs(it.Price)

 

截取字符串

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

 

替换字符串

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

 

转小写

SqlFunc.ToLower(object thisValue)

 

转大写

SqlFunc.ToUpper(object thisValue)

 

去前后空格

SqlFunc.Trim(object thisValue)

 

类型转换

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)

指定位置替换

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

 

BOOL返回值函数

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

(int)(object)it.str1>(int)(object)str2

//生成的sql

//it.str1>@str2

 

//或者用函数 较高版本支持

SqlFunc.GreaterThan

SqlFun.LessThan

 

判段是NULL或者空

SqlFunc.IsNullOrEmpty(object thisValue)

//可以取反

!SqlFunc.IsNullOrEmpty(object thisValue)

 

判段不是NULL 

SqlFunc.HasValue(object thisValue)

//或者

it.xx!=null // xx is not null

it.xx==null //xx is null

 

按位与 按位或

SqlFunc.BitwiseAnd  &

SqlFunc.BitwiseInclusiveOR |

 

判段大于0并且不等于NULL

SqlFunc.HasNumber(object thisValue)

 

模糊查询 like %@p%

SqlFunc.Contains(string thisValue, string parameterValue)

也可以使用 .Where(it=>it.Name.Contains("a"));

 

模糊查询 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、列逗号拼接 )

文档：https://www.donet5.com/Home/Doc?typeId=2231

 

导航函数

一对多和多对多

需要实体配置导航

注意：不需要  .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())

一对一

需要实体配置导航

//注意：不需 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.RowNumber(SqlFunc.Desc(it.Id)) //DESC  

   

  //多字段 5.1.2-preview01 一定要升级

  index  =  SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ");

   

  //如果要用到Where中一般要结合MegerTable进行使用，用例如下

  Select(it=>new 

  {

       index=SqlFunc.RowNumber(it.Id),

       name=it.name

   })

 .MergeTable()

 .Where(it=>it.Index=1).ToList()

 

 

JSON函数/数组函数

需要升级到 5.1.2.8+

支持JObject JArray ，实体 ，集合等类型

[SugarColumn(IsJson=true)]//添加特性

public List<Order> JsonObj{get;set;}

 

函数名	说明	兼容
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     SqlSugar 5.1.2.23： 支持 SqlServer2017 支持 MySql （只支持字段）     SqlSugar 5.1.4.62：  支持 Oracle (只支持字段）
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类型	支持PostgreSQL 支持MySql 支持SqlServer(最新版本)
SqlFunc.JsonListObjectAny(it.json, "Name", "a") 需要升级到：5.1.3.36	[{"name":"a"},{"name":"b"}] 集合中是否存在 name=a的一项 注意：数字和字符串差异	支持PostgreSQL 支持MySql 支持SqlServer(最新版本)
SqlFunc.JsonArrayLength(it.Json)	[1,2,3]获取数组长度	支持PostgreSQL SqlSugar 5.1.4.115 支持MySql 支持 SqlServer

 

其他函数

字符串相加函数

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

SqlFunc.MergeString

//字符串拼接 字段拼接

//MYSQL会生成  (concat(@MethodConst0,`Name`)

随机数函数 

SqlFunc.GetRandom

获取字符串长度

SqlFunc.Length(object value)

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

 SqlFunc.CharIndex

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

函数中用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()

补全PadLeft

SqlFunc.PadLeft(..)

向上取整和向下取整

SqlFunc.Floor(..)

SqlFunc.Ceil(..)