Functietoewijzingen van de Microsoft SQL Server-provider
Op deze pagina ziet u welke .NET-leden worden omgezet in welke SQL-functies worden gebruikt bij het gebruik van de SQL Server-provider.
Aggregatiefuncties
| .NET | SQL | Toegevoegd in |
|---|---|---|
| EF.Functions.StandardDeviationSample(groep.Select(x => x.Property)) | STDEV(eigenschap) | EF Core 7.0 |
| EF.Functions.StandardDeviationPopulation(group.Select(x => x.Property)) | STDEVP(eigenschap) | EF Core 7.0 |
| EF.Functions.VarianceSample(groep.Select(x => x.Property)) | VAR(eigenschap) | EF Core 7.0 |
| EF.Functions.VariancePopulation(groep.Select(x => x.Property)) | VARP(eigenschap) | EF Core 7.0 |
| groep.Gemiddelde(x => x.Eigenschap) | AVG(eigenschap) | |
| groep. Count() | AANTAL(*) | |
| groep. LongCount() | COUNT_BIG(*) | |
| groep.Max(x => x.Eigenschap) | MAX(eigenschap) | |
| groep.Min(x => x.Property) | MIN(eigenschap) | |
| groep.Sum(x => x.Property) | SOM(Eigenschap) | |
| string.Concat(groep.Select(x => x.Property)) | STRING_AGG(eigenschap, N'') | EF Core 7.0 |
| string.Join(scheidingsteken, group.Select(x => x.Property)) | STRING_AGG(eigenschap, @separator) | EF Core 7.0 |
Binaire functies
| .NET | SQL | Toegevoegd in |
|---|---|---|
| Bytes. Contains(value) | CHARINDEX(@value, @bytes)> 0 | |
| Bytes.ElementAt(i) | SUBSTRING(@bytes, @i + 1, 1) | EF Core 8.0 |
| bytes.First() | SUBTEKENREEKS(@bytes, 1, 1) | |
| Bytes. Lengte | DATALENGTH(@bytes) | |
| bytes.SequenceEqual(second) | @bytes = @second | |
| bytes[i] | SUBTEKENREEKS(@bytes, @i + 1, 1) | |
| EF.Functions.DataLength(arg) | DATALENGTH(@arg) |
Conversiefuncties
| .NET | SQL | Toegevoegd in |
|---|---|---|
| bytes.ToString() | CONVERTEREN(varchar(100), @bytes) | |
| byteValue.ToString() | CONVERT(varchar(3), @byteValue) | |
| charValue.ToString() | CONVERTEREN(varchar(1), @charValue) | |
| Convert.ToBoolean(value) (zet de waarde om naar een Booleaanse waarde) | CONVERTEREN(bit, @value) | |
| Convert.ToByte(value) | CONVERT(tinyint, @value) | |
| Convert.ToDecimal(value) | CONVERTEREN(decimaal(18, 2), @value) | |
| Convert.ToDouble(waarde) | CONVERTEREN(float, @value) | |
| Convert.ToInt16(value) | CONVERTEREN(smallint, @value) | |
| Convert.ToInt32(value) | CONVERTEREN(int, @value) | |
| Convert.ToInt64(waarde) | CONVERTEREN(bigint, @value) | |
| Convert.ToString(waarde) | CONVERTEREN(nvarchar(max), @value) | |
| dateOnly.ToString() | CONVERTEREN(varchar(100), @dateOnly) | EF Core 8.0 |
| dateTime.ToString() | CONVERTEREN(varchar(100), @dateTime) | |
| dateTimeOffset.ToString() | CONVERTEREN(varchar(100), @dateTimeOffset) | |
| decimalValue.ToString() | CONVERTEREN(varchar(100), @decimalValue) | |
| doubleValue.ToString() | CONVERTEREN(varchar(100), @doubleValue) | |
| floatValue.ToString() | CONVERTEREN(varchar(100), @floatValue) | |
| Guid. ToString() | CONVERTEREN(varchar(36), @guid) | |
| intValue.ToString() | CONVERTEREN(varchar(11), @intValue) | |
| longValue.ToString() | CONVERTEREN(varchar(20), @longValue) | |
| sbyteValue.ToString() | CONVERTEREN(varchar(4), @sbyteValue) | |
| shortValue.ToString() | CONVERT(varchar(6), @shortValue) | |
| timeOnly.ToString() | CONVERTEREN(varchar(100), @timeOnly) | EF Core 8.0 |
| timeSpan.ToString() | CONVERTEREN(varchar(100), @timeSpan) | |
| uintValue.ToString() | CONVERTEREN(varchar(10), @uintValue) | |
| ulongValue.ToString() | CONVERTEREN(varchar(19), @ulongValue) | |
| ushortValue.ToString() | CONVERTEREN(varchar(5), @ushortValue) |
Datum- en tijdfuncties
| .NET | SQL | Toegevoegd in |
|---|---|---|
| DateTime.Now | GETDATE() | |
| DateTime.Today | CONVERT(date, GETDATE()) | |
| DateTime.UtcNow | GETUTCDATE() | |
| dateTime.AddDays(waarde) | DATEADD(day, @value, @dateTime) | |
| dateTime.AddHours(value) | DATEADD(hour, @value, @dateTime) | |
| dateTime.AddMilliseconds(value) | DATEADD(milliseconden, @value, @dateTime) | |
| dateTime.AddMinutes(value) | DATEADD(minuut, @value, @dateTime) | |
| dateTime.AddMonths(maanden) | DATEADD(maand, @months, @dateTime) | |
| dateTime.AddSeconds(value) | DATEADD(second, @value, @dateTime) | |
| dateTime.AddYears(value) | DATEADD(jaar, @value, @dateTime) | |
| dateTime.Date | CONVERTEREN(datum, @dateTime) | |
| dateTime.Day | DATEPART(day, @dateTime) | |
| datumTijd.DagVanHetJaar | DATEPART(dayofyear, @dateTime) | |
| dateTime.Hour | DATEPART(uur, @dateTime) | |
| dateTime.Microsecond | DATEPART(microseconden, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Millisecond | DATEPART(milliseconden, @dateTime) | |
| dateTime.Minute | DATEPART(minuut, @dateTime) | |
| dateTime.Maand | DATEPART(maand, @dateTime) | |
| dateTime.Nanosecond | DATEPART(nanoseconde, @dateTime) % 1000 | EF Core 10.0 |
| dateTime.Second | DATEPART(second, @dateTime) | |
| dateTime.TijdVanDeDag | CONVERTEREN(tijd, @dateTime) | |
| dateTime.Year | DATEPART(year, @dateTime) | |
| DateTimeOffset.Now | SYSDATETIMEOFFSET() | |
| DateTimeOffset.UtcNow | SYSUTCDATETIME() | |
| dateTimeOffset.AddDays(dagen) | DATEADD(day, @days, @dateTimeOffset) | |
| dateTimeOffset.AddHours(uren) | DATEADD(hour, @hours, @dateTimeOffset) | |
| dateTimeOffset.AddMilliseconds(milliseconden) | DATEADD(milliseconden, @milliseconds, @dateTimeOffset) | |
| dateTimeOffset.AddMinutes(minuten) | DATEADD(minuut, @minutes, @dateTimeOffset) | |
| dateTimeOffset.AddMonths(maanden) | DATEADD(maand, @months, @dateTimeOffset) | |
| dateTimeOffset.AddSeconds(seconds) | DATEADD(second, @seconds, @dateTimeOffset) | |
| dateTimeOffset.AddYears(years) | DATEADD(jaar, @years, @dateTimeOffset) | |
| dateTimeOffset.Date | CONVERTEREN(datum, @dateTimeOffset) | |
| dateTimeOffset.Day | DATEPART(day, @dateTimeOffset) | |
| dateTimeOffset.DayOfYear | DATEPART(dayofyear, @dateTimeOffset)) | |
| dateTimeOffset.Hour | DATEPART(uur, @dateTimeOffset) | |
| dateTimeOffset.Microsecond | DATEPART(microseconden, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Millisecond | DATEPART(milliseconden, @dateTimeOffset) | |
| dateTimeOffset.Minute | DATEPART(minuut, @dateTimeOffset) | |
| dateTimeOffset.Maand | DATEPART(maand, @dateTimeOffset) | |
| dateTimeOffset.Nanosecond | DATEPART(nanoseconde, @dateTimeOffset) % 1000 | EF Core 10.0 |
| dateTimeOffset.Second | DATEPART(second, @dateTimeOffset) | |
| dateTimeOffset.TimeOfDay | CONVERTEREN(tijd, @dateTimeOffset) | |
| dateTimeOffset.ToUnixTimeSeconds() | DATEDIFF_BIG(seconde, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | EF Core 8.0 |
| dateTimeOffset.ToUnixTimeMilliseconds() | DATEDIFF_BIG(milliseconden, '1970-01-01T00:00:00.0000000+00:00', @dateTimeOffset) | EF Core 8.0 |
| dateTimeOffset.Jaar | DATEPART(year, @dateTimeOffset) | |
| DateOnly.FromDateTime(dateTime) | CONVERTEREN(datum, @dateTime) | EF Core 8.0 |
| dateOnly.AddDays(value) | DATEADD(day, @value, @dateOnly) | EF Core 8.0 |
| dateOnly.AddMonths(maanden) | DATEADD(maand, @months, @dateOnly)) | EF Core 8.0 |
| dateOnly.VoegJarenToe(waarde) | DATEADD(jaar, @value, @dateOnly) | EF Core 8.0 |
| dateOnly.Day | DATEPART(dag, @dateOnly) | EF Core 8.0 |
| dateOnly.DagVanHetJaar | DATEPART(dayofyear, @dateOnly) | EF Core 8.0 |
| dateOnly.Maand | DATEPART(maand, @dateOnly) | EF Core 8.0 |
| dateOnly.Jaar | DATEPART(year, @dateOnly) | EF Core 8.0 |
| EF. Functions.AtTimeZone(dateTime, timeZone) | @dateTime IN TIJDZONE @timeZone | EF Core 7.0 |
| EF.Functions.DateDiffDay(start, end) | DATEDIFF(day, @start, @end)) | |
| EF. Functions.DateDiffHour(begin, einde) | DATEDIFF(uur, @start, @end) | |
| EF.Functions.DateDiffMicrosecond(start, end) | DATEDIFF(microseconde, @start, @end) | |
| EF. Functions.DateDiffMillisecond(begin, einde) | DATEDIFF(milliseconden, @start, @end) | |
| EF.Functions.DateDiffMinute(start, end) | DATUMVERSCHIL(minuut, @start, @d2) | |
| EF.Functions.DateDiffMonth(begin, einde) | DATUMVERSCHIL(maand, @start, @end) | |
| EF. Functions.DateDiffNanosecond(begin, einde) | DATEDIFF(nanoseconde, @start, @end) | |
| EF. Functions.DateDiffSecond(begin, einde) | DATEDIFF(second, @start, @end) | |
| EF. Functions.DateDiffWeek(begin, einde) | DATEDIFF(week, @start, @end)) | |
| EF.Functions.DateDiffYear(start, end) | DATEDIFF(jaar, @start, @end) | |
| EF.Functions.DateFromParts(jaar, maand, dag) | DATEFROMPARTS(@year, @month, @day) | |
| EF. Functions.DateTime2FromParts(jaar, maand, dag, ...) | DATETIME2FROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DateTimeFromParts(year, month, day, ...) | DATETIMEFROMPARTS(@year, @month, @day, ...) | |
| EF.Functions.DateTimeOffsetFromParts(jaar, maand, dag, ...) | DATETIMEOFFSETFROMPARTS(@year, @month, @day, ...) | |
| EF. Functions.IsDate(expression) | ISDATE(@expression) | |
| EF.Functions.SmallDateTimeFromParts(jaar, maand, dag, ...) | SMALLDATETIMEFROMPARTS(@year, @month, @day, ...) | |
| EF. Functions.TimeFromParts(uur, minuut, seconde, ...) | TIMEFROMPARTS(@hour, @minute, @second, ...) | |
| timeOnly.VoegUrenToe(waarde) | DATEADD(hour, @value, @timeOnly) | EF Core 8.0 |
| timeOnly.AddMinutes(waarde) | DATEADD(minuut, @value, @timeOnly) | EF Core 8.0 |
| alleenTijd.Uur | DATEPART(uur, @timeOnly) | EF Core 8.0 |
| timeOnly.IsBetween(start, end) | @timeOnly >= @start EN @timeOnly<@end | EF Core 8.0 |
| timeOnly.Microsecond | DATEPART(MICROSECOND, @timeOnly) % 1000 | EF Core 10.0 |
| timeOnly.Millisecond | DATEPART(milliseconden, @timeOnly) | EF Core 8.0 |
| timeOnly.Minute | DATEPART(minuut, @timeOnly) | EF Core 8.0 |
| timeOnly.Nanosecond | DATEPART(nanoseconde, @timeOnly) % 1000 | EF Core 10.0 |
| timeOnly.Second | DATEPART(second, @timeOnly) | EF Core 8.0 |
| timeSpan.Hours | DATEPART(uur, @timeSpan) | |
| timeSpan.Microseconde | DATEPART(microseconden, @timeSpan) % 1000 | EF Core 10.0 |
| timeSpan.Milliseconden | DATEPART(milliseconden, @timeSpan) | |
| timeSpan.Minutes | DATEPART(minuut, @timeSpan) | |
| timeSpan.Nanoseconde | DATEPART(nanoseconde, @timeSpan) % 1000 | EF Core 10.0 |
| tijdspanne.Seconden | DATEPART(second, @timeSpan) |
Numerieke functies
| .NET | SQL | Toegevoegd in |
|---|---|---|
| dubbel. DegreesToRadians(x) | RADIANEN(@x) | EF Core 8.0 |
| double.RadiansToDegrees(x) | DEGREES(@x) | EF Core 8.0 |
| EF.Functions.Random() | RAND() | |
| Math.Abs(waarde) | ABS (absolute waarde) (@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) | PLAFOND(@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) | GROOTSTE(@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) | AFRONDEN(@d, 0) | |
| Math.Round(d, decimalen) | AFRONDEN(@d, @decimals) | |
| Math.Sign(waarde) | SIGN(@value) | |
| Math.Sin(a) | SIN(@a) | |
| Math.Sqrt(d) | SQRT(@d) | |
| Math.Tan(a) | TAN(@a) | |
| Math.Truncate(d) | AFRONDEN(@d, 0, 1) |
Tip
Naast de hier vermelde methoden worden de bijbehorende generieke math implementaties en MathF methoden ook vertaald. Bijvoorbeeld: Math.Sin, MathF.Sin, double.Sinen float.Sin komen allemaal overeen met de functie SIN in SQL.
Tekenreeksfuncties
| .NET | SQL | Toegevoegd in |
|---|---|---|
| EF.Functions.Collate(operand, sortering) | @operand COLLATIE @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(expressie) | ISNUMERIC(@expression) | |
| EF.Functions.Like(matchExpression, patroon) | @matchExpression VIND-LEUK @pattern | |
| EF.Functions.Like(matchExpressie, patroon, ontsnappingsteken) | @matchExpression LIKE @pattern ESCAPE @escapeCharacter | |
| string.Compare(strA, strB) | CASE WHEN @strA = @strB THEN 0 ... EINDE | |
| string.Concat(str0, str1) | @str0 + @str1 | |
| string.IsNullOrEmpty(waarde) | @value IS NULL OF @value LIKE N'' | |
| string.IsNullOrWhiteSpace(waarde) | @value IS NULL OF @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 ... EINDE | |
| 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 | EF Core 7.0 |
| stringValue.LastOrDefault() | SUBSTRING(@stringValue, LENGTE(@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) |
Diverse functies
| .NET | SQL | Toegevoegd in |
|---|---|---|
| enumValue.HasFlag(flag) | @enumValue & @flag = @flag | |
| Guid.NewGuid() | NEWID() | |
| nullable.GetValueOrDefault() | COALESCE(@nullable, 0) | |
| nullable.GetValueOrDefault(defaultValue) | COALESCE(@nullable, @defaultValue) |
Notitie
Sommige SQL is vereenvoudigd voor illustratiedoeleinden. De werkelijke SQL is complexer voor het verwerken van een breder bereik van waarden.
Zie ook
Met ons samenwerken op GitHub
De bron voor deze inhoud vindt u op GitHub, waar u ook problemen en pull-aanvragen kunt maken en controleren. Bekijk onze gids voor inzenders voor meer informatie.
