H2Sharp in progress

linq2db in progress

The linq2db provider lives in its own repository, dimzon/h2sharp-linq2db — written from scratch against linq2db's public API and the engine's public API. Unlike the Entity Framework Core provider, this one is not a fork of another provider.

What it generates is H2's SQL, checked against H2's frozen documentation and against the reference engine on the JVM.

Using it

using LinqToDB;
using LinqToDB.DataProvider.H2Sharp;

await using var db = H2SharpTools.CreateDataConnection("jdbc:h2:mem:mydb");

var rows = await db.GetTable<Row>()
    .Where(r => r.Price > 10m)
    .OrderBy(r => r.Id)
    .ToListAsync();

The connection string is the JDBC URL. The ADO.NET spelling works too — Data Source=mem:mydb names the same database and is translated; the settings a URL carries (MODE=PostgreSQL and the like) are the connection string's other keys.

What it generates

Identifiersalways quoted, the way H2 documents it: an unquoted identifier folds to upper case, a quoted one does not
TypesH2's own: varchar, char, numeric, varbinary, uuid, json, clob, blob, timestamp, time, boolean, smallint, integer, bigint, real, double
PagingLIMIT n / OFFSET n
Concatenation||, which H2 has
Parametersnamed @name, which the engine rewrites into ? before the statement is parsed
Table aliaswithout the AS: FROM "Rows" "r" — the grammar allows both spellings ([ [ AS ] newTableAlias ]), and this is the one the engine reads

State

Buildsyes, against the released linq2db 6.5.0; .NET 8 and .NET 10
Public APIH2SharpTools.GetDataProvider, CreateDataConnection, H2SharpProvider, H2SharpVersion
Schema providerreads INFORMATION_SCHEMA the way H2 2.5 keeps it
Member translationonly the common one — H2-specific date and math translation is not written yet
Working against the enginequery, insert, filter with a parameter, order

Port gaps it is waiting on

Listed in the provider's own docs/DIVERGENCES.md, where each one says who has to fix it — here they are all port defects, not limits of H2:

GapSymptom
INFORMATION_SCHEMA catalogue differs from the referencethe catalogue name is the database path from the URL (mem:db), where H2 2.5 uses the database name; the collation column is empty
DB_CLOSE_DELAY and file databasesNot ported yet: Engine.openSession: persistent databases need the file store — a pooled connection takes the database with it
The engine's catalogue from the ADO layerH2Connection.Session is internal, while Schema.GetAllTablesAndViews wants a SessionLocal
JOINFROM T3 a LEFT JOIN T3 b — expected statement
AS before a table aliasSELECT a.Id FROM T3 AS a — expected statement
identity DDLCREATE TABLE T1 (Id INT GENERATED BY DEFAULT AS IDENTITY …) — expected )
CREATE SEQUENCECREATE SEQUENCE "foo" AS integer … — expected TABLE
IN with a subqueryParser.parseQuerySelect: IN with a subquery is not ported

How the first bug was found

Worth telling, because it is the method the whole project follows: an INSERT wrote the column names instead of the values. The cause was not in the provider's own code but in linq2db's base Convert, which writes a parameter's name as it is — because the prefix is each provider's business: PostgreSQL and Oracle write :, H2 must write @. It was found with an instrumented local copy of linq2db's core, with the insert expression and the generated SQL printed under LINQ2DB_DEBUG_SQL=1. A published package cannot be instrumented, so the build moved to a local copy of the sources, with a property to point at it — building against the released package stays possible.

Also in that repository: docs/USAGE.md is the guide (connecting, a data context, mapping, H2's functions, window functions, scaffolding, reading the catalogue, the SQL it generates, and what does not work yet), plus AGENTS.md for the rules of the work and UPSTREAM.md for its origin. License: MIT.