다음을 통해 공유

Microsoft SQL Server 공급자의 함수 매핑

이 페이지에서는 SQL Server 공급자를 사용할 때 어떤 .NET 멤버가 어떤 SQL 함수로 변환되는지 보여 줍니다.

집계 함수

.NET SQL 추가된 위치
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)
그룹. 평균(x => x.Property) AVG(Property)
group.Count() COUNT(*)
group.LongCount() COUNT_BIG(*)
그룹. Max(x => x.Property) MAX(Property)
그룹.Min(x => x.Property) MIN(Property)
그룹. Sum(x => x.Property) SUM(Property)
문자열.Concat(group.Select(x => x.Property)) STRING_AGG(Property, N'')
문자열. Join(separator, group. Select(x => x.Property)) STRING_AGG(Property, @separator)

이진 함수

.NET SQL 추가된 위치
bytes.Contains(value) CHARINDEX(@value, @bytes)> 0
bytes.ElementAt(i) SUBSTRING(@bytes, @i + 1, 1)
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)
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)

날짜 및 시간 함수

.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(밀리초, @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(시간, @dateTime)
dateTime.Microsecond DATEPART(마이크로초, @dateTime) % 1000 EF Core 10.0
dateTime.Millisecond DATEPART(밀리초, @dateTime)
dateTime.Minute DATEPART(분, @dateTime)
dateTime.Month DATEPART(month, @dateTime)
dateTime.Nanosecond DATEPART(nanosecond, @dateTime) % 1000) EF Core 10.0
dateTime.Second DATEPART(초, @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(밀리초, @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(시간, @dateTimeOffset)
dateTimeOffset.Microsecond DATEPART(마이크로초, @dateTimeOffset) % 1000 EF Core 10.0
dateTimeOffset.Millisecond DATEPART(밀리초, @dateTimeOffset)
dateTimeOffset.Minute DATEPART(분, @dateTimeOffset)
dateTimeOffset.Month DATEPART(month, @dateTimeOffset)
dateTimeOffset.Nanosecond DATEPART(nanosecond, @dateTimeOffset) % 1000) EF Core 10.0
dateTimeOffset.Second DATEPART(초, @dateTimeOffset)
dateTimeOffset.TimeOfDay CONVERT(time, @dateTimeOffset)
dateTimeOffset.ToUnixTimeSeconds() DATEDIFF_BIG(초, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset)
dateTimeOffset.ToUnixTimeMilliseconds() DATEDIFF_BIG(밀리초, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset)
dateTimeOffset.Year DATEPART(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.Day DATEPART(day, @dateOnly)
dateOnly.DayOfYear DATEPART(dayofyear, @dateOnly)
dateOnly.Month DATEPART(month, @dateOnly)
dateOnly.Year DATEPART(year, @dateOnly)
dateOnly.날짜번호 DATEDIFF(day, '0001-01-01', @dateOnly) EF Core 10.0
EF. Functions.AtTimeZone(dateTime, timeZone) @dateTime 표준 시간대 @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(마이크로초, @start, @end)
EF. Functions.DateDiffMillisecond(start, end) DATEDIFF(밀리초, @start, @end))
EF. Functions.DateDiffMinute(start, end) DATEDIFF(minute, @start, @d2)
EF. Functions.DateDiffMonth(start, end) 날짜차이(월, @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.Hour DATEPART(시간, @timeOnly)
timeOnly.IsBetween(start, end) 메서드는 주어진 start와 end 사이에 있는지를 확인합니다. @timeOnly >= @start AND @timeOnly<@end
timeOnly.마이크로초 DATEPART(마이크로초, @timeOnly) % 1000 EF Core 10.0
timeOnly.Millisecond DATEPART(밀리초, @timeOnly)
timeOnly.Minute DATEPART(분, @timeOnly)
timeOnly.Nanosecond DATEPART(nanosecond, @timeOnly) % 1000) EF Core 10.0
timeOnly.Second DATEPART(초, @timeOnly)
timeSpan.Hours DATEPART(시간, @timeSpan)
타임스팬.마이크로초 DATEPART(마이크로초, @timeSpan) % 1000 EF Core 10.0
timeSpan.Milliseconds DATEPART(밀리초, @timeSpan)
timeSpan.Minutes DATEPART(분, @timeSpan)
timeSpan.Nanosecond DATEPART(nanosecond, @timeSpan) % 1000) EF Core 10.0
timeSpan.Seconds DATEPART(초, @timeSpan)

숫자 함수

.NET SQL 추가된 위치
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)

팁

여기에 나열된 메서드 외에도 해당 제네릭 수학 구현 및 MathF 메서드도 번역됩니다. 예를 들어 Math.Sin, MathF.Sin, double.Sin, float.Sin 모두 SQL의 SIN 함수에 매핑합니다.

문자열 함수

.NET SQL 추가된 위치
EF. Functions.Collate(피연산자, 데이터 정렬) @operand 정렬 @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(속성 참조, 자유 텍스트, 언어용어) FREETEXT(@propertyReference, @freeText, LANGUAGE @languageTerm)
EF.Functions.IsNumeric(expression) ISNUMERIC(@expression)
EF. Functions.Like(matchExpression, pattern) @matchExpression 같이 @pattern
EF. Functions.Like(matchExpression, pattern, escapeCharacter) @matchExpression LIKE @pattern ESCAPE @escapeCharacter
문자열. Compare(strA, strB) CASE WHEN @strA = @strB THEN 0 ... End
문자열.Concat(str0, str1) @str0 + @str1
string.IsNullOrEmpty(value) @value NULL 또는 @value LIKE N''
string.IsNullOrWhiteSpace(value) @value IS NULL OR @value = N''
문자열. 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.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 추가된 위치
enumValue.HasFlag(flag) @enumValue & @flag = @flag
Guid.NewGuid() NEWID()
nullable.GetValueOrDefault() COALESCE(@null 허용, 0)
nullable.GetValueOrDefault(defaultValue) COALESCE(@null 허용, @defaultValue)

참고 항목

일부 SQL 번역은 설명 목적으로 간소화되었습니다. 실제 SQL은 더 넓은 범위의 값을 처리하기 위해 더 복잡합니다.

참고 항목

  • Last updated on