ADO.NET and EF Core Embedded Storage Tuning

CSharpDB.Data and CSharpDB.EntityFrameworkCore now expose the same embedded storage-engine tuning surface that already existed at the engine level. That lets ADO.NET and EF Core callers opt into the read, write, and hybrid performance presets documented in src/CSharpDB.Storage/README.md without changing the default storage-tuning profile. The connection-pooling lifecycle is documented separately below.

Summary

• Storage tuning remains opt-in. Provider-created EF Core file connections enable pooling by default; ADO.NET callers still opt in with Pooling=true.
• Connection strings expose a small convenience surface: Storage Preset and Embedded Open Mode.
• Full engine composition still lives on DatabaseOptions and HybridDatabaseOptions.
• EF Core can apply the same settings through the provider-specific CSharpDbDbContextOptionsBuilder.

Public Surface

ADO.NET:

• CSharpDbConnection.DirectDatabaseOptions
• CSharpDbConnection.HybridDatabaseOptions
• additive CSharpDbConnection constructor overloads for direct and hybrid options
• CSharpDbConnectionStringBuilder.StoragePreset
• CSharpDbConnectionStringBuilder.EmbeddedOpenMode

EF Core:

• UseDirectDatabaseOptions(DatabaseOptions)
• UseHybridDatabaseOptions(HybridDatabaseOptions)
• UseStoragePreset(CSharpDbStoragePreset)
• UseEmbeddedOpenMode(CSharpDbEmbeddedOpenMode)

Connection-string keywords:

• Storage Preset
• Embedded Open Mode

Supported Storage Preset values:

• DirectLookupOptimized
• DirectColdFileLookup
• HybridFileCache
• WriteOptimized
• LowLatencyDurableWrite

Supported Embedded Open Mode values:

• Direct
• HybridIncrementalDurable
• HybridSnapshot

Both keywords parse case-insensitively.

ADO.NET Examples

Use full direct options when you want exact engine control:

using CSharpDB.Data;
using CSharpDB.Engine;

var directOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseWriteOptimizedPreset());

await using var connection = new CSharpDbConnection(
    "Data Source=ingest.cdb",
    directOptions);

await connection.OpenAsync();

Use hybrid open mode plus direct options when you want a lazy-resident file-backed runtime:

using CSharpDB.Data;
using CSharpDB.Engine;

var directOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseDirectLookupOptimizedPreset());

var hybridOptions = new HybridDatabaseOptions
{
    PersistenceMode = HybridPersistenceMode.IncrementalDurable,
    HotTableNames = ["users", "sessions"],
};

await using var connection = new CSharpDbConnection("Data Source=app.cdb")
{
    DirectDatabaseOptions = directOptions,
    HybridDatabaseOptions = hybridOptions,
};

await connection.OpenAsync();

Use connection-string convenience keywords when a named preset is enough:

await using var connection = new CSharpDbConnection(
    "Data Source=app.cdb;Storage Preset=WriteOptimized;Embedded Open Mode=HybridIncrementalDurable");

await connection.OpenAsync();

EF Core Examples

Apply engine options directly:

using CSharpDB.Engine;
using CSharpDB.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

var directOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseWriteOptimizedPreset());

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseCSharpDb(
        "Data Source=app.cdb",
        csharpdb => csharpdb.UseDirectDatabaseOptions(directOptions))
    .Options;

Apply named presets and embedded open mode through the provider builder:

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseCSharpDb(
        "Data Source=app.cdb",
        csharpdb =>
        {
            csharpdb.UseStoragePreset(CSharpDbStoragePreset.WriteOptimized);
            csharpdb.UseEmbeddedOpenMode(CSharpDbEmbeddedOpenMode.HybridIncrementalDurable);
        })
    .Options;

If you pass an existing CSharpDbConnection, EF Core validates that any provider builder tuning matches the supplied connection. It does not mutate the existing connection object.

Precedence and Pooling

• Explicit DirectDatabaseOptions override Storage Preset.
• Explicit HybridDatabaseOptions override Embedded Open Mode.
• Connection-string preset and open-mode keywords only fill gaps when the corresponding explicit options object is absent.
• File-backed embedded pooling keeps one warm engine alive across logical open/close cycles. ADO.NET enables it explicitly; provider-created EF Core connections enable it by default unless the connection string specifies Pooling=false.
• Max Pool Size bounds simultaneous logical sessions over that warm engine rather than creating that many physical engines.
• Repeated opens on one connection reuse its validated embedded configuration. Short-lived connections using the same live connection-string instance can also reuse a weak prepared plan for an absolute pooled file target.
• Existing healthy pools use a direct checkout path. Logical close avoids reader and temporary-state cleanup when the session is proven clean, while engine-observed temporary contexts are still cleared conservatively.
• Pool identity is options-aware in v1.
• The pool key includes the normalized file target, max pool size, effective embedded open mode, effective preset selection, and explicit options object identity.
• Separate explicit options object instances do not share a pool in v1 even if their contents are equivalent.
• ClearPool(connectionString) clears all pooled entries for the normalized file-backed target.

For the lowest ADO.NET lifecycle overhead, keep the connection string in a reused variable instead of rebuilding an equivalent string for every connection. Relative file paths continue to follow the process working directory on each reopen, and explicit options objects remain isolated by identity.

Unsupported Cases

• Remote Http, Grpc, and NamedPipes connections reject embedded tuning.
• Named shared-memory databases reject embedded tuning.
• Private :memory: databases support direct tuning, but not hybrid open modes.
• EF Core rejects endpoint connections, non-direct transports, and named shared-memory databases.

Practical Preset Guidance

Use these as the first measurement targets:

• WriteOptimized for file-backed durable ingest and general write-heavy embedded workloads.
• LowLatencyDurableWrite only as a measure-first variant when you want to test deferred advisory planner-stat persistence.
• DirectLookupOptimized for hot local file-backed lookup workloads.
• DirectColdFileLookup for colder or cache-pressured direct file reads where memory-mapped clean-page reads help.
• HybridFileCache for explicit bounded file-cache experiments.

As of April 20, 2026, the repository benchmark guidance still treats WriteOptimized as the stable first preset for file-backed durable write paths, while LowLatencyDurableWrite remains a workload-specific experiment rather than a blanket default change.