Skip to content

Expressions

Jump to bottom
github-actions[bot] edited this page Sep 2, 2026 · 9 revisions

Expressions

LINQ expressions inside Where, Select and other methods are translated directly to SQL. The framework stays close to the shape of the LINQ query and does not rewrite the query just to make a method work.

Arithmetic

The standard arithmetic operators all work:

var results = await db.Table<Book>()
    .Where(b => (b.Price * 1.1) + 5 > 20)
    .ToListAsync();

var discounted = await db.Table<Book>()
    .Select(b => new { b.Title, Sale = b.Price * 0.9 })
    .ToListAsync();

var evens = await db.Table<Book>()
    .Where(b => b.Id % 2 == 0)
    .ToListAsync();

Supported operators are +, -, *, / and %.

A few arithmetic and comparison cases have SQLite-specific behavior, such as division by zero, floating-point domain errors and order comparisons on a nullable column. See Limitations.

Math Functions

C# SQL
Math.Abs(x) ABS(x)
Math.Round(x) round half to even (banker's), the .NET default, via a CASE over ROUND
Math.Round(x, digits) round half to even (banker's), the .NET default, via a CASE over ROUND
Math.Round(x, MidpointRounding.AwayFromZero) ROUND(x)
Math.Round(x, MidpointRounding.ToEven) round half to even (banker's), via a CASE over ROUND
Math.Round(x, digits, MidpointRounding.AwayFromZero) ROUND(x, digits)
Math.Round(x, digits, MidpointRounding.ToEven) round half to even (banker's), via a CASE over ROUND
Math.Floor(x) FLOOR(x)
Math.Ceiling(x) CEIL(x)
Math.Truncate(x) TRUNC(x)
Math.Pow(x, y) POWER(CAST(x AS REAL), y)
Math.Sqrt(x) SQRT(x)
Math.Cbrt(x) CASE WHEN x >= 0 THEN POWER(x, 1.0/3.0) ELSE -POWER(-x, 1.0/3.0) END
Math.Exp(x) EXP(x)
Math.Log(x) LN(x)
Math.Log(x, base) LOG(base, x)
Math.Log10(x) LOG10(x)
Math.Log2(x) LOG2(x)
Math.Sign(x) CASE WHEN x > 0 THEN 1 WHEN x < 0 THEN -1 ELSE 0 END
Math.Max(x, y) MAX(x, y)
Math.Min(x, y) MIN(x, y)
Math.Clamp(x, min, max) MAX(min, MIN(x, max))
Math.Sin(x) SIN(x)
Math.Cos(x) COS(x)
Math.Tan(x) TAN(x)
Math.Asin(x) ASIN(x)
Math.Acos(x) ACOS(x)
Math.Atan(x) ATAN(x)
Math.Atan2(y, x) ATAN2(y, x)
Math.Sinh(x) SINH(x)
Math.Cosh(x) COSH(x)
Math.Tanh(x) TANH(x)
Math.Asinh(x) ASINH(x)
Math.Acosh(x) ACOSH(x)
Math.Atanh(x) ATANH(x) 
var results = await db.Table<Book>()
    .Where(b => Math.Abs(b.Price - 10) < 1)
    .ToListAsync();

var rounded = await db.Table<Book>()
    .Select(b => new { b.Title, Price = Math.Round(b.Price, 2) })
    .ToListAsync();

String Methods

C# SQL
s.Length LENGTH(s)
s.ToUpper() / s.ToUpperInvariant() UPPER(s)
s.ToLower() / s.ToLowerInvariant() LOWER(s)
s.Trim() TRIM(s, <whitespace>)
s.TrimStart() LTRIM(s, <whitespace>)
s.TrimEnd() RTRIM(s, <whitespace>)
s.Contains(value) s LIKE '%value%' ESCAPE '\'
s.StartsWith(value) s LIKE 'value%' ESCAPE '\'
s.EndsWith(value) s LIKE '%value' ESCAPE '\'
s.Equals(value) s IS value
s.Replace(old, new) REPLACE(s, old, new)
s.Substring(start, length) SUBSTR(s, start + 1, length)
s[index] SUBSTR(s, index + 1, 1)
s.IndexOf(value) INSTR(s, value) - 1
s.IndexOf(value, startIndex) INSTR(SUBSTR(s, startIndex + 1), value) adjusted back to a 0-based absolute index or -1
s.LastIndexOf(value) CASE WHEN LENGTH(value) = 0 THEN LENGTH(s) ELSE COALESCE((WITH RECURSIVE find_pos(pos, rem) AS (SELECT 0, s UNION ALL SELECT pos + INSTR(rem, value), SUBSTR(rem, INSTR(rem, value) + 1) FROM find_pos WHERE INSTR(rem, value) > 0) SELECT MAX(pos) - 1 FROM find_pos WHERE pos > 0), -1) END
s.LastIndexOf(value, startIndex) the same LastIndexOf search run over the prefix SUBSTR(s, 1, startIndex + 1), so the match must fall within the first startIndex + 1 characters
s.Insert(index, value) SUBSTR(s, 1, index) || value || SUBSTR(s, index + 1)
s.Remove(start) SUBSTR(s, 1, start)
s.Remove(start, count) SUBSTR(s, 1, start) || SUBSTR(s, start + count + 1)
s.PadLeft(n) CASE WHEN LENGTH(s) >= n THEN s ELSE (SELECT SUBSTR(REPLACE(HEX(ZEROBLOB(n - LENGTH(s))), '00', ' '), 1, n - LENGTH(s)) || s) END
s.PadLeft(n, c) CASE WHEN LENGTH(s) >= n THEN s ELSE (SELECT SUBSTR(REPLACE(HEX(ZEROBLOB(n - LENGTH(s))), '00', c), 1, n - LENGTH(s)) || s) END
s.PadRight(n) CASE WHEN LENGTH(s) >= n THEN s ELSE (s || (SELECT SUBSTR(REPLACE(HEX(ZEROBLOB(n - LENGTH(s))), '00', ' '), 1, n - LENGTH(s)))) END
s.PadRight(n, c) CASE WHEN LENGTH(s) >= n THEN s ELSE (s || (SELECT SUBSTR(REPLACE(HEX(ZEROBLOB(n - LENGTH(s))), '00', c), 1, n - LENGTH(s)))) END
s + other s || other
string.Concat(a, b, ...) a || b || ...
string.Join(sep, values) val1 || sep || val2 || ...
string.Compare(a, b) CASE WHEN a = b THEN 0 WHEN a < b THEN -1 ELSE 1 END
string.Compare(a, indexA, b, indexB, length) the same Compare, run over SUBSTR(a, indexA + 1, length) and SUBSTR(b, indexB + 1, length)
s.CompareTo(other) CASE WHEN s = other THEN 0 WHEN s < other THEN -1 ELSE 1 END
string.IsNullOrEmpty(s) (COALESCE(s, '') = '')
string.IsNullOrWhiteSpace(s) (TRIM(COALESCE(s, ''), CHAR(9, 10, 11, 12, 13, 32, 133, 160, 5760, 8192, 8193, 8194, 8195, 8196, 8197, 8198, 8199, 8200, 8201, 8202, 8232, 8233, 8239, 8287, 12288)) = '')

In +, string.Concat and string.Join, a nullable string column is wrapped in COALESCE(col, ''), so a NULL value becomes an empty string. This matches .NET, where string.Concat and string.Join treat a null argument as empty.

String length, comparison, ordering and Substring bounds have SQLite-specific behavior. See Limitations.

Contains, StartsWith and EndsWith use LIKE, which is case-insensitive for ASCII by default. To make them case-sensitive, build the database with UseCaseSensitiveStringComparison(). They then translate to INSTR / SUBSTR instead of LIKE. See Storage Options.

Pass StringComparison.OrdinalIgnoreCase to Contains, StartsWith or EndsWith to force a case-insensitive match regardless of that option:

var results = await db.Table<Book>()
    .Where(b => b.Title.Contains("test", StringComparison.OrdinalIgnoreCase))
    .ToListAsync();

The ?? operator translates to COALESCE:

var results = await db.Table<Book>()
    .Select(b => new { b.Id, Notes = b.Notes ?? "No notes" })
    .ToListAsync();

Char Methods

C# SQL
char.ToLower(c) / char.ToLowerInvariant(c) LOWER(c)
char.ToUpper(c) / char.ToUpperInvariant(c) UPPER(c)
char.IsWhiteSpace(c) TRIM(c, CHAR(9, 10, 11, 12, 13, 32, 133, 160, 5760, 8192, 8193, 8194, 8195, 8196, 8197, 8198, 8199, 8200, 8201, 8202, 8232, 8233, 8239, 8287, 12288)) = ''
char.IsAsciiDigit(c) c BETWEEN '0' AND '9'
char.IsAsciiLetter(c) LOWER(c) BETWEEN 'a' AND 'z'
char.IsAsciiLetterOrDigit(c) (c BETWEEN '0' AND '9') OR (LOWER(c) BETWEEN 'a' AND 'z')
char.IsAsciiLetterLower(c) c BETWEEN 'a' AND 'z'
char.IsAsciiLetterUpper(c) c BETWEEN 'A' AND 'Z'

DateTime Properties

You can read individual components of a DateTime column directly:

var recent = await db.Table<Order>()
    .Where(o => o.PlacedAt.Year == 2024 && o.PlacedAt.Month == 12)
    .ToListAsync();

var years = await db.Table<Order>()
    .Select(o => o.PlacedAt.Year)
    .ToListAsync();

Supported properties are Year, Month, Day, Hour, Minute, Second, Millisecond, Ticks, DayOfWeek, DayOfYear, Date and TimeOfDay.

DateTime Methods

var shifted = await db.Table<Order>()
    .Select(o => new { o.Id, Due = o.PlacedAt.AddDays(30) })
    .ToListAsync();

Supported methods are Add, AddYears, AddMonths, AddDays, AddHours, AddMinutes, AddSeconds, AddMilliseconds, AddMicroseconds and AddTicks. The static DateTime.IsLeapYear(year) method also works with a constant, captured value or translated integer expression.

DateOnly Properties

You can read individual components of a DateOnly column the same way as DateTime:

var recent = await db.Table<Order>()
    .Where(o => o.Date.Year == 2024 && o.Date.Month == 12)
    .ToListAsync();

Supported properties are Year, Month, Day, DayOfWeek and DayOfYear.

DateOnly Methods

var shifted = await db.Table<Order>()
    .Select(o => new { o.Id, Due = o.Date.AddDays(30) })
    .ToListAsync();

Supported methods are AddYears, AddMonths and AddDays.

DateTimeOffset Properties

Supported properties are Year, Month, Day, Hour, Minute, Second, Millisecond, Ticks, DayOfWeek, DayOfYear, Date and TimeOfDay.

DateTimeOffset Methods

Supported methods are Add, AddYears, AddMonths, AddDays, AddHours, AddMinutes, AddSeconds, AddMilliseconds, AddMicroseconds and AddTicks.

TimeOnly Properties

Supported properties are Hour, Minute, Second and Ticks.

TimeOnly Methods

Supported methods are Add, AddHours and AddMinutes.

TimeSpan Properties

Supported properties are Days, TotalDays, Hours, TotalHours, Minutes, TotalMinutes, Seconds, TotalSeconds, Milliseconds, TotalMilliseconds and Ticks.

TimeSpan Methods

var results = await db.Table<Order>()
    .Where(o => o.Duration.Subtract(TimeSpan.FromHours(1)).TotalHours > 5)
    .ToListAsync();

Supported methods are Add, Subtract, Negate and Duration.

You can also call the static TimeSpan creation methods inside an expression:

var results = await db.Table<Order>()
    .Where(o => o.Duration == TimeSpan.FromHours(o.Id))
    .ToListAsync();

Supported static methods are FromDays, FromHours, FromMinutes, FromSeconds, FromMilliseconds, FromMicroseconds and FromTicks.

Guid

Guid columns support equality comparisons:

Guid id = Guid.NewGuid();

var result = await db.Table<Order>()
    .Where(o => o.TrackingId == id)
    .FirstOrDefaultAsync();

Binary Data

A byte[] column supports Length, value equality through == or SequenceEqual, and Contains of a single byte. Reading an individual byte by index is not supported.

var matches = await db.Table<FileRecord>()
    .Where(f => f.Content.Contains((byte)0x7F))
    .ToListAsync();

Clone this wiki locally