Translations

Entity Framework Core allows providers to translate query expressions to SQL for database evaluation. For example, MySQL supports regular expression operations, and the MySQL EF Core provider automatically translates .NET's Regex.IsMatch to use this feature. Since evaluation happens at the server, table data doesn't need to be transferred to the client (saving bandwidth), and in some cases indexes can be used to speed things up. The same C# code on other providers will trigger client evaluation.

Date and time functions

.NET	SQL
DateTime.Now	CURRENT_TIMESTAMP()
DateTime.Today	CURDATE()
DateTime.UtcNow	UTC_TIMESTAMP()
DateTime.AddDays(value)	DATE_ADD(date,INTERVAL expr DAY)
DateTime.AddHours(value)	DATE_ADD(date,INTERVAL expr HOUR)
DateTime.AddMinutes(value)	DATE_ADD(date,INTERVAL expr MINUTE)
DateTime.AddMonths(value)	DATE_ADD(date,INTERVAL expr MONTH)
DateTime.AddSeconds(value)	DATE_ADD(date,INTERVAL expr SECOND)
DateTime.AddYears(value)	DATE_ADD(date,INTERVAL expr YEAR)
DateTime.Date	CONVERT(expr,DATE))
DateTime.Day	EXTRACT(DAY FROM date)
DateTime.DayOfYear	DAYOFYEAR(date)
DateTime.TimeOfDay	CAST(expr AS TIME)
DateTime.Hour	EXTRACT(HOUR FROM date)
DateTime.Minute	EXTRACT(MINUTE FROM date)
DateTime.Month	EXTRACT(MONTH FROM date)
DateTime.Second	EXTRACT(SECOND FROM date)
DateTime.Milliseconds	EXTRACT(MICROSECOND FROM date)
DateTime.Year	EXTRACT(YEAR FROM date)
TimeSpan.Hours	EXTRACT(HOUR FROM date)
TimeSpan.Minutes	EXTRACT(MINUTE FROM date)
TimeSpan.Seconds	EXTRACT(SECOND FROM date)
TimeSpan.Milliseconds	EXTRACT(MICROSECOND FROM date)

DateDiff functions

.NET	SQL
EF.Functions.DateDiffYear(startDate, endDate)	TIMESTAMPDIFF(YEAR,datetime_expr1,datetime_expr2)
EF.Functions.DateDiffMonth(startDate, endDate)	TIMESTAMPDIFF(MONTH,datetime_expr1,datetime_expr2)
EF.Functions.DateDiffDay(startDate, endDate)	TIMESTAMPDIFF(DAY,datetime_expr1,datetime_expr2)
EF.Functions.DateDiffHour(startDate, endDate)	TIMESTAMPDIFF(HOUR,datetime_expr1,datetime_expr2)
EF.Functions.DateDiffMinute(startDate, endDate)	TIMESTAMPDIFF(MINUTE,datetime_expr1,datetime_expr2)
EF.Functions.DateDiffSecond(startDate, endDate)	TIMESTAMPDIFF(SECOND,datetime_expr1,datetime_expr2)
EF.Functions.DateDiffMicrosecond(startDate, endDate)	TIMESTAMPDIFF(MICROSECOND,datetime_expr1,datetime_expr2)

Numeric functions

.NET	SQL
EF.Functions.Degrees(radians)	DEGREES(X)
EF.Functions.Radians(degrees)	RADIANS(X)
Math.Abs(value)	ABS(X)
Math.Acos(d)	ACOS(X)
Math.Asin(d)	ASIN(X)
Math.Atan(d)	ATAN(X)
Math.Atan2(y, x)	ATAN2(Y,X)
Math.Ceiling(d)	CEILING(X)
Math.Cos(d)	COS(X)
Math.Exp(d)	EXP(X)
Math.Floor(d)	FLOOR(X)
Math.Log(d)	LOG(X)
Math.Log10(d)	LOG10(X)
Math.Max(x, y)	GREATEST(value1,value2,...)
Math.Min(x, y)	LEAST(value1,value2,...)
Math.Pow(x, y)	POW(X,Y)
Math.Round(d)	ROUND(X)
Math.Round(d, decimals)	ROUND(X,D)
Math.Sin(a)	SIN(X)
Math.Sign(value)	SIGN(X)
Math.Sqrt(d)	SQRT(X)
Math.Tan(a)	TAN(X)
Math.Truncate(d)	TRUNCATE(X,D)

String functions

.NET	SQL
EF.Functions.Like(matchExpression, pattern)	expr LIKE pat [ESCAPE 'escape_char']
EF.Functions.Like(matchExpression, pattern, escapeCharacter)	expr LIKE pat [ESCAPE 'escape_char']
string.IsNullOrWhiteSpace(value)	ISNULL(expr), TRIM([remstr FROM] str)
string.Concat(value1, value2, ...)	CONCAT(str1,str2,...)
stringValue.Contains(value)	expr LIKE pat [ESCAPE 'escape_char'], LOCATE(substr,str)
stringValue.EndsWith(value)	expr LIKE pat [ESCAPE 'escape_char'], RIGHT(str,len)
stringValue.FirstOrDefault()	SUBSTRING(str,pos,len)
stringValue.IndexOf(value)	LOCATE(substr,str)
stringValue.IndexOf(value, startIndex)	LOCATE(substr,str,pos)
stringValue.LastOrDefault()	SUBSTRING(str,pos,len), CHAR_LENGTH(str)
stringValue.Length	CHAR_LENGTH(str)
stringValue.PadLeft(length)	LPAD(str,len,padstr)
stringValue.PadLeft(length, char)	LPAD(str,len,padstr)
stringValue.PadRight(length)	RPAD(str,len,padstr)
stringValue.PadRight(length, char)	RPAD(str,len,padstr)
stringValue.Replace(oldValue, newValue)	REPLACE(str,from_str,to_str)
stringValue.StartsWith(value)	expr LIKE pat [ESCAPE 'escape_char'], LEFT(str,len)
stringValue.Substring(startIndex, length)	SUBSTRING(str,pos,len)
stringValue.ToLower()	LOWER(str)
stringValue.ToUpper()	UPPER(str)
stringValue.Trim()	TRIM([remstr FROM] str)
stringValue.Trim(trimChar)	TRIM([remstr FROM] str)
stringValue.TrimEnd()	TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
stringValue.TrimEnd(trimChar)	TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
stringValue.TrimStart()	TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
stringValue.TrimStart(trimChar)	TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
Regex.IsMatch(stringValue, "^A+")	expr REGEXP pat

Blob functions

.NET	SQL
bytesArray.ElementAt(index)	ASCII(SUBSTRING(str, start, extract))

Miscellaneous functions

.NET	SQL
Guid.NewGuid()	UUID()
EF.Functions.Hex(value)	HEX(str)
EF.Functions.Unhex(value)	UNHEX(str)