Microsoft SQL Server プロバイダーの関数のマッピング

[アーティクル]
01/12/2024

このページでは、SQL サービスプロバイダーを使用するときに、どの .NET メンバーがどの SQL関数に変換されるかを示します。

集計関数

.NET	SQL	追加されたバージョン:
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 関数

.NET	SQL	追加されたバージョン:
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)

変換関数

.NET	SQL	追加されたバージョン:
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)

日時の関数

.NET	SQL	追加されたバージョン:
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.FromDateTime(dateTime)	CONVERT(date, @dateTime)	EF Core 8.0
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.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.Hour	DATEPART(hour, @timeOnly)	EF Core 8.0
timeOnly.IsBetween(start, end)	@timeOnly>= @start AND @timeOnly<@end	EF Core 8.0
timeOnly.Millisecond	DATEPART(millisecond, @timeOnly)	EF Core 8.0
timeOnly.Minute	DATEPART(minute, @timeOnly)	EF Core 8.0
timeOnly.Second	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 関数

.NET	SQL	追加されたバージョン:
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)

ヒント

ここに記載されているメソッドに加えて、対応するジェネリック型数値演算の実装と MathF メソッドも変換されます。たとえば、Math.Sin、MathF.Sin、double.Sin および float.Sin はすべて SQL の SIN 関数にマップされます。

String 関数

.NET	SQL	追加されたバージョン:
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)

その他の関数

.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

いくつかの SQL は、説明のために簡略化されています。実際の SQL は、より広い範囲の値を処理するために、もっと複雑です。

次の方法で共有