Nota
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare ad accedere o modificare le directory.
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare a modificare le directory.
Questa pagina mostra quali .NET membri vengono convertiti in quali funzioni SQL quando si usa il provider di SQL Server.
Funzioni di aggregazione
Per informazioni generali sull'uso di funzioni di aggregazione nelle query, vedere Funzioni di aggregazione.
| .NET | SQL | Aggiunta in |
|---|---|---|
| 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(Proprietà) | |
| EF. Functions.VariancePopulation(group. Select(x => x.Property)) | VARP(Proprietà) | |
| gruppo. Average(x => x.Property) | AVG(Property) | |
| gruppo. Count() | COUNT(*) | |
| gruppo.LongCount() | COUNT_BIG(*) | |
| gruppo.Max(x => x.Property) | MAX(Property) | |
| gruppo.Min(x => x.Proprietà) | MIN(Property) | |
| gruppo.Somma(x => x.Property) | SOMMA(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(Property, N'') WITHIN GROUP (ORDER BY Other) | EF Core 11.0 |
| string.Join(separatore, gruppo.Select(x => x.Property)) | STRING_AGG(Proprietà, @separator) | |
| string.Join(separator, group.OrderBy(x => x.Other).Select(x => x.Property)) | STRING_AGG(Property, @separator) WITHIN GROUP (ORDER BY Other) | EF Core 11.0 |
Funzioni binarie
| .NET | SQL | Aggiunta in |
|---|---|---|
| Byte. Contains(value) | CHARINDEX(@value, @bytes)> 0 | |
| bytes.ElementAt(i) | SUBSTRING(@bytes, @i + 1, 1) | |
| bytes.First() | SUBSTRING(@bytes, 1, 1) | |
| Byte. Lunghezza | DATALENGTH(@bytes) | |
| bytes.SequenceEqual(second) | @bytes = @second | |
| byte[i] | SUBSTRING(@bytes, @i + 1, 1) | |
| EF.Functions.DataLength(arg) | DATALENGTH(@arg) |
Funzioni di conversione
| .NET | SQL | Aggiunta in |
|---|---|---|
| bytes.ToString() | CONVERT(varchar(100), @bytes) | |
| byteValue.ToString() | CONVERT(varchar(3), @byteValue) | |
| charValue.ToString() | CONVERT(varchar(1), @charValue) | |
| Convert.ToBoolean(valore) | 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) |
Funzioni di data e ora
| .NET | SQL | Aggiunta in |
|---|---|---|
| DateTime.Now | GETDATE() | |
| DateTime.Today | CONVERT(date, GETDATE()) | |
| DateTime.UtcNow | GETUTCDATE() | |
| dateTime.AddDays(value) | DATEADD(giorno, @value, @dateTime) | |
| dateTime.AddHours(value) | DATEADD(ora, @value, @dateTime)) | |
| dateTime.AddMilliseconds(value) | DATEADD(millisecondo, @value, @dateTime) | |
| dateTime.AddMinutes(value) | DATEADD(minuto, @value, @dateTime)) | |
| dataOra.AggiungiMesi(mesi) | DATEADD(mese, @months, @dateTime) | |
| dateTime.AddSeconds(value) | DATEADD(secondo, @value, @dateTime)) | |
| dateTime.AddYears(value) | DATEADD(year, @value, @dateTime)) | |
| dateTime.Date (Funzione per ottenere la data) | CONVERT(date, @dateTime) | |
| dateTime.Giorno | DATEPART(giorno, @dateTime) | |
| dateTime.DayOfYear (Giorno dell'anno) | DATEPART(giorno dell'anno, @dateTime) | |
| dateTime.Hour | DATEPART(hour, @dateTime) | |
| dateTime.Microsecond | DATEPART(microsecondo, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Millisecond | DATEPART(millisecondi, @dateTime) | |
| dateTime.Minute | DATEPART(minuto, @dateTime) | |
| dateTime.Month | DATEPART(month, @dateTime) | |
| dateTime.Nanosecond | DATEPART(nanosecondo, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Second | DATEPART(secondo, @dateTime) | |
| dateTime.TimeOfDay | CONVERT(time, @dateTime) | |
| dateTime.Year | DATEPART(anno, @dateTime) | |
| DateTimeOffset.Now | SYSDATETIMEOFFSET() | |
| DateTimeOffset.UtcNow | SYSUTCDATETIME() | |
| dateTimeOffset.AddDays(days) | DATEADD(giorno, @days, @dateTimeOffset) | |
| dateTimeOffset.AddHours(hours) | DATEADD(ora, @hours, @dateTimeOffset)) | |
| dateTimeOffset.AddMilliseconds(milliseconds) - Questo metodo aggiunge un numero specificato di millisecondi all'oggetto dateTimeOffset. | DATEADD(millisecondo, @milliseconds, @dateTimeOffset) | |
| dateTimeOffset.AddMinutes(minutes) | DATEADD(minuto, @minutes, @dateTimeOffset)) | |
| dateTimeOffset.AddMonths(months) | DATEADD(mese, @months, @dateTimeOffset) | |
| dateTimeOffset.AddSeconds(aggiungiSecondi) | DATEADD(secondo, @seconds, @dateTimeOffset)) | |
| dateTimeOffset.AddYears(years) | DATEADD(year, @years, @dateTimeOffset)) | |
| dateTimeOffset.Date | CONVERT(date, @dateTimeOffset) | |
| dateTimeOffset.DateTime | CONVERT(datetime2, @dateTimeOffset) | EF Core 11.0 |
| dateTimeOffset.Day | DATEPART(giorno, @dateTimeOffset) | |
| dateTimeOffset.DayOfYear // Giorno dell'anno | DATEPART(giorno dell'anno, @dateTimeOffset) | |
| dateTimeOffset.Hour | DATEPART(hour, @dateTimeOffset) | |
| dateTimeOffset.LocalDateTime | CONVERT(datetime2, @dateTimeOffset AT TIME ZONE CURRENT_TIMEZONE_ID()) | EF Core 11.0 |
| dateTimeOffset.Microsecond | DATEPART(microsecondo, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Millisecond | DATEPART(millisecondi, @dateTimeOffset) | |
| dateTimeOffset.Minute | DATEPART(minuto, @dateTimeOffset) | |
| dateTimeOffset.Month | DATEPART(month, @dateTimeOffset) | |
| dateTimeOffset.Nanosecond | DATEPART(nanosecondo, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Offset.TotalMinutes | CAST(DATEPART(tz, @dateTimeOffset) AS float) | EF Core 11.0 |
| dateTimeOffset.Second | DATEPART(secondo, @dateTimeOffset) | |
| dateTimeOffset.TimeOfDay | CONVERT(time, @dateTimeOffset) | |
| dateTimeOffset.ToOffset(offset) | SWITCHOFFSET(@dateTimeOffset, @offset) | EF Core 11.0 |
| dateTimeOffset.ToUnixTimeSeconds() | DATEDIFF_BIG(secondo, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.ToUnixTimeMilliseconds() | DATEDIFF_BIG(millesimo di secondo, '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(anno, @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(giorno, @value, @dateOnly) | |
| dateOnly.AggiungiMesi(months) | DATEADD(mese, @months, @dateOnly) | |
| dateOnly.AddYears(value) | DATEADD(year, @value, @dateOnly)) | |
| dateOnly.Giorno | DATEPART(giorno, @dateOnly) | |
| dateOnly.GiornoDellAnno | DATEPART(giorno dell'anno, @dateOnly) | |
| dateOnly.Month | DATEPART(month, @dateOnly) | |
| dateOnly.Anno | DATEPART(anno, @dateOnly) | |
| dateOnly.DayNumber | DATEDIFF(giorno, '0001-01-01', @dateOnly) | EF Core 10.0 |
| EF. Functions.AtTimeZone(dateTime, timeZone) | @dateTime FUSO ORARIO @timeZone | |
| EF.Functions.DateDiffDay(start, end) | DATEDIFF(giorno, @start, @end) | |
| EF.Functions.DateDiffHour(start, end) | DATEDIFF(ora, @start, @end) | |
| EF.Functions.DateDiffMicrosecond(start, end) | DATEDIFF(microsecondi, @start, @end) | |
| EF.Functions.DateDiffMillisecond(start, end) | DATEDIFF(millisecondo, @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(nanosecondo, @start, @end) | |
| EF.Functions.DateDiffSecond(start, end) | DATEDIFF(secondo, @start, @end) | |
| EF.Functions.DateDiffWeek(start, end) | DATEDIFF(week, @start, @end)) | |
| EF.Functions.DateDiffYear(start, end) | DATEDIFF(anno, @start, @end) | |
| EF.Functions.DateFromParts(year, month, day) | DATEFROMPARTS(@year, @month, @day) | |
| EF.Functions.DateTime2FromParts(year, month, day, ...) | DATETIME2FROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DataOraDaParti(anno, mese, giorno, ...) | DATETIMEFROMPARTS(@year, @month, @day, ...) | |
| EF. Functions.DateTimeOffsetFromParts(year, month, day, ...) | DATETIMEOFFSETFROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DateTrunc(datepart, value) | DATETRUNC(@datepart, @value) | EF Core 11.0 |
| EF. Functions.IsDate(expression) | ISDATE(@expression) | |
| EF.Funzioni.SmallDateTimeFromParts(year, month, day, ...) | SMALLDATETIMEFROMPARTS(@year, @month, @day, ...) - Crea una data di tipo SmallDateTime a partire dalle sue componenti. | |
| EF.Funzioni.TimeFromParts(hour, minute, second, ...) | TIMEFROMPARTS(@hour, @minute, @second, ...) | |
| timeOnly.AddHours(value) | DATEADD(ora, @value, @timeOnly)) | |
| timeOnly.AddMinutes(value) | DATEADD(minuto, @value, @timeOnly)) | |
| timeOnly.Hour | DATEPART(hour, @timeOnly) | |
| timeOnly.IsBetween(start, end) | @timeOnly >= @start AND @timeOnly<@end | |
| timeOnly.Microsecond | DATEPART(microsecondo, @timeOnly) % 1000 | EF Core 10.0 |
| timeOnly.Millisecond | DATEPART(millisecondi, @timeOnly) | |
| timeOnly.Minute | DATEPART(minuto, @timeOnly) | |
| timeOnly.Nanosecond | DATEPART(nanosecondo, @timeOnly) % 1000 | EF Core 10.0 |
| timeOnly.Second | DATEPART(secondo, @timeOnly) | |
| timeSpan.Hours | DATEPART(hour, @timeSpan) | |
| intervalloDiTempo.Microsecondo | DATEPART(microsecondo, @timeSpan) % 1000 | EF Core 10.0 |
| timeSpan.Milliseconds | DATEPART(millisecondi, @timeSpan) | |
| timeSpan.Minutes | DATEPART(minuto, @timeSpan) | |
| timeSpan.Nanosecond | DATEPART(nanosecondo, @timeSpan) % 1000 | EF Core 10.0 |
| timeSpan.Seconds | DATEPART(secondo, @timeSpan) |
Funzioni numeriche
| .NET | SQL | Aggiunta in |
|---|---|---|
| double.DegreesToRadians(x) | RADIANS (@x) | |
| double.RadiansToDegrees(x) | DEGREES (@x) | |
| EF.Functions.Random() | RAND() | |
| Math.Abs(valore) | ABS(@valore) | |
| 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(valore) | SIGN(@valore) | |
| Math.Sin(a) | SIN(@a) | |
| Math.Sqrt(d) | SQRT(@d) | |
| Math.Tan(a) | TAN(@a) | |
| Math.Truncate(d) | ROUND(@d, 0, 1) |
Suggerimento
Oltre ai metodi elencati qui, vengono tradotte anche le implementazioni matematiche generiche corrispondenti e i metodi MathF. Ad esempio, Math.Sin, MathF.Sin, double.Sin, e float.Sin tutti eseguono il mapping alla funzione SIN in SQL.
Funzioni di stringa
| .NET | SQL | Aggiunta in |
|---|---|---|
| EF.Functions.Collate(operando, regole di confronto) | @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) | |
| FunzioniDiEF.TestoLibero(propertyReference, freeText, languageTerm) | FREETEXT(@propertyReference, @freeText, LANGUAGE @languageTerm) | |
| EF.Functions.IsNumeric(expression) | ISNUMERIC(@expression) | |
| EF.Functions.Like(matchExpression, pattern) | @matchExpression COME @pattern | |
| EF.Functions.Like(matchExpression, pattern, escapeCharacter) | @matchExpression LIKE @pattern ESCAPE @escapeCharacter | |
| stringa.Compare(strA, strB) | CASO QUANDO @strA = @strB POI 0 ... FINE | |
| stringa.Concat(str0, str1) | @str0 + @str1 | |
| string.IsNullOrEmpty(value) | @value È NULL O @value COME N'' | |
| string.IsNullOrWhiteSpace(value) | @value È NULL O @value = N'' | |
| corda. Join(", ", new [] { x, y, z}) | CONCAT_WS(N', ', @x, @y, @z) | EF Core 9.0 |
| stringValue.CompareTo(strB) | CASO QUANDO @stringValue = @strB POI 0 ... FINE | |
| stringValue.Contains(valore) | @stringValue LIKE N'%' + @value + N'%' | |
| stringValue.EndsWith(valore) | @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) |
Funzioni JSON
| .NET | SQL | Aggiunta in |
|---|---|---|
| EF.Functions.JsonContains(json, searchValue, path?, searchMode?) | JSON_CONTAINS(@json, @searchValue, @path?, @searchMode?) | EF 11.0 |
| EF.Functions.JsonPathExists(json, path) | JSON_PATH_EXISTS(@json, @path) | EF 11.0 |
Funzioni varie
| .NET | SQL | Aggiunta in |
|---|---|---|
| enumValue.HasFlag(flag) | @enumValue & @flag = @flag | |
| Guid.NewGuid() | NEWID() | |
| nullable.GetValueOrDefault() | COALESCE(@nullable, 0) | |
| Nullable.GetValueOrDefault(defaultValue) | COALESCE(@nullable, @defaultValue) |
Nota
Alcune traduzioni SQL sono state semplificate a scopo illustrativo. L'effettivo SQL è più complesso per gestire un intervallo più ampio di valori.
Vedi anche
Collabora con noi su GitHub
L'origine di questo contenuto è disponibile in GitHub, in cui è anche possibile creare ed esaminare i problemi e le richieste pull. Per ulteriori informazioni, vedere la guida per i collaboratori.
