用法
我们可以使用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}
(2)多层级查询,查询id 的值 {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数组的索引对象
如果是['a','c'] 那么返回a |
PostgreSQL MySql SqlServer2017 |
||
SqlFunc.JsonParse | 转成JSON类型 | 支持PostgreSQL | ||
SqlFunc.JsonContainsFieldName |
第一层是否存在id (如果多层级需要结合JsonField)
|
支持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(..)
|