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.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) | |
| 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
Collaborate with us on GitHub
The source for this content can be found on GitHub, where you can also create and review issues and pull requests. For more information, see our contributor guide.
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for