SqlFunc查询函数

一、常用的C#函数

支持一些常用的C#函数

.ToString()
.Contains()
.Length()
.ToLower()
.ToUpper()  
.ToSubstring()
.Equals()
.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

三、时间函数

• 时间格式化

  //都支持常用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) //去重统计数量

五、格式转换截取替换等

SqlFunc.Round(it.Price,2) //四舍五入 精度 (5.0.4),两位小数
 
SqlFunc.Abs(it.Price)//绝对值函数 (5.0.4)
    
SqlFunc.Substring(object value, int index, int length)//字符串截取  
  
SqlFunc.Replace(object value, string oldChar, string newChar)//替换字符串
    
SqlFunc.Stuff(string sourceString, int start, int length, string AddString)//指定位置替换
    
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)    

六、BOOL返回值函数

SqlFunc.GreaterThan//字符串比大小，字符串比较，字符串对比
SqlFun.LessThan
    
SqlFunc.IsNullOrEmpty(object thisValue)//判段是NULL或者空
    
SqlFunc.HasValue(object thisValue)//判段不是NULL 
 
SqlFunc.BitwiseAnd  &         //按位与 按位或
SqlFunc.BitwiseInclusiveOR |    
    
SqlFunc.HasNumber(object thisValue) //判段大于0并且不等于NULL
    
SqlFunc.Contains(string thisValue, string parameterValue)    //模糊查询 like %@p%
    
SqlFunc.ContainsArray(object[] thisValue, string parameterValue)    //非参数化in 支持数量无上限
    
SqlFunc.ContainsArrayUseSqlParameters(object[] thisValue, string parameterValue) //参数化in ，特殊类型适合用这个兼容性好
    
SqlFunc.StartsWith(object thisValue, string parameterValue) //查询字符串开头   ,模糊查询 like @p%
    
SqlFunc.EndsWith(object thisValue, string parameterValue)//模糊查询 like %@p
    
SqlFunc.Equals(object thisValue, object parameterValue)// 等于  
    
SqlFunc.EqualsNull(it.Name,null)     //SQL: it.Name is null
    
SqlFunc.EqualsNull(it.Name,"a")     //SQL:  it.Name='a'
    
SqlFunc.Between(object value, object start, object end)    //范围判段
    
SqlFunc.SplitIn("1,2,3,4","5") //不存在5返回false
SqlFunc.SplitIn("1,2,3,4","1") //存在1返回true      

七、开窗函数

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

八、其他函数

1、字符串相加函数

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

2、随机数函数

SqlFunc.GetRandom

3、向上取整和向下取整

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

4、补全PadLeft

SqlFunc.PadLeft(..)