Engine · SQL

SQL, live queries, and raw ADO.NET

SQL generation here is the stock EF Core SQLite provider, untouched. Bsqlite replaces only the two services that assume synchronous I/O, and registers on every connection the same ef_* functions, EF_DECIMAL collation, and regexp that EF expects from a native SqliteConnection. Each panel below runs against this tab's database and shows exactly what it ran.

Client database OPFS

Backend

Fastest persistent tier. Real files in the origin private file system. Concurrent connections, no COOP/COEP.

Live

A query that re-runs itself

Live queries are table-level: Bsqlite reads the tables out of the generated SQL, then re-executes the query when any of them is written - by this tab or, over a BroadcastChannel, by another one. Include is honoured, so the customer below comes along.

Try Open Orders in a second tab and change a status. This table updates without a refresh and without polling.

Live

No orders yet.

csharp Pages/Queries.razor

_live = context.Orders
    .Include(o => o.Customer)
    .OrderByDescending(o => o.Id)
    .AsLiveQuery();

_live.Changed += (_, rows) =>
{
    _liveRows = rows;
    _ = InvokeAsync(StateHasChanged);
};

_liveRows = await _live.RefreshAsync();

LINQ

Comparing decimals in SQL

decimal is not a SQLite affinity, so EF stores it as TEXT and compares it with ef_compare under the EF_DECIMAL collation. Those functions run on 96-bit coefficients inside the worker - not on JavaScript numbers, which would quietly round.

Try Raise the threshold and run it: the generated SQL appears underneath.

Live

csharp Pages/Queries.razor

var query = context.Customers
    .AsNoTracking()
    .Where(c => c.CreditLimit >= _minCredit)
    .OrderBy(c => c.DisplayName);

_decimalSql = query.ToQueryString();   // the SQL below, straight from EF
_decimalRows = await query.ToListAsync();

Raw SQL

REGEXP, registered on every open

SQLite ships no regexp implementation; a host has to supply one. The worker registers it on every connection, so the pattern is a JavaScript RegExp - lookaround and backreferences included.

Try Try ^a, or northwind|alpine, against the seeded emails.

Live

csharp Pages/Queries.razor

// regexp is registered on every open, so raw SQL can use it too.
_regexRows = await context.Customers
    .FromSql($"SELECT * FROM \"Customers\" WHERE \"Email\" REGEXP {_pattern}")
    .AsNoTracking()
    .ToListAsync();

ADO.NET

No EF at all

The session exposes a DbConnection that is already open. Commands, parameters, and readers behave as you expect, with one rule: the async overloads only. The version below is read from wa-sqlite in the worker, not from a native e_sqlite3.

Live

csharp Commands on the session connection

// No EF: the session's connection is a DbConnection, already open.
await using var command = session.Connection.CreateCommand();
command.CommandText = "SELECT sqlite_version()";
var version = await command.ExecuteScalarAsync(cancellationToken);

// Parameters bind by CLR type; the binder picks TEXT / INTEGER / REAL / BLOB.
command.CommandText = "SELECT * FROM Products WHERE Price >= $min";
command.Parameters.Add(new BsqliteParameter { ParameterName = "$min", Value = 25.00m });
await using var reader = await command.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
    var sku = reader.GetString(reader.GetOrdinal("Sku"));
}

csharp Transactions

Edit this page on GitHub