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.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'' | |
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
Collaborate with us on GitHub
The source for this content can be found on GitHub, where you can also create and review issues and pull requests. For more information, see our contributor guide.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for