1.7 Query‐Sql functions

1、C# function

Supports some common C# functions

//Where(it=>it.Name.Contains("a"))

. ToString  .Contains  .Length.ToLower  .ToUpper  .ToSubstring

.Equals    .HasValue  .Replace .EndsWith  .StartsWith  .Trim

.hasvalue.value.adddays (AddHours, etc.) and commonly used Convert.ToInt32, etc

Ternary Expression  xx??0   time.DayOfWeek time.Date Indicates the time.Day, (time-time).TotalDays

2、SqlSugar function

2.1 Logical function

Case When

 SqlFunc.IF(st.Id > 1)
     .Return(st.Id)
     .ElseIF(st.Id == 1)
     .Return(st.SchoolId).End(st.Id)

IsNull

SqlFunc.IsNull(it.Id, 0)// If null, 0

//it.Id?? 0

// If it is equal to handling NULL look at EqualsNull below
//ifnull

2.2 DateTime function

// yyyy-MM-dd is supported
it.CreateTime.ToString("yyyy-MM-dd")

//1. SqlServer 2012 and later use SQL to view the version: select@@version
//5.0.6+ ToString perfectly supports all yyyy mmm ddd formats


//SqlServer2012 below
// 5.0.6+ Only three formats are supported: yyyy-MM-dd, yyyy-MM-dd, and YYYY-MM-DD hh:mm:ss
// ToString("23") convert with digits (varchar(20), time, 23)


//2, MySql and Sqlite
// Support yyyy-MM-dd, yyyy-MM-dd, and YYYY-MM-DD hh:mm:ss
// Native conversion can be used: it.CreateTime.ToString("%Y-%m")// As long as there is a % sign, the native conversion will be entered
/ / https://www.cnblogs.com/shuilangyizu/p/8036620.html table

PgSql and Oracle perfectly support all C# yyyy mmm ddd format conversions
Get database time 4.6.0.1

SqlFunc.GetDate();  // Database server time
Note: The Sqlfunc method can only be used in expressions, if db.GetDate() is used in non-expressions.
Whether it is the same day

SqlFunc.DateIsSame(DateTime date1, DateTime date2)

// Example: Query Today Today
SqlFunc.DateIsSame(it.CreateTime,DateTime.Now)
Is it the same month?

it.CreateTime.ToString("yyyy-MM")==DateTime.Now.ToString("yyyy-MM") // Date formatting
Is it the same year?

it.CreateTime.Year==DateTime.Now.Year
Whether it is the same time (dataType can be years, months, days, hours, minutes, seconds, and milliseconds)

SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType)
// This function can be used on any day of this month or year
Add a certain time to the current time (dataType can be years, months, days, hours, minutes, seconds, and milliseconds)

SqlFunc.DateAdd(DateTime date, int addValue, DateType dataType)
Add N days to the current time

SqlFunc.DateAdd(DateTime date, int addValue)

// Can also be used
it.CreateTime.AddDasy(1)
Gets the year, month, day, hour, minute, second, or millisecond of the current time

SqlFunc.DateValue(DateTime date, DateType dataType)

// Can also be used
it.CreateTime.Day
DateDiff calculation time difference (known MSSQL support)

SqlFunc.DateDiff(type,DateTime.Now,DateTime.Now.AddDays(1))
// The result is equal to 1, the difference is 1 day, the small time is in the front, the large time is in the back
// Generated SQL
DATEDIFF(day,@MethodConst1, (DATEADD(Day,@MethodConst4,@MethodConst3)) )


// That's OK
(time-time).TotalDays
Number and day of the week

// What day of the week
SqlFunc.DateValue(DateTime.Now, DateType.Weekday);
// The week of the year
SqlFunc.WeekOfYear

2.3 Aggregate function

SqlFunc.AggregateSum<TResult>(TResult thisValue) // Sum
SqlFunc. AggregateSumNoNull < TResult > (TResult thisValue) / / sum (new filter support null)
SqlFunc.AggregateAvg<TResult>(TResult thisValue)// Average value
SqlFunc.AggregateMin(TResult thisValue) // Minimum
SqlFunc.AggregateMax<TResult>(TResult thisValue) // Max
SqlFunc.AggregateCount<TResult>(TResult thisValue)// Statistics quantity
SqlFunc. AggregateDistinctCount < TResult > (TResult thisValue) / / to statistical weight

