Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
En esta página se muestran los miembros de .NET que se traducen en qué funciones SQL al usar el proveedor de SQL Server.
Funciones de agregado
| .NET | SQL | Agregado en |
|---|---|---|
| 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) | |
| grupo.Promedio(x => x.Property) | AVG(Property) | |
| group.Count() | COUNT(*) | |
| group.LongCount() | COUNT_BIG(*) | |
| grupo.Máximo(x => x.Propiedad) | MAX(Property) | |
| grupo.Min(x => x.Property) | MIN(Property) | |
| grupo.Sum(x => x.Property) | SUM(Property) | |
| cuerda. Concat(group. Select(x => x.Property)) | STRING_AGG(Property, N'') | |
| cuerda. Join(separator, group. Select(x => x.Property)) | STRING_AGG(Propiedad, @separator) |
Funciones binarias
| .NET | SQL | Agregado en |
|---|---|---|
| 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) |
Funciones de conversión
| .NET | SQL | Agregado en |
|---|---|---|
| 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) |
Funciones de fecha y hora
| .NET | SQL | Agregado en |
|---|---|---|
| DateTime.Now | GETDATE() | |
| DateTime.Today | CONVERT(date, GETDATE()) | |
| DateTime.UtcNow | GETUTCDATE() | |
| dateTime.AddDays(value) | DATEADD(día, @value, @dateTime) | |
| dateTime.AddHours(value) | DATEADD(hora, @value, @dateTime) | |
| dateTime.AddMilliseconds(value) | DATEADD(milisegundos, @value, @dateTime) | |
| dateTime.AddMinutes(value) | DATEADD(minuto, @value, @dateTime) | |
| dateTime.AddMonths(months) | DATEADD(mes, @months, @dateTime) | |
| dateTime.AddSeconds(value) | DATEADD(second, @value, @dateTime)) | |
| dateTime.AddYears(value) | DATEADD(año, @value, @dateTime) | |
| dateTime.Date | CONVERT(date, @dateTime) | |
| dateTime.Day | DATEPART(día, @dateTime) | |
| dateTime.DayOfYear | DATEPART(dayofyear, @dateTime) | |
| dateTime.Hour | DATEPART(hora, @dateTime) | |
| dateTime.Microsecond | DATEPART(microsecond, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Millisecond | DATEPART(milisegundo, @dateTime) | |
| dateTime.Minute | DATEPART(minute, @dateTime) | |
| dateTime.Month | DATEPART(month, @dateTime) | |
| dateTime.Nanosecond | DATEPART(nanosecond, @dateTime) % 1000 | EF Core 10.0 |
| 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(día, @days, @dateTimeOffset) | |
| dateTimeOffset.AddHours(hours) | DATEADD(hora, @hours, @dateTimeOffset) | |
| dateTimeOffset.AddMilliseconds(milliseconds) | DATEADD(milisegundos, @milliseconds, @dateTimeOffset) | |
| dateTimeOffset.AddMinutes(minutes) | DATEADD(minuto, @minutes, @dateTimeOffset) | |
| dateTimeOffset.AddMonths(months) | DATEADD(mes, @months, @dateTimeOffset) | |
| dateTimeOffset.AddSeconds(seconds) | DATEADD(second, @seconds, @dateTimeOffset)) | |
| dateTimeOffset.AddYears(years) | DATEADD(año, @years, @dateTimeOffset) | |
| dateTimeOffset.Date | CONVERT(date, @dateTimeOffset) | |
| dateTimeOffset.Day | DATEPART(día, @dateTimeOffset) | |
| dateTimeOffset.DayOfYear | DATEPART(dayofyear, @dateTimeOffset) | |
| dateTimeOffset.Hour | DATEPART(hora, @dateTimeOffset) | |
| dateTimeOffset.Microsecond | DATEPART(microsecond, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Millisecond | DATEPART(milisegundo, @dateTimeOffset) | |
| dateTimeOffset.Minute | DATEPART(minute, @dateTimeOffset) | |
| dateTimeOffset.Month | DATEPART(month, @dateTimeOffset) | |
| dateTimeOffset.Nanosecond | DATEPART(nanosecond, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Second | DATEPART(second, @dateTimeOffset) | |
| dateTimeOffset.TimeOfDay | CONVERT(time, @dateTimeOffset) | |
| dateTimeOffset.ToUnixTimeSeconds() | DATEDIFF_BIG(second, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.ToUnixTimeMilliseconds() | DATEDIFF_BIG(milisegundo, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.Year | DATEPART(year, @dateTimeOffset) | |
| DateOnly.FromDateTime(dateTime) | CONVERT(date, @dateTime) | |
| dateOnly.AddDays(value) | DATEADD(día, @value, @dateOnly) | |
| dateOnly.AddMonths(months) | DATEADD(mes, @months, @dateOnly) | |
| dateOnly.AddYears(value) | DATEADD(año, @value, @dateOnly) | |
| dateOnly.Day | DATEPART(día, @dateOnly) | |
| dateOnly.DayOfYear | DATEPART(dayofyear, @dateOnly) | |
| dateOnly.Month | DATEPART(month, @dateOnly) | |
| dateOnly.Year | DATEPART(year, @dateOnly) | |
| dateOnly.DayNumber | DATEDIFF(día, '0001-01-01', @dateOnly) | EF Core 10.0 |
| EF. Functions.AtTimeZone(dateTime, timeZone) | @dateTime ZONA HORARIA @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(microsegundo, @start, @end) | |
| EF. Functions.DateDiffMillisecond(start, end) | DATEDIFF(milisegundo, @start, @end) | |
| EF. Functions.DateDiffMinute(start, end) | DATEDIFF(minuto, @start, @d2) | |
| EF. Functions.DateDiffMonth(start, end) | DATEDIFF(month, @start, @end) | |
| EF. Functions.DateDiffNanosecond(start, end) | DATEDIFF(nanosegundo, @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(año, @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(año, mes, día, ...) | 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(hora, @value, @timeOnly) | |
| timeOnly.AddMinutes(value) | DATEADD(minuto, @value, @timeOnly) | |
| timeOnly.Hour | DATEPART(hora, @timeOnly) | |
| timeOnly.IsBetween(start, end) | @timeOnly >= @start AND @timeOnly<@end | |
| timeOnly.Microsecond | DATEPART(microsecond, @timeOnly) % 1000 | EF Core 10.0 |
| timeOnly.Millisecond | DATEPART(milisegundo, @timeOnly) | |
| timeOnly.Minute | DATEPART(minute, @timeOnly) | |
| timeOnly.Nanosecond | DATEPART(nanosecond, @timeOnly) % 1000 | EF Core 10.0 |
| timeOnly.Second | DATEPART(second, @timeOnly) | |
| timeSpan.Hours | DATEPART(hora, @timeSpan) | |
| timeSpan.Microsecond | DATEPART(microsecond, @timeSpan) % 1000 | EF Core 10.0 |
| timeSpan.Milliseconds | DATEPART(milisegundo, @timeSpan) | |
| timeSpan.Minutes | DATEPART(minute, @timeSpan) | |
| timeSpan.Nanosecond | DATEPART(nanosecond, @timeSpan) % 1000 | EF Core 10.0 |
| timeSpan.Seconds | DATEPART(second, @timeSpan) |
Funciones numéricas
| .NET | SQL | Agregado en |
|---|---|---|
| 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, decimales) | 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) |
Sugerencia
Además de los métodos enumerados aquí, también se traducen las implementaciones matemáticas genéricas correspondientes y los métodos MathF. Por ejemplo, Math.Sin, MathF.Sin, double.Sin y float.Sin todos se asignan a la función SIN en SQL.
Funciones de cadena
| .NET | SQL | Agregado en |
|---|---|---|
| EF.Functions.Collate(operando, ordenación) | @operand COTEJAR @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 GUSTAR @pattern | |
| EF. Functions.Like(matchExpression, pattern, escapeCharacter) | @matchExpression LIKE @pattern ESCAPE @escapeCharacter | |
| String.Compare(strA, strB) | CASE WHEN @strA = @strB THEN 0 ... FIN | |
| cadena.Concat(str0, str1) | @str0 + @str1 | |
| string.IsNullOrEmpty(value) | @value ES NULL O @value COMO N'' | |
| string.IsNullOrWhiteSpace(value) | @value IS NULL OR @value = N'' | |
| cuerda. Join(", ", new [] { x, y, z}) | CONCAT_WS(N', ', @x, , @y@z) | EF Core 9.0 |
| stringValue.CompareTo(strB) | CASE WHEN @stringValue = @strB THEN 0 ... FIN | |
| 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) |
Funciones varias
| .NET | SQL | Agregado en |
|---|---|---|
| enumValue.HasFlag(flag) | @enumValue & @flag = @flag | |
| Guid.NewGuid () | NEWID() | |
| nullable.GetValueOrDefault() | COALESCE(@nullable, 0) | |
| nullable.GetValueOrDefault(defaultValue) | COALESCE(@nullable, @defaultValue) |
Nota:
Algunas traducciones sql se han simplificado con fines ilustrativos. El SQL actual es más complejo para manipular una gama más amplia de valores.
Consulte también
Colaborar con nosotros en GitHub
El origen de este contenido se puede encontrar en GitHub, donde también puede crear y revisar problemas y solicitudes de incorporación de cambios. Para más información, consulte nuestra guía para colaboradores.
