我们可以使用SqlFunc这个类调用Sql函数,用法如下:
db.Queryable<Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList();
支持一些常用的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)
字符串比大小,字符串比较,字符串对比
(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, ",")
文档: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.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()
支持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 支持 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类型 注意:如果是多个值可以结合【动态表达式】 | 支持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)
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()
SqlFunc.PadLeft(..)
SqlFunc.Floor(..) SqlFunc.Ceil(..)
https://www.donet5.com/Home/Doc?typeId=1225
自定义函数
2016 © donet5.comApache Licence 2.0