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 muestra qué miembros de .NET se traducen en cuáles funciones SQL al usar el proveedor de SQL Server.
Funciones de agregado
Para obtener información general sobre el uso de funciones de agregado en consultas, vea 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) | |
| string.Concat(group.Select(x => x.Property)) | STRING_AGG(Property, N'') | |
| string.Concat(group.OrderBy(x => x.Other).Select(x => x.Property)) | STRING_AGG(Propiedad, N'') WITHIN GROUP (ORDER BY Other) | EF Core 11.0 |
| string.Join(separator, group.Select(x => x.Property)) | STRING_AGG(Propiedad, @separator) | |
| string.Join(separador, grupo.OrderBy(x => x.Other).Select(x => x.Property)) | STRING_AGG(Property, @separator) WITHIN GROUP (ORDER BY Other) | EF Core 11.0 |
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(diadelanio, @dateTime)) | |
| dateTime.Hour | DATEPART(hora, @dateTime) | |
| dateTime.Microsecond | DATEPART(microsegundo, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Millisecond | DATEPART(milisegundo, @dateTime) | |
| dateTime.Minute | DATEPART(minute, @dateTime) | |
| dateTime.Month | DATEPART(mes, @dateTime) | |
| dateTime.Nanosecond | DATEPART(nanosecond, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Second | DATEPART(second, @dateTime) | |
| dateTime.TimeOfDay | CONVERT(time, @dateTime) | |
| dateTime.Year | DATEPART(año, @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.DateTime | CONVERT(datetime2, @dateTimeOffset) | EF Core 11.0 |
| dateTimeOffset.Day | DATEPART(día, @dateTimeOffset) | |
| dateTimeOffset.DayOfYear | DATEPART(diadelanio, @dateTimeOffset)) | |
| dateTimeOffset.Hour | DATEPART(hora, @dateTimeOffset) | |
| dateTimeOffset.LocalDateTime (fecha y hora local) | CONVERT(datetime2, @dateTimeOffset AT TIME ZONE CURRENT_TIMEZONE_ID()) | EF Core 11.0 |
| dateTimeOffset.Microsecond | DATEPART(microsegundo, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Millisecond | DATEPART(milisegundo, @dateTimeOffset) | |
| dateTimeOffset.Minute | DATEPART(minute, @dateTimeOffset) | |
| dateTimeOffset.Month | DATEPART(mes, @dateTimeOffset) | |
| dateTimeOffset.Nanosecond | DATEPART(nanosecond, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Offset.TotalMinutes | CAST(DATEPART(tz, @dateTimeOffset) AS float) | EF Core 11.0 |
| dateTimeOffset.Second | DATEPART(second, @dateTimeOffset) | |
| dateTimeOffset.TimeOfDay | CONVERT(time, @dateTimeOffset) | |
| dateTimeOffset.ToOffset(offset) | SWITCHOFFSET(@dateTimeOffset, @offset) | EF Core 11.0 |
| dateTimeOffset.ToUnixTimeSeconds() | DATEDIFF_BIG(segundo, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.ToUnixTimeMilliseconds() | DATEDIFF_BIG(milisegundo, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.UtcDateTime | CONVERT(datetime2, @dateTimeOffset AT TIME ZONE 'UTC') | EF Core 11.0 |
| dateTimeOffset.Year | DATEPART(año, @dateTimeOffset) | |
| new DateTimeOffset(dateTime) | TODATETIMEOFFSET(@dateTime, '+00:00') | EF Core 11.0 |
| new DateTimeOffset(dateTime, offset) | TODATETIMEOFFSET(@dateTime, @offset) | EF Core 11.0 |
| 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(diadelanio, @dateOnly)) | |
| dateOnly.Month | DATEPART(mes, @dateOnly) | |
| dateOnly.Year | DATEPART(año, @dateOnly) | |
| dateOnly.DayNumber | DATEDIFF(día, '0001-01-01', @dateOnly) | EF Core 10.0 |
| EF. Functions.AtTimeZone(dateTime, timeZone) | @dateTime EN ZONA HORARIA @timeZone | |
| EF. Functions.DateDiffDay(start, end) | DATEDIFF(día, @start, @end) | |
| EF. Functions.DateDiffHour(start, end) | DATEDIFF(hour, @start, @end)) | |
| EF.Functions.DateDiffMicrosecond(inicio, fin) | 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(mes, @start, @end) | |
| EF.Functions.DateDiffNanosecond(inicio, fin) | DATEDIFF(nanosegundo, @start, @end) | |
| EF. Functions.DateDiffSecond(start, end) | DATEDIFF(second, @start, @end) | |
| EF. Functions.DateDiffWeek(start, end) | DATEDIFF(semanas, @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(año, mes, día, ...) | DATETIME2FROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DateTimeFromParts(año, mes, día, ...) | DATETIMEFROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DateTimeOffsetFromParts(año, mes, día, ...) | DATETIMEOFFSETFROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DateTrunc(datepart, value) | DATETRUNC(@datepart, @value) | EF Core 11.0 |
| 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(microsegundo, @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(microsegundo, @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 IS NULL OR @value LIKE N'' | |
| string.IsNullOrWhiteSpace(value) | @value ES NULO O @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 ... 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 JSON
| .NET | SQL | Agregado en |
|---|---|---|
| EF.Functions.JsonContains(json, searchValue, path?, searchMode?) | JSON_CONTAINS(@json, @searchValue, @path?, @searchMode?) | EF 11.0 |
| EF.Functions.JsonPathExists(json, ruta) | JSON_PATH_EXISTS(@json, @path) | EF 11.0 |
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.