2.4 format conversion

time conversion see the title [DateTime function]

Rounding accuracy (5.0.4)

SqlFunc.Round(it.Price,2) // Two decimal places
Absolute value function (5.0.4)

SqlFunc.Abs(it.Price)
Intercept string

SqlFunc.Substring(object value, int index, int length)// Intercepts a string
Replacement string

SqlFunc.Replace(object value, string oldChar, string newChar)
Convert to lower case

SqlFunc.ToLower(object thisValue)
uppercase

SqlFunc.ToUpper(object thisValue)
unspace

SqlFunc.Trim(object thisValue)
Type conversion

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)
Designated location substitution

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

2.5 BOOL Return value function

String ratio size, string comparison, string comparison

(int)(object)it.str1>(int)(object)str2
// Generated sql
//it.str1>@str2

// or support with a later version of the function
SqlFunc.GreaterThan
SqlFun.LessThan
The sentence is NULL or empty

SqlFunc.IsNullOrEmpty(object thisValue)
// Can be reversed
! SqlFunc.IsNullOrEmpty(object thisValue)
Sentence segment is not NULL

SqlFunc.HasValue(object thisValue)
// or
it.xx! =null // xx is not null
it.xx==null //xx is null
Bitwise and bitwise or

SqlFunc.BitwiseAnd  &
SqlFunc.BitwiseInclusiveOR |
Sentence segment greater than 0 and not equal to NULL

SqlFunc.HasNumber(object thisValue)
The fuzzy query is like %@p%

SqlFunc.Contains(string thisValue, string parameterValue)
// Can also use.Where(it=> it.name.contains ("a"));
Fuzzy query is not like %@p%

! SqlFunc.Contains(string thisValue, string parameterValue) // Prefix!
In single-column operation

thisValue=new string[]{1,2,3}

paramterValie =it.Id

The resulting Sql is id in (1,2,3)

// There is no upper limit on the number of non-parameterized in
SqlFunc.ContainsArray(object[] thisValue, string parameterValue)
// Parameterized in, special types suitable for this compatibility is good
SqlFunc.ContainsArrayUseSqlParameters(object[] thisValue, string parameterValue)

// Native method syntax Sugar:
.where (it=> Array variable. Contains(it.Id));   // in
.Where(it=>! Array.Contains(it.Id)); // not in

// String types varchar and nvarchar (default varchar to ensure performance)
NameList.Contains(it.Name,true) //true and false to control varchar or nvarchar
Operation 51.4.67-preview04 In multiple columns

Where(it => list.Any(s => s.Id == it.Id && s.Name == it.Name))
Fuzzy query like @p%

SqlFunc.StartsWith(object thisValue, string parameterValue) // Start of the query string
A fuzzy query is like %@p

SqlFunc.EndsWith(object thisValue, string parameterValue)
Equal to

SqlFunc.Equals(object thisValue, object parameterValue)
Equal (is null argument supported)

// Use case: 1
SqlFunc.EqualsNull(it.Name,null)
//SQL: it.Name is null

// Use case: 2
SqlFunc.EqualsNull(it.Name,"a")
//SQL:  it.Name='a'
Range section

SqlFunc.Between(object value, object start, object end)
Whether the specified value exists after the comma separation (5.1.3.51-preview01)

SqlFunc.SplitIn("1,2,3,4","5") // No 5 Return false
SqlFunc.SplitIn("1,2,3,4","1") // Return true if 1 exists

// Related functions:
// Subquery if merge
//Names=SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")

2.5 Window Functions

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 // Similar to rownumber

// In reverse order
index = SqlFunc.RowNumber(SqlFunc.Desc(it.Id)) //DESC

// Multiple fields
//partition by id,name
//orderby id asc name desc
index = SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc " ,$"{it.Id},{it.Name}");

// If you want to use Where, it is generally used in combination with MegerTable, for example
Select(it=>new
{
index=SqlFunc.RowNumber(it.Id),
name=it.name
})
.MergeTable()
.Where(it=>it.Index=1).ToList()