用法
我们可以使用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等) .Date 和 常用的 Convert.ToInt32 等
三元, xx??0
逻辑函数
三元判段 ，相当于 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-ddit.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)‘ |
获取当前时间的年、月、天、小时、分钟、秒或者毫秒
SqlFunc.DateValue(DateTime date, DateType dataType) |
DateDiff 计算时间差( 已知MSSQL支持)
SqlFunc.DateDiff(type,DateTime.Now,DateTime.Now.AddDays(1)) //结果等于1 ，相差1天，小的时间在前面，大的时间在后面 //生成的SQL DATEDIFF(day,@MethodConst1, (DATEADD(Day,@MethodConst4,@MethodConst3)) ) |
聚合函数
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.GreaterThanSqlFun.LessThan |
判段是NULL或者空
SqlFunc.IsNullOrEmpty(object thisValue) |
判段不是NULL并且不是空
SqlFunc.HasValue(object thisValue) |
按位与 按位或
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 |
模糊查询 like @p%
SqlFunc.StartsWith(object thisValue, string parameterValue) //查询字符串开头 |
模糊查询 like %@p
SqlFunc.EndsWith(object thisValue, string parameterValue) |
等于
SqlFunc.Equals(object thisValue, object parameterValue) |
等于(支持 is null 的参数)
//用例：1SqlFunc.EqualsNull(it.Name,null) //SQL: it.Name is null//用例：2SqlFunc.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返回falseSqlFunc.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() index = SqlFunc.RowNumber(it.Id), // row_number() over( order by a. index = SqlFunc.RowNumber(it.Id,it.Name)// row_number() over( partition by name order by a. //倒序用法 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”}}
(3)查询第二个组数[1,2,3]
(4)查询数组第一个的id 的值 [ {id:1},{id:2}]
|
支持PostgreSQL
SqlSugar 5.1.2.23： 支持 SqlServer2017 支持 MySql （只支持字段） |
||||
| 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 |
其他函数
字符串相加函数
除了SqlServer外很多库对+不友好可以使用
SqlFunc.MergeString//字符串拼接 字段拼接//MYSQL会生成 (concat(@MethodConst0, |
随机数函数
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() |
自定义扩展函数
https://www.donet5.com/Home/Doc?typeId=1225
