ContextQMD
Back to Entityframework

Function Mappings - Microsoft SQL Server Database Provider - EF Core

entity-framework/core/providers/sql-server/functions.md

latest18.8 KB
Original Source

Function Mappings of the Microsoft SQL Server Provider

This page shows which .NET members are translated into which SQL functions when using the SQL Server provider.

Aggregate functions

.NETSQLAdded in
EF.Functions.StandardDeviationSample(group.Select(x => x.Property))STDEV(Property)
EF.Functions.StandardDeviationPopulation(group.Select(x => x.Property))STDEVP(Property)
EF.Functions.VarianceSample(group.Select(x => x.Property))VAR(Property)
EF.Functions.VariancePopulation(group.Select(x => x.Property))VARP(Property)
group.Average(x => x.Property)AVG(Property)
group.Count()COUNT(*)
group.LongCount()COUNT_BIG(*)
group.Max(x => x.Property)MAX(Property)
group.Min(x => x.Property)MIN(Property)
group.Sum(x => x.Property)SUM(Property)
string.Concat(group.Select(x => x.Property))STRING_AGG(Property, N'')
string.Join(separator, group.Select(x => x.Property))STRING_AGG(Property, @separator)

Binary functions

.NETSQLAdded in
bytes.Contains(value)CHARINDEX(@value, @bytes) > 0
bytes.ElementAt(i)SUBSTRING(@bytes, @i + 1, 1)
bytes.First()SUBSTRING(@bytes, 1, 1)
bytes.LengthDATALENGTH(@bytes)
bytes.SequenceEqual(second)@bytes = @second
bytes[i]SUBSTRING(@bytes, @i + 1, 1)
EF.Functions.DataLength(arg)DATALENGTH(@arg)

Conversion functions

.NETSQLAdded in
bytes.ToString()CONVERT(varchar(100), @bytes)
byteValue.ToString()CONVERT(varchar(3), @byteValue)
charValue.ToString()CONVERT(varchar(1), @charValue)
Convert.ToBoolean(value)CONVERT(bit, @value)
Convert.ToByte(value)CONVERT(tinyint, @value)
Convert.ToDecimal(value)CONVERT(decimal(18, 2), @value)
Convert.ToDouble(value)CONVERT(float, @value)
Convert.ToInt16(value)CONVERT(smallint, @value)
Convert.ToInt32(value)CONVERT(int, @value)
Convert.ToInt64(value)CONVERT(bigint, @value)
Convert.ToString(value)CONVERT(nvarchar(max), @value)
dateOnly.ToString()CONVERT(varchar(100), @dateOnly)
dateTime.ToString()CONVERT(varchar(100), @dateTime)
dateTimeOffset.ToString()CONVERT(varchar(100), @dateTimeOffset)
decimalValue.ToString()CONVERT(varchar(100), @decimalValue)
doubleValue.ToString()CONVERT(varchar(100), @doubleValue)
floatValue.ToString()CONVERT(varchar(100), @floatValue)
guid.ToString()CONVERT(varchar(36), @guid)
intValue.ToString()CONVERT(varchar(11), @intValue)
longValue.ToString()CONVERT(varchar(20), @longValue)
sbyteValue.ToString()CONVERT(varchar(4), @sbyteValue)
shortValue.ToString()CONVERT(varchar(6), @shortValue)
timeOnly.ToString()CONVERT(varchar(100), @timeOnly)
timeSpan.ToString()CONVERT(varchar(100), @timeSpan)
uintValue.ToString()CONVERT(varchar(10), @uintValue)
ulongValue.ToString()CONVERT(varchar(19), @ulongValue)
ushortValue.ToString()CONVERT(varchar(5), @ushortValue)

Date and time functions

