DuckDB.EFCoreProvider documentation

A practical guide to native DuckDB persistence, DuckLake catalogs, high-throughput writes, open-format analytics, and managed hot-to-cold data lifecycles — for EF Core 10 on .NET 10.

This page documents provider 1.17.0.

This is the provider LakeHold's own data plane runs on. What LakeHold's engine exercises in production is documented here as the public contract.

Overview

DuckDB.EFCoreProvider is a complete EF Core 10 relational provider for applications that need embedded analytics, local persistence, open-format data access, or a shared DuckLake catalog.

Know the engine boundary. DuckDB is an embedded, single-writer analytical engine. Use it for reporting, ETL, local stores, edge workloads, and Parquet-backed analytics — not as a high-concurrency, multi-instance OLTP system of record.

Install

1. Add the provider

The same package contains native DuckDB and the DuckLake backend profile.

dotnet add package DuckDB.EFCoreProvider

2. Register a context

Point EF Core at a persisted DuckDB file. Use :memory: only when you explicitly keep the underlying connection open.

builder.Services.AddDbContext<AnalyticsContext>(options =>
    options.UseDuckDB("Data Source=analytics.duckdb"));

3. Create or migrate, then query

Use normal EF Core lifecycle APIs and compose LINQ against your DbSet properties.

await db.Database.MigrateAsync();

var totals = await db.Orders
    .AsNoTracking()
    .GroupBy(order => order.Region)
    .Select(group => new {
        Region = group.Key,
        Revenue = group.Sum(order => order.Total)
    })
    .ToListAsync();

Choose a backend

Both backends ship in the same package. The choice is about who owns the schema and write coordination.

Configure

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseDuckDB(
        "Data Source=analytics.duckdb",
        duckdb => duckdb
            .EnableBulkInsertBatching()
            .EnableBulkUpdateBatching()
            .MemoryLimit("4GB")
            .Threads(4)
            .FileSearchPath("/data,/data/archive")
            .LoadExtension("httpfs"));

Extension provisioning is deliberate. The provider supports install-and-load, load-only/preinstalled, and caller-managed extension modes. Production images can avoid runtime downloads while still declaring what the provider depends on.

MemoryLimit and Threads configure DuckDB database-instance settings when a connection opens. Every connection to the same database should use compatible values; they are resource limits, not per-query isolation controls.

Model and query

Relational model

Keys, relationships, concurrency tokens, JSON, arrays, lists, temporal values, GUIDs, binary values, and optional spatial mappings are supported.

modelBuilder.Entity<Invoice>(entity =>
{
    entity.HasKey(invoice => invoice.Id);
    entity.Property(invoice => invoice.Id)
        .UseAutoIncrement();
    entity.Property(invoice => invoice.Reference)
        .IsConcurrencyToken();
    entity.HasMany(invoice => invoice.Lines)
        .WithOne(line => line.Invoice);
});

Analytical LINQ

Compose joins, grouping, aggregates, ordering, paging, string/math/temporal functions, arrays, JSON traversal, and set operations.

var revenue = await db.InvoiceLines
    .AsNoTracking()
    .Where(line => line.Invoice.IssuedOn >= start)
    .GroupBy(line => line.Invoice.Region)
    .Select(group => new
    {
        Region = group.Key,
        Revenue = group.Sum(line =>
            line.Quantity * line.UnitPrice)
    })
    .OrderByDescending(row => row.Revenue)
    .ToListAsync();

Query command plans

Provider 1.17.0 exposes an application-independent compiler/tooling boundary. It translates a single-command IQueryable<T> or supported terminal operator without opening the connection, and returns the exact DuckDB command text plus immutable parameter metadata and values.

var query = db.Invoices
    .Where(invoice => invoice.IssuedOn >= start)
    .Select(invoice => new { invoice.Region, invoice.Total });

var plan = db.Database.GetDuckDBCommandPlan(query);

await using var result = await db.Database.SqlQueryDynamicCommandAsync(
    plan,
    cancellationToken);

Dedicated extractors cover Count, LongCount, Any, Min, Max, Sum, and Average. Predicates and selectors are composed before extraction; numeric Sum and Average use a supported numeric or nullable numeric projection. Split/multi-command queries fail explicitly. A replayed aggregate exposes DuckDB's database value without EF's client-side empty-sequence result shaping.

SqlQueryDynamicCommandAsync(sql, parameters, ...) also executes existing named ADO.NET commands without rewriting SQL braces or mutating caller-owned parameters. Dynamic schema tools can call GetDuckDBStoreTypeMapping(storeType) to distinguish scalar EF properties, STRUCT complex properties, raw-reader-only types, and unsupported types instead of maintaining a duplicate map.

