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

.NET	SQL	Added in
EF.Functions.StandardDeviationSample(group.Select(x => x.Property))	STDEV(Property)	EF Core 7.0
EF.Functions.StandardDeviationPopulation(group.Select(x => x.Property))	STDEVP(Property)	EF Core 7.0
EF.Functions.VarianceSample(group.Select(x => x.Property))	VAR(Property)	EF Core 7.0
EF.Functions.VariancePopulation(group.Select(x => x.Property))	VARP(Property)	EF Core 7.0
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'')	EF Core 7.0
string.Join(separator, group.Select(x => x.Property))	STRING_AGG(Property, @separator)	EF Core 7.0

Binary functions

.NET	SQL	Added in
bytes.Contains(value)	CHARINDEX(@value, @bytes) > 0
bytes.ElementAt(i)	SUBSTRING(@bytes, @i + 1, 1)	EF Core 8.0
bytes.First()	SUBSTRING(@bytes, 1, 1)
bytes.Length	DATALENGTH(@bytes)
bytes.SequenceEqual(second)	@bytes = @second
bytes[i]	SUBSTRING(@bytes, @i + 1, 1)
EF.Functions.DataLength(arg)	DATALENGTH(@arg)

Conversion functions

.NET	SQL	Added 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)	EF Core 8.0
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)	EF Core 8.0
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

.NET	SQL	Added in
DateTime.Now	GETDATE()
DateTime.Today	CONVERT(date, GETDATE())
DateTime.UtcNow	GETUTCDATE()
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.Date	CONVERT(date, @dateTime)
dateTime.Day	DATEPART(day, @dateTime)
dateTime.DayOfYear	DATEPART(dayofyear, @dateTime)
dateTime.Hour	DATEPART(hour, @dateTime)
dateTime.Millisecond	DATEPART(millisecond, @dateTime)
dateTime.Minute	DATEPART(minute, @dateTime)
dateTime.Month	DATEPART(month, @dateTime)
dateTime.Second	DATEPART(second, @dateTime)
dateTime.TimeOfDay	CONVERT(time, @dateTime)
dateTime.Year	DATEPART(year, @dateTime)
DateTimeOffset.Now	SYSDATETIMEOFFSET()
DateTimeOffset.UtcNow	SYSUTCDATETIME()
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.Date	CONVERT(date, @dateTimeOffset)
dateTimeOffset.Day	DATEPART(day, @dateTimeOffset)
dateTimeOffset.DayOfYear	DATEPART(dayofyear, @dateTimeOffset)
dateTimeOffset.Hour	DATEPART(hour, @dateTimeOffset)
dateTimeOffset.Millisecond	DATEPART(millisecond, @dateTimeOffset)
dateTimeOffset.Minute	DATEPART(minute, @dateTimeOffset)
dateTimeOffset.Month	DATEPART(month, @dateTimeOffset)
dateTimeOffset.Second	DATEPART(second, @dateTimeOffset)
dateTimeOffset.TimeOfDay	CONVERT(time, @dateTimeOffset)
dateTimeOffset.ToUnixTimeSeconds()	DATEDIFF_BIG(second, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset)	EF Core 8.0
dateTimeOffset.ToUnixTimeMilliseconds()	DATEDIFF_BIG(millisecond, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset)	EF Core 8.0
dateTimeOffset.Year	DATEPART(year, @dateTimeOffset)
dateOnly.AddDays(value)	DATEADD(day, @value, @dateOnly)	EF Core 8.0
dateOnly.AddMonths(months)	DATEADD(month, @months, @dateOnly)	EF Core 8.0
dateOnly.AddYears(value)	DATEADD(year, @value, @dateOnly)	EF Core 8.0
dateOnly.Day	DATEPART(day, @dateOnly)	EF Core 8.0
dateOnly.DayOfYear	DATEPART(dayofyear, @dateOnly)	EF Core 8.0
DateOnly.FromDateTime(dateTime)	CONVERT(date, @dateTime)	EF Core 8.0
dateOnly.Month	DATEPART(month, @dateOnly)	EF Core 8.0
dateOnly.Year	DATEPART(year, @dateOnly)	EF Core 8.0
EF.Functions.AtTimeZone(dateTime, timeZone)	@dateTime AT TIME ZONE @timeZone	EF Core 7.0
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)	EF Core 8.0
timeOnly.AddMinutes(value)	DATEADD(minute, @value, @timeOnly)	EF Core 8.0
timeOnly.Hours	DATEPART(hour, @timeOnly)	EF Core 8.0
timeOnly.IsBetween(start, end)	@timeOnly >= @start AND @timeOnly < @end	EF Core 8.0
timeOnly.Milliseconds	DATEPART(millisecond, @timeOnly)	EF Core 8.0
timeOnly.Minutes	DATEPART(minute, @timeOnly)	EF Core 8.0
timeOnly.Seconds	DATEPART(second, @timeOnly)	EF Core 8.0
timeSpan.Hours	DATEPART(hour, @timeSpan)
timeSpan.Milliseconds	DATEPART(millisecond, @timeSpan)
timeSpan.Minutes	DATEPART(minute, @timeSpan)
timeSpan.Seconds	DATEPART(second, @timeSpan)

Numeric functions

.NET	SQL	Added in
double.DegreesToRadians(x)	RADIANS(@x)	EF Core 8.0
double.RadiansToDegrees(x)	DEGREES(@x)	EF Core 8.0
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

.NET	SQL	Added 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	EF Core 7.0
stringValue.LastOrDefault()	SUBSTRING(@stringValue, LEN(@stringValue), 1)
stringValue.Length	LEN(@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

.NET	SQL
collection.Contains(item)	@item IN @collection
enumValue.HasFlag(flag)	@enumValue & @flag = @flag
Guid.NewGuid()	NEWID()
nullable.GetValueOrDefault()	COALESCE(@nullable, 0)
nullable.GetValueOrDefault(defaultValue)	COALESCE(@nullable, @defaultValue)

Note

Some SQL has been simplified for illustration purposes. The actual SQL is more complex to handle a wider range of values.

See also

• Spatial Function Mappings
• HierarchyId Function Mappings