Symbiont 1.0.0

Symbiont

Hybrid data access for .NET: Dapper for reads, EF Core for writes — one session, one connection, one transaction.

The Stack Overflow-style split, packaged: EF Core keeps what it is great at (change tracking, migrations, relationships, transactions) and Dapper keeps what it is great at (raw SQL, DTO projection, minimal overhead). Symbiont wires the two together so they always share the DbContext's connection — and, inside a unit of work, its transaction.

// READ — Dapper (raw SQL, no change tracker in the way)
var page = await session.Query.QueryPagedAsync<OrderDto>(
    "SELECT id, total FROM orders WHERE total > @min",
    orderBy: "id", param: new { min = 100 }, page: 2, pageSize: 50, ct);

// WRITE — EF Core (tracking, validation, relationships)
session.Db.Orders.Add(order);
await session.SaveChangesAsync(ct);

// BOTH — one shared transaction; throw = both roll back
await session.ExecuteInTransactionAsync(async ct =>
{
    session.Db.Orders.Add(order);
    await session.SaveChangesAsync(ct);

    var count = await session.Query.QuerySingleAsync<int>(
        "SELECT COUNT(*) FROM orders WHERE customer_id = @id", new { id }, ct);
    if (count > limit) throw new InvalidOperationException(); // rollback
}, ct);

Install

dotnet add package Symbiont

Targets net8.0, net9.0, net10.0 (EF Core 8/9/10 respectively). Provider-agnostic core; Upsert/BulkInsert/paged queries support SQL Server, PostgreSQL, SQLite, MySQL/MariaDB.

Setup

builder.Services.AddDbContext<AppDbContext>(o => o.UseNpgsql(connectionString));
builder.Services.AddSymbiont<AppDbContext>();          // that's it

Then inject IDataSession<AppDbContext> (or non-generic IDataSession in single-context apps):

Why not plain Dapper + EF side by side?

Rolling your own hybrid has well-known traps. Symbiont removes them:

• Different connections/transactions. Naively injecting an IDbConnection next to a DbContext gives Dapper its own connection — reads miss uncommitted writes and nothing rolls back together. Symbiont always uses Database.GetDbConnection() and enlists in Database.CurrentTransaction.
• No CancellationToken. Dapper's simple APIs don't take one (its most-requested feature); you must build CommandDefinition by hand. Every Symbiont method takes ct — including multi-mapping overloads.
• Untestable static extension methods. IDapperQuery/IDataSession are plain interfaces — mock them with anything.
• Retry strategies break manual transactions. ExecuteInTransactionAsync wraps the provider execution strategy (EnableRetryOnFailure keeps working) and joins an already-open transaction instead of nesting.

Features

CancellationToken-first Dapper surface

QueryAsync, QueryFirstAsync/OrDefault, QuerySingleAsync/OrDefault, ExecuteScalarAsync, ExecuteAsync, 2- and 3-type multi-mapping, QueryMultipleAsync (multiple result sets in one round-trip) — all with ct support.

Paged queries

var page = await session.Query.QueryPagedAsync<ProductDto>(
    "SELECT id, name, price FROM products WHERE price > @min",
    orderBy: "price DESC, id", param: new { min = 10 }, page: 3, pageSize: 25, ct);
// page.Items, page.TotalCount, page.TotalPages, page.HasNextPage, page.HasPreviousPage

Provider-native paging (OFFSET..FETCH / LIMIT..OFFSET) plus a count query. Pass the SQL without ORDER BY; pass ordering via orderBy (never user input — it is concatenated).

Streaming

await foreach (var row in session.Query.QueryUnbufferedAsync<LogRow>(
    "SELECT * FROM logs WHERE day = @day", new { day }, ct))
{
    // rows are read lazily; constant memory for huge result sets
}

Upsert (the most-requested EF Core feature)

await session.UpsertAsync(new Setting { Key = "theme", Value = "dark" }, ct);
await session.UpsertRangeAsync(settings, ct); // one transaction, mixed insert/update

One provider-native statement — MERGE WITH (HOLDLOCK) (SQL Server), INSERT .. ON CONFLICT DO UPDATE (PostgreSQL/SQLite), INSERT .. ON DUPLICATE KEY UPDATE (MySQL) — generated from your EF Core model: table/column names, quoting via the provider's own ISqlGenerationHelper, primary key as conflict target, value converters (e.g. enum-to-string) applied. Requires an application-settable key (natural or client-generated); store-generated identity keys are rejected with a clear error.

Bulk insert

await session.BulkInsertAsync(fiveThousandRows, ct: ct);                    // store assigns identities
await session.BulkInsertAsync(importedRows, keepIdentity: true, ct: ct);    // keep explicit ids

Batched multi-row INSERT statements sized to the provider's parameter limit (2100 SQL Server / 999 SQLite / 65k PostgreSQL & MySQL), wrapped in one transaction. Honest note: this is a portable fast path — typically an order of magnitude faster than SaveChanges for large sets, but a native bulk API (SqlBulkCopy, PostgreSQL COPY) is still faster if you can take the provider dependency.

Observability

Every call is logged (Debug: SQL + duration; Warning above SlowQueryThreshold) and traced via an ActivitySource named "Symbiont" with db.statement tags:

tracing.AddSource("Symbiont"); // OpenTelemetry

Options

builder.Services.AddSymbiont<AppDbContext>(o =>
{
    o.EnableSqlLogging = true;                            // default: true
    o.SlowQueryThreshold = TimeSpan.FromMilliseconds(500);// default: 500 ms
    o.MatchNamesWithUnderscores = true;                   // snake_case -> PascalCase (flips Dapper's global switch)
    o.BulkBatchSize = 1000;                               // rows per INSERT statement
});

Which tool for which query?

Anti-patterns Symbiont won't save you from

• Don't hydrate EF-tracked entities with Dapper. Dapper results are detached; mutating and SaveChanges-ing them does nothing. Query DTOs with Dapper, entities with EF.
• Don't pass user input into orderBy — it is concatenated into SQL by design.
• Don't expect store-generated keys back from BulkInsertAsync — it bypasses the change tracker; use EF AddRange + SaveChanges when you need the ids.

Limitations (v1)

• Upsert/BulkInsert skip shadow properties (no CLR member to read) and don't support owned-type splitting, table-per-hierarchy discriminators or keyless entities.
• Not NativeAOT-compatible — Dapper's default mapper is reflection/IL based. (Dapper.AOT interop is on the roadmap.)
• MatchNamesWithUnderscores flips a process-global Dapper switch, not a per-context one (upstream Dapper limitation).
• SQL Server MERGE upserts use HOLDLOCK for atomicity; for extremely hot upsert paths measure against UPDATE+INSERT alternatives.

Sample

A runnable minimal API lives in samples/Symbiont.Sample.Api — paged Dapper reads, EF writes, upsert, bulk insert and the shared-transaction hybrid flow.

License

MIT