These APIs translate and replay; they do not sandbox authored code or authorize a query. A hosting tool still owns its source policy, schema/model generation, tenant credentials, read-only SQL validation, execution limits, and audit. LakeHold's optional isolated C# LINQ Workbench planner is a real consumer of this contract. See the full provider guide and the LakeHold integration guide.

Write paths

Three write paths trade EF semantics against raw throughput. Choose semantics first, then speed.

// Tracked domain write
db.Invoices.Add(invoice);
await db.SaveChangesAsync();

// Raw ingestion fast path
await db.BulkInsertAsync(importedRows);

// Primary-key insert or update
await db.UpsertAsync(synchronisedRows);

File analytics

Parquet (columnar analytics), CSV (with schema auto-detection), JSON (document ingestion), and remote URLs via httpfs or Azure can all be mapped and queried directly.

Query files as entities

[FromParquet("archive/invoices/*.parquet")]
public sealed class InvoiceSnapshot
{
    public int Id { get; set; }
    public string Region { get; set; } = "";
    public decimal Total { get; set; }
}

Export translated queries

await context.Database.ExportToParquetAsync(
    context.Events.Where(row => row.Timestamp < cutoff),
    "/data/archive/events",
    options => options
        .PartitionBy(row => row.Category)
        .OverwriteOrIgnore()
        .Compression(DuckDBParquetCompression.Zstd),
    cancellationToken);

Object storage is a connection concern. Load the required DuckDB extension and create secrets through ConfigureConnection. The provider supports S3-compatible paths, GCS, R2, and Azure-backed archive URLs while keeping credentials outside models and generated SQL.

Dynamic SQL results

Use the dynamic result API when analytical SQL is known at runtime but its columns are not. The provider opens the reader, exposes ordered DuckDB and CLR metadata, and streams row-owned values without buffering the complete result set.

await using var result = await context.Database
    .SqlQueryDynamicRawAsync(sql, cancellationToken);

foreach (var column in result.Columns)
{
    Console.WriteLine(
        $"{column.Name}: {column.DuckDBTypeName} / {column.ClrType.Name}");
}

await foreach (var row in result.ReadRowsAsync(cancellationToken))
{
    // ReadOnlyMemory<object?>, aligned to Columns by ordinal.
}

The dynamic API is a row-result stream. DuckDB.NET currently reports RecordsAffected == -1 on its reader path, so it cannot honestly infer a DML count. When the SQL is known to be DML and the affected-row count matters, use ExecuteSqlRawAsync instead.

Raw SQL remains trusted SQL. Parameterize values, but never splice untrusted identifiers or SQL fragments into raw text. The provider normalizes database nulls and preserves DuckDB.NET nested values; it deliberately does not choose a UI row limit or JSON serialization policy.

See also: the entity and raw-reader type-mapping contracts.

Types and spatial

EF entity properties and raw DuckDB.NET reader values are separate contracts. Model mappings follow configured EF types; dynamic SQL reports and returns the driver's runtime values without forcing them through an entity mapping.

Enable NetTopologySuite

dotnet add package DuckDB.EFCoreProvider.NTS

options.UseDuckDB(
    "Data Source=spatial.duckdb",
    duckdb => duckdb.UseNetTopologySuite());

The add-on loads DuckDB's spatial extension. Geometry is stored in the native GEOMETRY type; WKT is only the current driver wire representation for reading and writing values.

See also: complete type mappings and precision notes.

DuckLake

The profile loads the DuckLake extension, runs secret initialisation, safely attaches the catalog, and selects it before EF uses provider-owned or caller-owned connections.

options.UseDuckLake(
    duckLake => duckLake
        .UseNamedSecret("application_lake")
        .CatalogName("analytics")
        .CreateIfNotExists(false),
    duckDB => duckDB
        .LoadExtension("httpfs")
        .ConfigureConnection(connection =>
        {
            using var command = connection.CreateCommand();
            command.CommandText = BuildCreateSecretFromEnvironment();
            command.ExecuteNonQuery();
        }));

The named secret must be a DuckDB TYPE ducklake secret. Create it on each session connection so credentials remain outside descriptors, options, and logs:

CREATE SECRET application_lake (
    TYPE ducklake,
    METADATA_PATH '',
    DATA_PATH 's3://bucket/lake/',
    METADATA_PARAMETERS MAP {
        'TYPE': 'postgres',
        'SECRET': 'application_postgres'
    });

DuckLake reads scale through independent sessions. Create a separate read-only DbContext or provider-owned read-only connection for each concurrent operation and attach the same catalog. The metadata backend must support those concurrent clients, and a DbContext must never be shared across threads. This is client scale-out, not a DuckDB replica topology.

