Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
Cette page indique quels membres .NET sont traduits en quelles fonctions SQL avec le fournisseur SQL Server.
Fonctions d’agrégation
Pour obtenir des informations générales sur l’utilisation de fonctions d’agrégation dans les requêtes, consultez Fonctions d’agrégation.
| .NET | SQL | Ajouté à |
|---|---|---|
| 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) | |
| groupe.Average(x => x.Property) | AVG(Property) | |
| group.Count() | COUNT(*) | |
| group.LongCount() | COUNT_BIG(*) | |
| groupe.Max(x => x.Propriété) | MAX(Property) | |
| groupe.Min(x => x.Property) | MIN(Property) | |
| groupe.Somme(x => x.Propriété) | 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(Property, N'') WITHIN GROUP (ORDER BY Other) | EF Core 11.0 |
| string.Join(séparateur, groupe.Select(x => x.Property)) | STRING_AGG(Propriété, @separator) | |
| String. Join(séparateur, groupe). OrderBy(x => x.Other). Select(x => x.Property)) | STRING_AGG(Property, @separator) WITHIN GROUP (ORDER BY Other) | EF Core 11.0 |
Fonctions binaires
| .NET | SQL | Ajouté à |
|---|---|---|
| bytes.Contains(value) | CHARINDEX(@value, @bytes)> 0 | |
| bytes.ElementAt(i) | SOUSCHAÎNE(@bytes, @i + 1, 1) | |
| bytes.First() | SUBSTRING(@bytes, 1, 1) | |
| bytes.Length | DATALENGTH(@bytes) | |
| bytes.SequenceEqual(second) | @bytes = @second | |
| bytes[i] | SOUSCHAÎNE(@bytes, @i + 1, 1) | |
| EF. Functions.DataLength(arg) | DATALENGTH(@arg) |
Fonctions de conversion
| .NET | SQL | Ajouté à |
|---|---|---|
| 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) |
Fonctions de date et heure
| .NET | SQL | Ajouté à |
|---|---|---|
| DateTime.Now | GETDATE() | |
| DateTime.Today | CONVERT(date, GETDATE()) | |
| DateTime.UtcNow | GETUTCDATE() | |
| dateTime.AddDays(value) | DATEADD(day, , @value@dateTime) | |
| dateTime.AddHours(value) | DATEADD(heure, , @value@dateTime) | |
| dateTime.AddMilliseconds(value) | DATEADD(milliseconde, @value, @dateTime) | |
| dateTime.AddMinutes(value) | DATEADD(minute, @value, @dateTime)) | |
| dateTime.AddMonths(months) | DATEADD(mois, @months, @dateTime)) | |
| dateTime.AddSeconds(value) | DATEADD(second, , @value@dateTime) | |
| dateTime.AddYears(value) | DATEADD(année, @value, @dateTime)) | |
| dateTime.Date | CONVERT(date, @dateTime) | |
| dateTime.Day | DATEPART(jour, @dateTime) | |
| dateTime.DayOfYear | DATEPART(jourdelannee, @dateTime) | |
| dateTime.Hour | DATEPART(heure, @dateTime) | |
| dateTime.Microsecond | DATEPART(microseconde, @dateTime) % 1 000 | EF Core 10.0 |
| dateTime.Millisecond | DATEPART(milliseconde, @dateTime) | |
| dateTime.Minute | DATEPART(minute, @dateTime) | |
| dateTime.Month | DATEPART(mois, @dateTime) | |
| dateTime.Nanosecond | DATEPART(nanoseconde, @dateTime) % 1 000 | EF Core 10.0 |
| dateTime.Second | DATEPART(seconde, @dateTime) | |
| dateTime.TimeOfDay | CONVERT(time, @dateTime) | |
| dateTime.Year | DATEPART(année, @dateTime) | |
| DateTimeOffset.Now | SYSDATETIMEOFFSET() | |
| DateTimeOffset.UtcNow | SYSUTCDATETIME() | |
| dateTimeOffset.AddDays(days) | DATEADD(day, , @days@dateTimeOffset) | |
| dateTimeOffset.AddHours(hours) | DATEADD(heure, , @hours@dateTimeOffset) | |
| dateTimeOffset.AddMilliseconds(milliseconds) | DATEADD(milliseconde, @milliseconds, @dateTimeOffset) | |
| dateTimeOffset.AddMinutes(minutes) | DATEADD(minute, @minutes, @dateTimeOffset)) | |
| dateTimeOffset.AddMonths(months) | DATEADD(mois, @months, @dateTimeOffset)) | |
| dateTimeOffset.AddSeconds(seconds) | DATEADD(second, , @seconds@dateTimeOffset) | |
| dateTimeOffset.AddYears(years) | DATEADD(année, @years, @dateTimeOffset)) | |
| dateTimeOffset.Date | CONVERT(date, @dateTimeOffset) | |
| dateTimeOffset.DateTime | CONVERT(datetime2, @dateTimeOffset) | EF Core 11.0 |
| dateTimeOffset.Day | DATEPART(jour, @dateTimeOffset) | |
| dateTimeOffset.DayOfYear | DATEPART(jourdelannee, @dateTimeOffset) | |
| dateTimeOffset.Hour | DATEPART(heure, @dateTimeOffset) | |
| dateTimeOffset.LocalDateTime | CONVERTIR(datetime2, @dateTimeOffset DANS LE FUSEAU HORAIRE CURRENT_TIMEZONE_ID()) | EF Core 11.0 |
| dateTimeOffset.Microsecond | DATEPART(microseconde, @dateTimeOffset) % 1 000 | EF Core 10.0 |
| dateTimeOffset.Millisecond | DATEPART(milliseconde, @dateTimeOffset) | |
| dateTimeOffset.Minute | DATEPART(minute, @dateTimeOffset)) | |
| dateTimeOffset.Month | DATEPART(mois, @dateTimeOffset) | |
| dateTimeOffset.Nanosecond | DATEPART(nanoseconde, @dateTimeOffset) % 1 000 | EF Core 10.0 |
| dateTimeOffset.Offset.TotalMinutes | CAST(DATEPART(tz, @dateTimeOffset) AS float) | EF Core 11.0 |
| dateTimeOffset.Second | DATEPART(seconde, @dateTimeOffset) | |
| dateTimeOffset.TimeOfDay | CONVERT(time, @dateTimeOffset) | |
| dateTimeOffset.ToOffset(offset) | SWITCHOFFSET(@dateTimeOffset, @offset) | EF Core 11.0 |
| dateTimeOffset.ToUnixTimeSeconds() | DATEDIFF_BIG(seconde, '1970-01-01T00:00:00.000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.ToUnixTimeMilliseconds() | DATEDIFF_BIG(milliseconde, '1970-01-01T00:00:00.000000+00:00', @dateTimeOffset) | |
| dateTimeOffset.UtcDateTime | CONVERT(datetime2, @dateTimeOffset AT TIME ZONE ’UTC’) | EF Core 11.0 |
| dateTimeOffset.Year | DATEPART(année, @dateTimeOffset) | |
| new DateTimeOffset(dateTime) // Crée un nouvel objet DateTimeOffset avec la date et l'heure spécifiées. | 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(day, , @value@dateOnly) | |
| dateOnly.AddMonths(months) | DATEADD(mois, @months, @dateOnly)) | |
| dateOnly.AddYears(value) | DATEADD(année, @value, @dateOnly)) | |
| dateOnly.Day | DATEPART(jour, @dateOnly) | |
| dateOnly.DayOfYear | DATEPART(jourdelannee, @dateOnly) | |
| dateOnly.Month | DATEPART(mois, @dateOnly) | |
| dateOnly.Year | DATEPART(année, @dateOnly) | |
| dateOnly.DayNumber | DATEDIFF(day, '0001-01-01', @dateOnly) | EF Core 10.0 |
| EF. Functions.AtTimeZone(dateTime, timeZone) | @dateTime FUSEAU HORAIRE @timeZone | |
| EF. Functions.DateDiffDay(start, end) | DATEDIFF(jour, @start, @end)) | |
| EF.Functions.DateDiffHour(start, end) | DATEDIFF(heure, @start, @end)) | |
| EF.Functions.DateDiffMicrosecond(start, end) | DATEDIFF(microseconde, @start, @end) | |
| EF.Functions.DateDiffMillisecond(start, end) | DATEDIFF(milliseconde, @start, @end) | |
| EF.Functions.DateDiffMinute(début, fin) | DATEDIFF(minute, @start, @d2)) | |
| EF.Functions.DateDiffMonth(début, fin) | DATEDIFF(mois, @start, @end)) | |
| EF.Functions.DateDiffNanosecond(début, fin) | DATEDIFF(nanosecond, @start, @end)) | |
| EF.Functions.DateDiffSecond(start, end) | DATEDIFF(second, , @start@end) | |
| EF.Functions.DateDiffWeek(start, end) | DATEDIFF(semaine, @start, @end) | |
| EF.Functions.DateDiffYear(start, end) | DATEDIFF(année, @start, @end)) | |
| EF. Functions.DateFromParts(année, mois, jour) | DATEFROMPARTS(@year, @month, @day) | |
| EF.Functions.DateTime2FromParts(année, mois, jour, ...) | DATETIME2FROMPARTS(@year, @month, @day, ...) | |
| EF. Functions.DateTimeFromParts(année, mois, jour, ...) | 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.Functions.SmallDateTimeFromParts(année, mois, jour, ...) | SMALLDATETIMEFROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.TimeFromParts(heure, minute, seconde, ...) | TIMEFROMPARTS(@hour, @minute, @second, ...) | |
| timeOnly.AddHours(value) | DATEADD(heure, , @value@timeOnly) | |
| timeOnly.AddMinutes(value) | DATEADD(minute, @value, @timeOnly)) | |
| timeOnly.Hour | DATEPART(heure, @timeOnly) | |
| timeOnly.IsBetween(start, end) | @timeOnly >= @start AND @timeOnly<@end | |
| timeOnly.Microsecond | DATEPART(microseconde, @timeOnly) % 1 000 | EF Core 10.0 |
| timeOnly.Millisecond | DATEPART(milliseconde, @timeOnly) | |
| timeOnly.Minute | DATEPART(minute, @timeOnly) | |
| timeOnly.Nanosecond | DATEPART(nanoseconde, @timeOnly) % 1 000 | EF Core 10.0 |
| timeOnly.Second | DATEPART(seconde, @timeOnly) | |
| timeSpan.Hours | DATEPART(heure, @timeSpan) | |
| timeSpan.Microsecond | DATEPART(microseconde, @timeSpan) % 1 000 | EF Core 10.0 |
| timeSpan.Milliseconds | DATEPART(milliseconde, @timeSpan) | |
| timeSpan.Minutes | DATEPART(minute, @timeSpan) | |
| timeSpan.Nanosecond | DATEPART(nanoseconde, @timeSpan) % 1 000 | EF Core 10.0 |
| timeSpan.Seconds | DATEPART(seconde, @timeSpan) |
Fonctions Numériques
| .NET | SQL | Ajouté à |
|---|---|---|
| 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, décimales) | 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) |
Conseil
Outre les méthodes répertoriées ici, des implémentations mathématiques génériques correspondantes et des méthodes MathF sont également traduites. Par exemple, Math.Sin, MathF.Sin, double.Sin et float.Sin mappent tous vers la fonction SIN dans SQL.
Fonctions de chaîne
| .NET | SQL | Ajouté à |
|---|---|---|
| EF. Functions.Collate(opérande, classement) | @operand COLLATION @collation | |
| EF.Functions.Contains(propertyReference, searchCondition) | CONTAINS(@propertyReference, @searchCondition) | |
| EF.Functions.Contains(propertyReference, conditionDeRecherche, termeDeLangue) | CONTAINS(@propertyReference, , @searchConditionLANGUAGE @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 COMME @pattern | |
| EF.Functions.Like(matchExpression, pattern, escapeCharacter) | @matchExpression LIKE @pattern ESCAPE @escapeCharacter | |
| String.Compare(strA, strB) | CASE WHEN @strA = @strB THEN 0 ... FIN | |
| String.Concat(str0, str1) | @str0 + @str1 | |
| string.IsNullOrEmpty(value) | @value EST NULL OU @value COMME N'' | |
| string.IsNullOrWhiteSpace(value) | @value EST NULL OU @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) | SOUS-CHAÎNE(@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) |
Fonctions JSON
| .NET | SQL | Ajouté à |
|---|---|---|
| EF.Fonctions.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 |
Fonctions diverses
| .NET | SQL | Ajouté à |
|---|---|---|
| enumValue.HasFlag(flag) | @enumValue & @flag = @flag | |
| Guid.NewGuid() | NEWID() | |
| nullable.GetValueOrDefault() | COALESCE(@nullable, 0) | |
| nullable.GetValueOrDefault(defaultValue) | COALESCE(@nullable, @defaultValue)) |
Note
Certaines traductions SQL ont été simplifiées à des fins d’illustration. Le SQL réel est plus complexe pour la gestion d’une plus grande plage de valeurs.
Voir aussi
Collaborer avec nous sur GitHub
La source de ce contenu se trouve sur GitHub, où vous pouvez également créer et examiner les problèmes et les demandes de tirage. Pour plus d’informations, consultez notre guide du contributeur.
