Function Mappings of the SQLite EF Core Provider
This page shows which .NET members are translated into which SQL functions when using the SQLite provider.
Aggregate functions
| .NET | SQL | Added in |
|---|---|---|
| group.Average(x => x.Property) | AVG(Property) | |
| group.Average(x => x.DecimalProperty) | ef_avg(DecimalProperty) | EF Core 9.0 |
| group.Count() | COUNT(*) | |
| group.LongCount() | COUNT(*) | |
| group.Max(x => x.Property) | MAX(Property) | |
| group.Min(x => x.Property) | MIN(Property) | |
| group.Sum(x => x.Property) | SUM(Property) | |
| group.Sum(x => x.DecimalProperty) | ef_sum(DecimalProperty) | EF Core 9.0 |
| string.Concat(group.Select(x => x.Property)) | group_concat(Property, '') | EF Core 7.0 |
| string.Join(separator, group.Select(x => x.Property)) | group_concat(Property, @separator) | EF Core 7.0 |
Binary functions
| .NET | SQL | Added in |
|---|---|---|
| bytes.Contains(value) | instr(@bytes, char(@value)) > 0 | |
| bytes.Length | length(@bytes) | |
| bytes.SequenceEqual(second) | @bytes = @second | |
| EF.Functions.Hex(bytes) | hex(@bytes) | |
| EF.Functions.Substr(bytes, startIndex) | substr(@bytes, @startIndex) | |
| EF.Functions.Substr(bytes, startIndex, length) | substr(@bytes, @startIndex, @length) | |
| EF.Functions.Unhex(value) | unhex(@value) | EF Core 8.0 |
| EF.Functions.Unhex(value, ignoreChars) | unhex(@value, @ignoreChars) | EF Core 8.0 |
Conversion functions
| .NET | SQL |
|---|---|
| boolValue.ToString() | CAST(@boolValue AS TEXT) |
| byteValue.ToString() | CAST(@byteValue AS TEXT) |
| bytes.ToString() | CAST(@bytes AS TEXT) |
| charValue.ToString() | CAST(@charValue AS TEXT) |
| dateTime.ToString() | CAST(@dateTime AS TEXT) |
| dateTimeOffset.ToString() | CAST(@dateTimeOffset AS TEXT) |
| decimalValue.ToString() | CAST(@decimalValue AS TEXT) |
| doubleValue.ToString() | CAST(@doubleValue AS TEXT) |
| floatValue.ToString() | CAST(@floatValue AS TEXT) |
| guid.ToString() | CAST(@guid AS TEXT) |
| intValue.ToString() | CAST(@intValue AS TEXT) |
| longValue.ToString() | CAST(@longValue AS TEXT) |
| sbyteValue.ToString() | CAST(@sbyteValue AS TEXT) |
| shortValue.ToString() | CAST(@shortValue AS TEXT) |
| timeSpan.ToString() | CAST(@timeSpan AS TEXT) |
| uintValue.ToString() | CAST(@uintValue AS TEXT) |
| ushortValue.ToString() | CAST(@ushortValue AS TEXT) |
Date and time functions
| .NET | SQL | Added in |
|---|---|---|
| dateOnly.AddDays(value) | date(@dateOnly, @value || ' days') | |
| dateOnly.AddMonths(months) | date(@dateOnly, @months || ' months') | |
| dateOnly.AddYears(value) | date(@dateOnly, @value || ' years') | |
| dateOnly.Day | strftime('%d', @dateOnly) | |
| dateOnly.DayOfWeek | strftime('%w', @dateOnly) | |
| dateOnly.DayOfYear | strftime('%j', @dateOnly) | |
| DateOnly.FromDateTime(dateTime) | date(@dateTime) | EF Core 8.0 |
| dateOnly.Month | strftime('%m', @dateOnly) | |
| dateOnly.Year | strftime('%Y', @dateOnly) | |
| DateTime.Now | datetime('now', 'localtime') | |
| DateTime.Today | datetime('now', 'localtime', 'start of day') | |
| DateTime.UtcNow | datetime('now') | |
| dateTime.AddDays(value) | datetime(@dateTime, @value || ' days') | |
| dateTime.AddHours(value) | datetime(@dateTime, @d || ' hours') | |
| dateTime.AddMilliseconds(value) | datetime(@dateTime, (@value / 1000.0) || ' seconds') | |
| dateTime.AddMinutes(value) | datetime(@dateTime, @value || ' minutes') | |
| dateTime.AddMonths(months) | datetime(@dateTime, @months || ' months') | |
| dateTime.AddSeconds(value) | datetime(@dateTime, @value || ' seconds') | |
| dateTime.AddTicks(value) | datetime(@dateTime, (@value / 10000000.0) || ' seconds') | |
| dateTime.AddYears(value) | datetime(@dateTime, @value || ' years') | |
| dateTime.Date | datetime(@dateTime, 'start of day') | |
| dateTime.Day | strftime('%d', @dateTime) | |
| dateTime.DayOfWeek | strftime('%w', @dateTime) | |
| dateTime.DayOfYear | strftime('%j', @dateTime) | |
| dateTime.Hour | strftime('%H', @dateTime) | |
| dateTime.Millisecond | (strftime('%f', @dateTime) * 1000) % 1000 | |
| dateTime.Minute | strftime('%M', @dateTime) | |
| dateTime.Month | strftime('%m', @dateTime) | |
| dateTime.Second | strftime('%S', @dateTime) | |
| dateTime.Ticks | (julianday(@dateTime) - julianday('0001-01-01 00:00:00')) * 864000000000 | |
| dateTime.TimeOfDay | time(@dateTime) | |
| dateTime.Year | strftime('%Y', @dateTime) |
Note
Some SQL has been simplified for illustration purposes. The actual SQL is more complex to handle a wider range of values.
Numeric functions
| .NET | SQL | Added in |
|---|---|---|
| -decimalValue | ef_negate(@decimalValue) | |
| decimalValue - d | ef_add(@decimalValue, ef_negate(@d)) | |
| decimalValue * d | ef_multiply(@decimalValue, @d) | |
| decimalValue / d | ef_divide(@decimalValue, @d) | |
| decimalValue % d | ef_mod(@decimalValue, @d) | |
| decimalValue + d | ef_add(@decimalValue, @d) | |
| decimalValue < d | ef_compare(@decimalValue, @d) < 0 | |
| decimalValue <= d | ef_compare(@decimalValue, @d) <= 0 | |
| decimalValue > d | ef_compare(@decimalValue, @d) > 0 | |
| decimalValue >= d | ef_compare(@decimalValue, @d) >= 0 | |
| double.DegreesToRadians(degrees) | radians(@degrees) | EF Core 8.0 |
| double.RadiansToDegrees(radians) | degrees(@dradians) | EF Core 8.0 |
| doubleValue % d | mod(@doubleValue, @d) | |
| EF.Functions.Random() | abs(random() / 9223372036854780000.0) | |
| Math.Abs(value) | abs(@value) | |
| Math.Acos(value) | acos(@value) | EF Core 8.0 |
| Math.Acosh(d) | acosh(@d) | EF Core 8.0 |
| Math.Asin(d) | asin(@d) | EF Core 8.0 |
| Math.Asinh(d) | asinh(@d) | EF Core 8.0 |
| Math.Atan(d) | atan(@d) | EF Core 8.0 |
| Math.Atan2(y, x) | atan2(@y, @x) | EF Core 8.0 |
| Math.Atanh(d) | atanh(@d) | EF Core 8.0 |
| Math.Ceiling(d) | ceiling(@d) | EF Core 8.0 |
| Math.Cos(d) | cos(@d) | EF Core 8.0 |
| Math.Cosh(value) | cosh(@value) | EF Core 8.0 |
| Math.Exp(d) | exp(@d) | EF Core 8.0 |
| Math.Floor(d) | floor(@d) | EF Core 8.0 |
| Math.Log(d) | ln(@d) | EF Core 8.0 |
| Math.Log(a, newBase) | log(@newBase, @a) | EF Core 8.0 |
| Math.Log2(x) | log2(@x) | EF Core 8.0 |
| Math.Log10(d) | log10(@d) | EF Core 8.0 |
| Math.Max(val1, val2) | max(@val1, @val2) | |
| Math.Min(val1, val2) | min(@val1, @val2) | |
| Math.Pow(x, y) | pow(@x, @y) | EF Core 8.0 |
| Math.Round(d) | round(@d) | |
| Math.Round(d, digits) | round(@d, @digits) | |
| Math.Sign(d) | sign(@d) | EF Core 8.0 |
| Math.Sin(a) | sin(@a) | EF Core 8.0 |
| Math.Sinh(value) | sinh(@value) | EF Core 8.0 |
| Math.Sqrt(d) | sqrt(@d) | EF Core 8.0 |
| Math.Tan(a) | tan(@a) | EF Core 8.0 |
| Math.Tanh(value) | tanh(@value) | EF Core 8.0 |
| Math.Truncate(d) | trunc(@d) | EF Core 8.0 |
Tip
In addition to the methods listed here, corresponding generic math implementations
and MathF methods are also translated. For example, Math.Sin, MathF.Sin, double.Sin,
and float.Sin all map to the sin function in SQL.
Tip
SQL functions prefixed with ef are created by EF Core.
String functions
| .NET | SQL |
|---|---|
| char.ToLower(c) | lower(@c) |
| char.ToUpper(c) | upper(@c) |
| EF.Functions.Collate(operand, collation) | @operand COLLATE @collation |
| EF.Functions.Glob(matchExpression, pattern) | @matchExpression GLOB @pattern |
| EF.Functions.Like(matchExpression, pattern) | @matchExpression LIKE @pattern |
| EF.Functions.Like(matchExpression, pattern, escapeCharacter) | @matchExpression LIKE @pattern ESCAPE @escapeCharacter |
| Regex.IsMatch(input, pattern) | @input REGEXP @pattern |
| string.Compare(strA, strB) | CASE WHEN @strA = @strB THEN 0 ... END |
| string.Concat(str0, str1) | @str0 || @str1 |
| string.IsNullOrEmpty(value) | @value IS NULL OR @value = '' |
| string.IsNullOrWhiteSpace(value) | @value IS NULL OR trim(@value) = '' |
| stringValue.CompareTo(strB) | CASE WHEN @stringValue = @strB THEN 0 ... END |
| stringValue.Contains(value) | instr(@stringValue, @value) > 0 |
| stringValue.EndsWith(value) | @stringValue LIKE '%' || @value |
| stringValue.FirstOrDefault() | substr(@stringValue, 1, 1) |
| stringValue.IndexOf(value) | instr(@stringValue, @value) - 1 |
| stringValue.LastOrDefault() | substr(@stringValue, length(@stringValue), 1) |
| stringValue.Length | length(@stringValue) |
| stringValue.Replace(oldValue, newValue) | replace(@stringValue, @oldValue, @newValue) |
| stringValue.StartsWith(value) | @stringValue LIKE @value || '%' |
| stringValue.Substring(startIndex) | substr(@stringValue, @startIndex + 1) |
| stringValue.Substring(startIndex, length) | substr(@stringValue, @startIndex + 1, @length) |
| stringValue.ToLower() | lower(@stringValue) |
| stringValue.ToUpper() | upper(@stringValue) |
| stringValue.Trim() | trim(@stringValue) |
| stringValue.Trim(trimChar) | trim(@stringValue, @trimChar) |
| stringValue.TrimEnd() | rtrim(@stringValue) |
| stringValue.TrimEnd(trimChar) | rtrim(@stringValue, @trimChar) |
| stringValue.TrimStart() | ltrim(@stringValue) |
| stringValue.TrimStart(trimChar) | ltrim(@stringValue, @trimChar) |
Note
Some SQL has been simplified for illustration purposes. The actual SQL is more complex to handle a wider range of values.
Miscellaneous functions
| .NET | SQL |
|---|---|
| collection.Contains(item) | @item IN @collection |
| enumValue.HasFlag(flag) | @enumValue & @flag = @flag |
| nullable.GetValueOrDefault() | coalesce(@nullable, 0) |
| nullable.GetValueOrDefault(defaultValue) | coalesce(@nullable, @defaultValue) |
See also
Sodelujte z nami v storitvi GitHub
Vir za to vsebino najdete v storitvi GitHub, kjer lahko tudi ustvarite in pregledate težave in zahtevke za uveljavitev sprememb. Če želite več informacij, glejte naš vodnik za sodelavce.