Historical queries and maintenance

Use a separate catalog-wide profile when joins and navigations must observe one coherent historical snapshot. The profile is forced read-only and disables creation and automatic metadata migration.

var historicalOptions = new DbContextOptionsBuilder<AnalyticsContext>()
    .UseDuckLake(
        "metadata.ducklake",
        lake => lake.AsOfSnapshot(snapshotId))
    .Options;

await using var historical = new AnalyticsContext(historicalOptions);
var rows = await historical.Events
    .Where(row => row.RecordedAt < cutoff)
    .ToListAsync();

// For one deliberately pinned query root:
var tableHistory = await context.Events
    .AsOfTimestamp(asOf)
    .ToListAsync();

Database.DuckLake() provides catalog-scoped typed maintenance. Snapshot expiry, old-file cleanup, and orphan-file deletion default to discovery-only dry runs; pass dryRun: false only after reviewing the returned candidates.

var lake = context.Database.DuckLake();
var snapshots = await lake.GetSnapshotsAsync(cancellationToken);
var expiryCandidates = await lake.ExpireSnapshotsAsync(
    cutoff,
    cancellationToken: cancellationToken); // dry run

await lake.FlushInlinedDataAsync(
    new DuckLakeFlushOptions { SchemaName = "main", TableName = "events" },
    cancellationToken);
await lake.MergeAdjacentFilesAsync(
    new DuckLakeMergeOptions { TableName = "events" },
    cancellationToken);

Make a provider-initiated write snapshot self-documenting by setting caller-owned metadata inside the same explicit transaction as the writes:

await using var transaction = await context.Database.BeginTransactionAsync(cancellationToken);
await context.SaveChangesAsync(cancellationToken);
await context.Database.DuckLake().SetCommitMessageAsync(
    author,
    commitMessage,
    extraInfo,
    cancellationToken);
await transaction.CommitAsync(cancellationToken);

Additional catalogs and scaffolding

AlsoAttach adds a verified local catalog and AlsoAttachNamedSecret adds a catalog whose metadata and storage configuration live in a caller-created TYPE ducklake secret. Both are read-only by default. EF entities continue to target the selected primary catalog; use catalog-qualified dynamic or raw SQL for additional catalogs.

lake.CatalogName("analytics")
    .AlsoAttach("reference", "reference.ducklake")
    .AlsoAttachNamedSecret("shared", "shared_profile");

dotnet ef dbcontext scaffold \
  "ducklake:/absolute/path/metadata.ducklake" \
  DuckDB.EFCoreProvider

Local-metadata scaffolding filters by the exact catalog and honors normal --schema/--table filters. DuckLake exposes no physical keys, so generated entities are keyless until you review them and declare logical keys. Remote and named-secret metadata require a caller-initialized connection so credentials never enter command-line arguments.

See also: the production DuckLake guide.

Tiered storage

Tiered storage splits an aggregate across two tiers. Hot data stays in writable DuckDB tables with normal tracking, SaveChanges, and navigations. Cold data moves to immutable partitioned Parquet on local disk or object storage, organised by declared Hive partition transforms. ArchiveTierAsync performs the transition, which is verified and crash-safe. Generated union views keep ordinary LINQ queries spanning both tiers.

modelBuilder
    .ToTieredStore<Record>(
        record => record.EffectiveAt,
        "/var/data/archive/records",
        TierGranularity.Month)
    .MatchBy(record => record.ExternalKey)
    .PartitionBy(partitions => partitions
        .By(record => record.GroupId, "root_group_id")
        .ByMonth(record => record.EffectiveAt))
    .WithReadModel<RecordReport>()
    .Including<RecordPart>(
        record => record.Parts,
        part => part.WithReadModel<RecordPartReport>());

Read the views without duplicate CLR types. WithTieredView() requests the same provider-managed hot/Parquet union view without registering a second read-model type, and ToTieredView(…) maps your existing entity types in a separate read-only context. Partition pruning is contract-checked in both, so independently deployed readers fail explicitly on layout drift instead of silently filtering valid history. One physical child table can sit beneath multiple independently archived roots, and every partition level can carry an explicit Hive directory name.

Operations

Reviewable retention

var plan = await db.Database.PlanArchiveRetentionAsync<Record>(
    new TierArchiveRetentionOptions
    {
        RetainFrom = retainFromUtc,
        RetainedPartitionScopes =
        [
            TierMaintenanceScope.ForPartitionValues(
                new Dictionary<string, object?>
                {
                    [nameof(Record.GroupId)] = retainedGroupId,
                }),
        ],
    });

