Document why linq2db generation does not fit the SQL-first contract

Context

The linq2db target was removed from the C# generator in fb7db77, and its generated examples were removed from #9 in 5d6a629. ADO.NET and Dapper remain supported.

This issue records the design problem and the alternatives considered, following #12 for Hibernate and Spring repositories. It is not a request to restore the removed target.

sqlc-ydb starts with named YQL queries and generates typed execution helpers. Idiomatic LINQ-to-database development starts with C# expressions and lets the provider generate SQL. These are opposite directions of query ownership. Raw SQL can coexist with LINQ in an application, but merely using linq2db to execute SQL does not provide a useful generated LINQ API.

The two workflows

In the SQL-first workflow, the developer writes:

-- name: ListAuthors :many
SELECT id, name, bio
FROM authors
WHERE name = $name
ORDER BY id;

The generator supplies parameter and result types and a method such as:

Task<IReadOnlyList<ListAuthorsRow>> ListAuthorsAsync(
    string name,
    CancellationToken cancellationToken = default);

The authored YQL defines filtering, ordering and projection.

With LINQ, the developer instead composes expressions over mapped tables:

var rows = await db.GetTable<Author>()
    .Where(author => author.Name == name)
    .OrderBy(author => author.Id)
    .Select(author => new ListAuthorsRow(author.Id, author.Name, author.Bio))
    .ToListAsync(cancellationToken);

Here the expression tree defines the query, and the provider translates it. The linq2db documentation presents this expression-based, type-safe workflow. linq2db does not require ORM change tracking; the conflict concerns query ownership, not entity lifecycle tracking.

The following examples illustrate API shapes, not verified YDB provider integration. They omit connection setup and mapping configuration.

Option 1: execute the original YQL through linq2db

The removed generator used this shape (simplified):

return await connection.QueryToListAsync(
    ListAuthorsRowFrom,
    "SELECT id, name, bio FROM authors WHERE name = $name ORDER BY id;",
    cancellationToken,
    new DataParameter("$name", name, DataType.NVarChar));

It supplied a reader-to-row mapper rather than generating LINQ expressions. The exact YDB type binding must be validated separately; this historical example is not a recommended binding recipe.

This preserves SQL-first query ownership and could be implemented. However, it adds a framework dependency and a separate adapter/test matrix while offering the same fixed-query execution already covered by ADO.NET and Dapper. It does not expose table expressions, provider-side composition or useful LINQ-specific behavior.

Calling LINQ operators after materialization is not a solution:

var rows = await queries.ListAuthorsAsync(name, cancellationToken);
var firstTen = rows.Where(row => row.Bio != null).Take(10);

The additional filtering and limit run in memory after the database result has been read. They do not become YQL predicates or a server-side limit. Wrapping this collection in AsQueryable() would not restore database execution.

A raw-SQL adapter might still help an application that already owns a linq2db connection or transaction, but that narrower interoperability benefit needs concrete demand. It does not justify advertising a LINQ code generator by itself.

Option 2: translate YQL into LINQ expressions

A restricted generator could translate the input SELECT into:

public IQueryable<ListAuthorsRow> ListAuthors(string name) =>
    db.GetTable<Author>()
        .Where(author => author.Name == name)
        .OrderBy(author => author.Id)
        .Select(author => new ListAuthorsRow(author.Id, author.Name, author.Bio));

The pipeline becomes:

Authored YQL -> generated C# expression tree -> provider-generated SQL/YQL

The caller could add .Where(...) or .Take(...) before execution, but the resulting query would be a composition built on the original query, rather than execution of that named query alone. Deferred execution also changes the helper contract: enumeration controls when execution happens and which connection/transaction must remain alive.

For even a restricted translation, we must establish equivalence for column mappings, projections, null semantics, joins, aggregation, ordering and parameter types. YQL-specific functions, AS_TABLE, typed values, table hints, UPSERT and mutation result clauses require explicit provider capabilities or rejection. There is no general equivalence established by translating C# syntax alone.

For example:

-- name: UpsertAuthor :exec
UPSERT INTO authors (id, name, bio)
VALUES ($id, $name, $bio);

must not silently become a provider operation that performs a different sequence of reads/writes or changes atomicity. Likewise, translating RETURNING into a separate SELECT would need a new, explicitly justified semantic contract.

This is technically possible for a documented subset. For the current product it creates a second translation layer and substantial semantic maintenance work without an established benefit over writing the LINQ expression directly. Raw-SQL fallback would also create two different execution models within one target.

Option 3: generate table mappings and let users write LINQ

A schema-oriented generator could produce:

[Table(Name = "authors")]
public sealed class Author
{
    [PrimaryKey, Column(Name = "id")]
    public ulong Id { get; set; }

    [Column(Name = "name")]
    public string Name { get; set; } = null!;

    [Column(Name = "bio")]
    public string? Bio { get; set; }
}

Users would then write their own LINQ queries against GetTable<Author>().

This is a coherent schema-first workflow, but named queries in queries.sql no longer drive the generated API. Adding generic CRUD methods would move further from the sqlc contract by introducing operations that were never authored as named queries. Such tooling may be useful independently; it is not a reason to restore this target in sqlc-ydb.

Decision and criteria for reconsideration

Keep linq2db generation excluded. Use ADO.NET or Dapper for generated helpers that execute authored YQL. This is a product-scope decision, not a claim that linq2db cannot execute raw SQL or that a limited translator is mathematically impossible.

Reconsider only with a concrete consumer scenario and an agreed contract that:

• provides useful framework integration beyond duplicating existing SQL adapters;
• preserves named-query semantics, or explicitly defines a separate composable-query contract;
• documents supported translations and rejects unsupported YQL without silent fallback;
• preserves YDB parameter/result types, transaction ownership, cancellation and mutation semantics;
• is validated with compiling examples and integration tests against the supported YDB provider.

Related: #9, #12; current compatibility contract, C# generation.

Start with docs/compatibility.md and docs/csharp.md, then compare them with the decision and criteria in this issue. Document why linq2db remains excluded, how ADO.NET and Dapper fit the SQL-first contract, and what would be required for reconsideration. Done means the compatibility rationale and supported alternatives are clear without restoring the removed target.