.NETSQLAdded in
DateTime.NowGETDATE()
DateTime.TodayCONVERT(date, GETDATE())
DateTime.UtcNowGETUTCDATE()
dateTime.AddDays(value)DATEADD(day, @value, @dateTime)
dateTime.AddHours(value)DATEADD(hour, @value, @dateTime)
dateTime.AddMilliseconds(value)DATEADD(millisecond, @value, @dateTime)
dateTime.AddMinutes(value)DATEADD(minute, @value, @dateTime)
dateTime.AddMonths(months)DATEADD(month, @months, @dateTime)
dateTime.AddSeconds(value)DATEADD(second, @value, @dateTime)
dateTime.AddYears(value)DATEADD(year, @value, @dateTime)
dateTime.DateCONVERT(date, @dateTime)
dateTime.DayDATEPART(day, @dateTime)
dateTime.DayOfYearDATEPART(dayofyear, @dateTime)
dateTime.HourDATEPART(hour, @dateTime)
dateTime.MicrosecondDATEPART(microsecond, @dateTime) % 1000EF Core 10.0
dateTime.MillisecondDATEPART(millisecond, @dateTime)
dateTime.MinuteDATEPART(minute, @dateTime)
dateTime.MonthDATEPART(month, @dateTime)
dateTime.NanosecondDATEPART(nanosecond, @dateTime) % 1000EF Core 10.0
dateTime.SecondDATEPART(second, @dateTime)
dateTime.TimeOfDayCONVERT(time, @dateTime)
dateTime.YearDATEPART(year, @dateTime)
DateTimeOffset.NowSYSDATETIMEOFFSET()
DateTimeOffset.UtcNowSYSUTCDATETIME()
dateTimeOffset.AddDays(days)DATEADD(day, @days, @dateTimeOffset)
dateTimeOffset.AddHours(hours)DATEADD(hour, @hours, @dateTimeOffset)
dateTimeOffset.AddMilliseconds(milliseconds)DATEADD(millisecond, @milliseconds, @dateTimeOffset)
dateTimeOffset.AddMinutes(minutes)DATEADD(minute, @minutes, @dateTimeOffset)
dateTimeOffset.AddMonths(months)DATEADD(month, @months, @dateTimeOffset)
dateTimeOffset.AddSeconds(seconds)DATEADD(second, @seconds, @dateTimeOffset)
dateTimeOffset.AddYears(years)DATEADD(year, @years, @dateTimeOffset)
dateTimeOffset.DateCONVERT(date, @dateTimeOffset)
dateTimeOffset.DayDATEPART(day, @dateTimeOffset)
dateTimeOffset.DayOfYearDATEPART(dayofyear, @dateTimeOffset)
dateTimeOffset.HourDATEPART(hour, @dateTimeOffset)
dateTimeOffset.MicrosecondDATEPART(microsecond, @dateTimeOffset) % 1000EF Core 10.0
dateTimeOffset.MillisecondDATEPART(millisecond, @dateTimeOffset)
dateTimeOffset.MinuteDATEPART(minute, @dateTimeOffset)
dateTimeOffset.MonthDATEPART(month, @dateTimeOffset)
dateTimeOffset.NanosecondDATEPART(nanosecond, @dateTimeOffset) % 1000EF Core 10.0
dateTimeOffset.SecondDATEPART(second, @dateTimeOffset)
dateTimeOffset.TimeOfDayCONVERT(time, @dateTimeOffset)
dateTimeOffset.ToUnixTimeSeconds()DATEDIFF_BIG(second, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset)
dateTimeOffset.ToUnixTimeMilliseconds()DATEDIFF_BIG(millisecond, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset)
dateTimeOffset.YearDATEPART(year, @dateTimeOffset)
DateOnly.FromDateTime(dateTime)CONVERT(date, @dateTime)
dateOnly.AddDays(value)DATEADD(day, @value, @dateOnly)
dateOnly.AddMonths(months)DATEADD(month, @months, @dateOnly)
dateOnly.AddYears(value)DATEADD(year, @value, @dateOnly)
dateOnly.DayDATEPART(day, @dateOnly)
dateOnly.DayOfYearDATEPART(dayofyear, @dateOnly)
dateOnly.MonthDATEPART(month, @dateOnly)
dateOnly.YearDATEPART(year, @dateOnly)
dateOnly.DayNumberDATEDIFF(day, '0001-01-01', @dateOnly)EF Core 10.0
EF.Functions.AtTimeZone(dateTime, timeZone)@dateTime AT TIME ZONE @timeZone
EF.Functions.DateDiffDay(start, end)DATEDIFF(day, @start, @end)
EF.Functions.DateDiffHour(start, end)DATEDIFF(hour, @start, @end)
EF.Functions.DateDiffMicrosecond(start, end)DATEDIFF(microsecond, @start, @end)
EF.Functions.DateDiffMillisecond(start, end)DATEDIFF(millisecond, @start, @end)
EF.Functions.DateDiffMinute(start, end)DATEDIFF(minute, @start, @d2)
EF.Functions.DateDiffMonth(start, end)DATEDIFF(month, @start, @end)
EF.Functions.DateDiffNanosecond(start, end)DATEDIFF(nanosecond, @start, @end)
EF.Functions.DateDiffSecond(start, end)DATEDIFF(second, @start, @end)
EF.Functions.DateDiffWeek(start, end)DATEDIFF(week, @start, @end)
EF.Functions.DateDiffYear(start, end)DATEDIFF(year, @start, @end)
EF.Functions.DateFromParts(year, month, day)DATEFROMPARTS(@year, @month, @day)
EF.Functions.DateTime2FromParts(year, month, day, ...)DATETIME2FROMPARTS(@year, @month, @day, ...)
EF.Functions.DateTimeFromParts(year, month, day, ...)DATETIMEFROMPARTS(@year, @month, @day, ...)
EF.Functions.DateTimeOffsetFromParts(year, month, day, ...)DATETIMEOFFSETFROMPARTS(@year, @month, @day, ...)
EF.Functions.IsDate(expression)ISDATE(@expression)
EF.Functions.SmallDateTimeFromParts(year, month, day, ...)SMALLDATETIMEFROMPARTS(@year, @month, @day, ...)
EF.Functions.TimeFromParts(hour, minute, second, ...)TIMEFROMPARTS(@hour, @minute, @second, ...)
timeOnly.AddHours(value)DATEADD(hour, @value, @timeOnly)
timeOnly.AddMinutes(value)DATEADD(minute, @value, @timeOnly)
timeOnly.HourDATEPART(hour, @timeOnly)
timeOnly.IsBetween(start, end)@timeOnly >= @start AND @timeOnly < @end
timeOnly.MicrosecondDATEPART(microsecond, @timeOnly) % 1000EF Core 10.0
timeOnly.MillisecondDATEPART(millisecond, @timeOnly)
timeOnly.MinuteDATEPART(minute, @timeOnly)
timeOnly.NanosecondDATEPART(nanosecond, @timeOnly) % 1000EF Core 10.0
timeOnly.SecondDATEPART(second, @timeOnly)
timeSpan.HoursDATEPART(hour, @timeSpan)
timeSpan.MicrosecondDATEPART(microsecond, @timeSpan) % 1000EF Core 10.0
timeSpan.MillisecondsDATEPART(millisecond, @timeSpan)
timeSpan.MinutesDATEPART(minute, @timeSpan)
timeSpan.NanosecondDATEPART(nanosecond, @timeSpan) % 1000EF Core 10.0
timeSpan.SecondsDATEPART(second, @timeSpan)