// Review the fingerprint, boundary, counts, files, and scopes.
var publication = await db.Database
    .PublishArchiveRetentionAsync<Record>(plan);

Planning fingerprints the active generation, exact physical catalog, contracts, boundary, partition scopes, and per-node counts. Publication revalidates that evidence, writes and verifies a replacement, then atomically switches the generated views. The provider does not decide retention policy, legal holds, approval, rollback depth, or deletion of obsolete local/remote objects.

Control-state recovery

// Capture after a successful publication and store outside the DuckDB file.
TierArchiveRecoveryCheckpoint checkpoint = await db.Database
    .CaptureArchiveRecoveryCheckpointAsync<Record>();

// After local control loss: plan first, then apply the reviewed plan.
TierArchiveRecoveryPlan recovery = await db.Database
    .PlanArchiveRecoveryAsync<Record>(persistedCheckpoint);
TierArchiveGenerationInventory inventory = await db.Database
    .ApplyArchiveRecoveryAsync<Record>(recovery);

The checkpoint carries binding, active generation, watermark, contracts, counts, and exact-file fingerprints but no archive path. Planning is read-only; apply revalidates and atomically rebuilds provider metadata and views. It never creates, modifies, adopts by guesswork, or deletes Parquet objects.

Evidence is explicit. Property filters on tiered views become Hive-bucket predicates; ordered, paged, and keyset queries are verified with DuckDB EXPLAIN file counts. Local and S3-compatible MinIO lanes are always on. Real AWS, GCS, and Azure matrices are credential-gated and opt-in; if a cloud lane is skipped, that environment is reported as unverified rather than counted as passing evidence.

See also: the complete tiered-storage contract and the compatibility and release-acceptance matrix.

Performance

The figures below are developer-laptop measurements from the repository guide, over 100,000 rows. Treat them as order-of-magnitude guidance and run BenchmarkDotNet on your own hardware.

Choosing a path by batch size

See also: benchmark method and full caveats.

Schema lifecycle

Native DuckDB

Create tables, columns, indexes, sequences, comments, and migration history through normal EF migrations.

dotnet ef migrations add InitialCreate
dotnet ef database update

DuckLake

Use EnsureCreated for initial provisioning, then apply reviewed DuckLake schema-evolution SQL through a controlled deployment process.

await db.Database.EnsureCreatedAsync();

DuckDB cannot perform every ALTER TABLE operation in place. The provider fails clearly for unsupported constraint changes by default. EnableMigrationTableRebuilds() opts into target-model table rebuilds where the provider can perform them safely.

See also: native migration behaviour and limitations.

Diagnostics and logging

Normal queries, tracked writes, connections, transactions, and migrations use EF Core's standard logging, diagnostic, and interception surfaces. Provider-owned raw operations publish one bounded start/completion/failure lifecycle through the same pipeline, without requiring a provider-specific logger.

using DuckDB.EFCoreProvider.Diagnostics;
using Microsoft.Extensions.Logging;

options.UseDuckDB("Data Source=analytics.duckdb")
    .LogTo(
        Console.WriteLine,
        new[]
        {
            DuckDBEventId.BulkInsertCompleted,
            DuckDBEventId.TieredStorageOperationFailed,
        },
        LogLevel.Information);

For structured consumption, use the LogTo overload that receives EF EventData or subscribe to DiagnosticListener, then cast provider events to DuckDBOperationEventData. The payload exposes the operation, non-secret target, duration, optional affected-row count, exception, and DbContext.

Raw fast paths stay outside DbCommandInterceptor. BulkInsert, Upsert, and provider maintenance use lower-level DuckDB operations. Their public observability contract is the bounded lifecycle event, not every internal staging or verification statement.

Compatibility

Reference

• README — complete setup, models, workflows, troubleshooting, and examples.
• Capability map — supported areas, engine limitations, provider gaps, and roadmap.
• DuckLake guide — secrets, catalog lifecycle, model rules, and production boundaries.
• Tiered storage — archive invariants, partitioning, reconciliation, restore, and operations.
• Type mappings — EF entity mappings, raw DuckDB.NET result values, JSON, nesting, and precision.
• Migrations — native DuckDB schema lifecycle, limitations, locking, and opt-in table rebuilds.
• Performance — benchmark commands, indicative results, caveats, and write-path guidance.
• Runnable sample — create a database, write tracked data, bulk insert, upsert, and query.
• DuckLake sample and tiered-storage sample — runnable storage-profile examples.

Install from NuGet or browse the source on GitHub. Community provider, MIT licensed, and not affiliated with DuckDB Labs or Microsoft.