Numeric functions

.NETSQLAdded in
double.DegreesToRadians(x)RADIANS(@x)
double.RadiansToDegrees(x)DEGREES(@x)
EF.Functions.Random()RAND()
Math.Abs(value)ABS(@value)
Math.Acos(d)ACOS(@d)
Math.Asin(d)ASIN(@d)
Math.Atan(d)ATAN(@d)
Math.Atan2(y, x)ATN2(@y, @x)
Math.Ceiling(d)CEILING(@d)
Math.Cos(d)COS(@d)
Math.Exp(d)EXP(@d)
Math.Floor(d)FLOOR(@d)
Math.Log(d)LOG(@d)
Math.Log(a, newBase)LOG(@a, @newBase)
Math.Log10(d)LOG10(@d)
Math.Max(x, y)GREATEST(@x, @y)EF Core 9.0
Math.Min(x, y)LEAST(@x, @y)EF Core 9.0
Math.Pow(x, y)POWER(@x, @y)
Math.Round(d)ROUND(@d, 0)
Math.Round(d, decimals)ROUND(@d, @decimals)
Math.Sign(value)SIGN(@value)
Math.Sin(a)SIN(@a)
Math.Sqrt(d)SQRT(@d)
Math.Tan(a)TAN(@a)
Math.Truncate(d)ROUND(@d, 0, 1)

[!TIP] In addition to the methods listed here, corresponding generic math implementations and MathF methods are also translated. For example, Math.Sin, MathF.Sin, double.Sin, and float.Sin all map to the SIN function in SQL.

String functions

.NETSQLAdded in
EF.Functions.Collate(operand, collation)@operand COLLATE @collation
EF.Functions.Contains(propertyReference, searchCondition)CONTAINS(@propertyReference, @searchCondition)
EF.Functions.Contains(propertyReference, searchCondition, languageTerm)CONTAINS(@propertyReference, @searchCondition, LANGUAGE @languageTerm)
EF.Functions.FreeText(propertyReference, freeText)FREETEXT(@propertyReference, @freeText)
EF.Functions.FreeText(propertyReference, freeText, languageTerm)FREETEXT(@propertyReference, @freeText, LANGUAGE @languageTerm)
EF.Functions.IsNumeric(expression)ISNUMERIC(@expression)
EF.Functions.Like(matchExpression, pattern)@matchExpression LIKE @pattern
EF.Functions.Like(matchExpression, pattern, escapeCharacter)@matchExpression LIKE @pattern ESCAPE @escapeCharacter
string.Compare(strA, strB)CASE WHEN @strA = @strB THEN 0 ... END
string.Concat(str0, str1)@str0 + @str1
string.IsNullOrEmpty(value)@value IS NULL OR @value LIKE N''
string.IsNullOrWhiteSpace(value)@value IS NULL OR @value = N''
string.Join(", ", new [] { x, y, z})CONCAT_WS(N', ', @x, @y, @z)EF Core 9.0
stringValue.CompareTo(strB)CASE WHEN @stringValue = @strB THEN 0 ... END
stringValue.Contains(value)@stringValue LIKE N'%' + @value + N'%'
stringValue.EndsWith(value)@stringValue LIKE N'%' + @value
stringValue.FirstOrDefault()SUBSTRING(@stringValue, 1, 1)
stringValue.IndexOf(value)CHARINDEX(@value, @stringValue) - 1
stringValue.IndexOf(value, startIndex)CHARINDEX(@value, @stringValue, @startIndex) - 1
stringValue.LastOrDefault()SUBSTRING(@stringValue, LEN(@stringValue), 1)
stringValue.LengthLEN(@stringValue)
stringValue.Replace(@oldValue, @newValue)REPLACE(@stringValue, @oldValue, @newValue)
stringValue.StartsWith(value)@stringValue LIKE @value + N'%'
stringValue.Substring(startIndex)SUBSTRING(@stringValue, @startIndex + 1, LEN(@stringValue))
stringValue.Substring(startIndex, length)SUBSTRING(@stringValue, @startIndex + 1, @length)
stringValue.ToLower()LOWER(@stringValue)
stringValue.ToUpper()UPPER(@stringValue)
stringValue.Trim()LTRIM(RTRIM(@stringValue))
stringValue.TrimEnd()RTRIM(@stringValue)
stringValue.TrimStart()LTRIM(@stringValue)

Miscellaneous functions

.NETSQLAdded in
enumValue.HasFlag(flag)@enumValue & @flag = @flag
Guid.NewGuid()NEWID()
nullable.GetValueOrDefault()COALESCE(@nullable, 0)
nullable.GetValueOrDefault(defaultValue)COALESCE(@nullable, @defaultValue)

[!NOTE] Some SQL translations have been simplified for illustration purposes. The actual SQL is more complex to handle a wider range of values.

See also