DuckDB for .NET: Columnar Analytics using EF Core

The embedded, analytics-first SQL engine you can drive from Entity Framework Core — with LINQ, migrations, direct Parquet querying, and a multi-tenant pattern that scales to a file per customer.

A practical guide to [DuckDB.EFCoreProvider](https://github.com/skuirrels/DuckDB.EFCoreProvider): what DuckDB is, why .NET developers should care, how the provider works under the hood, and the production pattern that made it click for us.

The one-paragraph pitch

You already know SQLite: a whole SQL database in a single file, no server, embedded right inside your process. DuckDB is SQLite’s analytical sibling. Same “just a file, no server” ergonomics — but where SQLite is row-oriented and tuned for transactional workloads (lots of small reads and writes), DuckDB is columnar and built for analytics: scanning millions of rows, grouping, aggregating, and joining at speeds that feel unfair for something running in-process. And as of EF Core 10, you can drive it from .NET with full LINQ, migrations, and type mapping through a dedicated provider.

This article covers what DuckDB is, why a .NET developer should care, and how the **DuckDB.EFCoreProvider** package works — including the parts that have no equivalent in a normal relational provider, like querying Parquet files as if they were tables.

Why DuckDB, and why from .NET?

Most .NET data access assumes a server database — SQL Server, PostgreSQL, MySQL — sitting across a network connection, optimised for many concurrent transactional clients (OLTP). That’s the right tool for an ordering system or a SaaS backend.

But a huge amount of real work isn’t that. It’s analytics: reporting, dashboards, ETL, ad-hoc “slice this dataset a hundred ways” exploration, embedded/edge data processing, and increasingly, data prep for ML. For that shape of work, a row-store server is often the wrong tool — you pay network round-trips and row-at-a-time overhead to do column-at-a-time work.

DuckDB fits that gap precisely:

• Embedded and in-process. No server to run, no ports, no ops. It’s a library. Your database is a file (or purely in-memory).
• Columnar + vectorised. Data is stored and processed column-by-column in batches, which is exactly what aggregation and scanning want.
• Speaks rich SQL. Window functions, PIVOT, list/struct/map types, read_parquet/read_csv, and more.
• Reads data files natively. Point it at a Parquet, CSV, or JSON file — local or globbed — and query it as a table without an import step.
• MIT-licensed and portable. Runs anywhere .NET runs.

The catch (and it’s an important one we’ll return to): DuckDB is single-writer and analytical. It is not a drop-in replacement for a high-concurrency, multi-writer OLTP server. Use it for what it’s good at.

The .NET stack: three layers

Getting DuckDB into idiomatic C# involves three layers, and it helps to know which does what:

1. DuckDB engine — the native columnar database itself.
2. **DuckDB.NET* — the ADO.NET layer: it exposes DuckDBConnection, DuckDBCommand, the appender API for fast bulk loads, and the raw plumbing. You can* use this directly, the same way you'd use raw SqlConnection.
3. [**DuckDB.EFCoreProvider](https://www.nuget.org/packages/DuckDB.EFCoreProvider)** — an Entity Framework Core 10 provider built on top of that ADO.NET layer. This is what lets you use DbContext, LINQ, change tracking, and migrations, instead of hand-writing SQL.

This article is mostly about layer 3, but everything ultimately runs through layers 2 and 1.

Getting started

Install the provider (targets .NET 10 / EF Core 10):

dotnet add package DuckDB.EFCoreProvider

Define a DbContext exactly as you would for any other provider — the only new thing is UseDuckDB(...):

using Microsoft.EntityFrameworkCore;
using DuckDB.EFCoreProvider.Extensions;

public class AnalyticsDbContext : DbContext
{
    public DbSet<Trip> Trips => Set<Trip>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseDuckDB("Data Source=analytics.duckdb");
}

public class Trip
{
    public long Id { get; set; }
    public DateTime PickupTime { get; set; }
    public string City { get; set; } = "";
    public decimal Fare { get; set; }
}

Connection strings mirror SQLite’s model:

• File-backed: "Data Source=analytics.duckdb" — a persistent database file.
• In-memory: "Data Source=:memory:" — lives only for the lifetime of the connection; perfect for tests and scratch analytics.

Wiring it into DI is the usual one-liner:

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

From here, SaveChanges, LINQ queries, and dotnet ef migrations all work. A store-generated key is read back via RETURNING after insert, just like you'd expect.

LINQ that becomes columnar SQL

Here’s where the engine choice pays off. A grouped aggregate in LINQ:

var revenueByDay = await context.Trips
    .GroupBy(t => t.PickupTime.Date)
    .Select(g => new
    {
        Day   = g.Key,
        Total = g.Sum(t => t.Fare),
        Trips = g.Count()
    })
    .OrderBy(x => x.Day)
    .ToListAsync();

The provider translates this into DuckDB SQL, and DuckDB executes it with its vectorised, column-at-a-time engine. On a wide table with millions of rows, that GroupBy/Sum is DuckDB's home turf — the kind of query that makes a row-store sweat runs comfortably in-process here.

You’re still writing ordinary EF Core LINQ. The provider is doing the work of mapping your expression tree onto DuckDB’s SQL dialect and type system.

The feature that has no OLTP equivalent: querying files as tables

This is the single most compelling reason to reach for DuckDB from .NET. DuckDB can read Parquet, CSV, and JSON files directly — and the provider surfaces that through attributes. Annotate an entity with [FromParquet] (or [FromCsv] / [FromJsonFile]) and it maps to a file-reading table function instead of a stored table:

using DuckDB.EFCoreProvider.Metadata;

[FromParquet("data/trips-*.parquet")]   // glob patterns work
public class TripRecord
{
    public DateTime PickupTime { get; set; }
    public string City { get; set; } = "";
    public decimal Fare { get; set; }
}

Now a LINQ query over DbSet<TripRecord> compiles down to something like:

SELECT ... FROM read_parquet('data/trips-*.parquet') WHERE ...

No import, no staging table, no ETL pipeline — you point EF Core at a folder of Parquet files and run LINQ over them. For reporting over a data lake, or joining a Parquet export against your own tables, this collapses a whole category of boilerplate. (These file-source entities are read-only query sources — you’re reading files, not writing rows back to them.)

If your file paths are relative, you can set a base directory once so they resolve predictably regardless of the process working directory:

o.UseDuckDB("Data Source=:memory:", duck => duck.FileSearchPath("/data"));

Writing fast: bulk insert, batching, and upsert

DuckDB’s columnar storage means row-at-a-time INSERTs are the slow path — the opposite of a row store. The provider gives you several faster options depending on whether you need change tracking.

1. Appender-backed bulk insert — the raw fast path (roughly a million rows/second territory), bypassing change tracking:

await context.BulkInsertAsync(trips);

2. Opt-in SaveChanges batching — keep change tracking and store-generated keys, but merge consecutive writes into single multi-row statements. Enable per-context:

o.UseDuckDB("Data Source=app.duckdb", duck => duck
    .EnableBulkInsertBatching()
    .EnableBulkUpdateBatching()
    .EnableBulkDeleteBatching());

These merge runs of inserts into INSERT ... VALUES (..),(..), eligible updates into UPDATE ... FROM (VALUES ..), and eligible deletes into a single DELETE, each roughly an order of magnitude faster than the per-row path — while preserving EF semantics (rows with concurrency tokens or computed columns fall back to the per-row path automatically).

3. Upsert — insert-or-update keyed on the primary key, sent as a single batched statement per chunk, backed by DuckDB’s INSERT ... ON CONFLICT DO UPDATE:

await context.UpsertAsync(rows);

The rule of thumb: **SaveChanges for correctness and change tracking; BulkInsert/Upsert for throughput.**

Rich types: arrays, JSON, decimals, temporal

DuckDB has a richer native type system than most relational stores, and the provider maps it to .NET:

• Lists/arrays round-trip to List<T> / T[] (DuckDB LIST/ARRAY).
• JSON and structural types map through DuckDB’s JSON support.
• Decimals carry precision/scale; temporal types (DateTime, DateOnly, TimeOnly, DateTimeOffset) map to DuckDB's date/time types.

That means a property like public string[] Tags { get; set; } is a first-class column, not a serialised blob — and you can query into it.

Migrations

The dotnet ef workflow works:

dotnet ef migrations add InitialCreate
dotnet ef database update

A couple of DuckDB-specific realities are worth knowing, because they stem from the engine rather than the provider:

• No idempotent scripts. Idempotent migration scripts rely on per-statement conditional guards, and DuckDB’s SQL simply doesn’t have that kind of procedural branching (SQLite is in the same boat). So --idempotent isn't an option — instead let EF apply migrations through dotnet ef database update / Database.Migrate(), which check the history table at runtime, or emit a plain (non-conditional) script.
• Table-based migration lock. Server providers coordinate concurrent migrators with session-scoped advisory locks; DuckDB has none, so the provider emulates the lock with a row in an __EFMigrationsLock table. It waits a bounded time (configurable) rather than hanging forever if a previous migrator crashed mid-run.

Spatial / GIS (optional)

If you need geometry, there’s a companion package, **DuckDB.EFCoreProvider.NTS**, that adds NetTopologySuite support (points, polygons, spatial predicates translated to DuckDB's spatial functions):

dotnet add package DuckDB.EFCoreProvider.NTS

o.UseDuckDB("Data Source=geo.duckdb", duck => duck.UseNetTopologySuite());

Tuning knobs

A few provider options map straight onto DuckDB engine settings, applied when a connection opens:

o.UseDuckDB("Data Source=app.duckdb", duck => duck
    .MemoryLimit("4GB")          // cap DuckDB's working memory (it grabs most of system RAM by default)
    .FileSearchPath("/data")     // base dir for relative file-source paths
    .MigrationLockTimeout(TimeSpan.FromMinutes(10)));

MemoryLimit earns its keep when DuckDB is sharing a box with other services. When a query wants more working memory than the cap allows, DuckDB doesn't fall over — it streams the overflow out to its temp directory. So a tighter limit buys you predictable memory usage at the cost of a bit of extra disk I/O on the heaviest queries.

How it actually works under the hood

If you’re the sort who wants to know what’s happening below UseDuckDB, here's the mental model.

EF Core is, at its core, a relational provider framework. A provider is a bag of services that EF Core’s pipeline calls into: a type-mapping source, a query SQL generator, a set of expression translators, a migrations SQL generator, an update SQL generator, a history repository, and so on. UseDuckDB(...) registers DuckDB's implementations of all of these.

When you run a LINQ query, EF Core:

1. Builds an expression tree from your LINQ.
2. Runs it through its query pipeline, where the provider’s translators convert method calls and members (.Date, .Sum(), string operations, array indexing, etc.) into DuckDB SQL expressions.
3. Emits a DuckDB-dialect SQL string via the provider’s query SQL generator.
4. Executes it through the ADO.NET layer (DuckDBConnection/DuckDBCommand).
5. Materialises the columnar result set back into your entity types via the type-mapping layer.

Writes go through the analogous update pipeline — which is where the batching and RETURNING-based key read-back live. Migrations go through the migrations SQL generator plus the table-based lock described earlier.

The reason the file-querying trick works so cleanly is that, to EF Core, a [FromParquet] entity's "table" is simply a different SQL fragment — read_parquet('...') instead of a quoted table name. The rest of the query pipeline doesn't need to care.

And the reason analytical queries are fast isn’t the provider at all — it’s DuckDB: columnar storage means a SUM(fare) reads only the fare column's compressed vector, and vectorised execution processes it in batches rather than row-by-row.

Use cases: where this shines

DuckDB isn’t a general-purpose “replace your database” play. It’s a sharp tool for a specific and surprisingly common shape of problem. The ones we keep coming back to:

• Reporting, dashboards, and BI. Aggregations, roll-ups, and window functions over wide tables — the classic “group by a hundred ways” workload — run in-process at columnar speed, with no warehouse to provision.
• Multi-tenant, per-customer analytics. Give each customer their own database file. More on this below — it’s the pattern that sold us.
• Querying a data lake without a warehouse. Point [FromParquet] at Parquet in local or mounted object storage and run LINQ over it. Great for querying exports, or joining a Parquet dataset against your own tables, without an ingestion step.
• ETL and data prep. Read messy CSV/JSON, transform with SQL/LINQ, write Parquet — all in one process, no Spark cluster.
• Embedded / edge / desktop analytics. A local-first app, CLI, or edge service that needs real analytical SQL over local data, with zero ops and a single-file store.
• Fast, realistic integration tests. An in-memory (Data Source=:memory:) OLAP database that behaves like the real thing, so analytical queries get genuine test coverage without a container.
• ML feature engineering. Slice, aggregate, and window over feature tables in-process before handing vectors to your model.

Case study: scaling per-customer analytics with one DuckDB file per customer

Here’s the pattern that made DuckDB click for us in production. We had a multi-tenant SaaS with a heavy per-customer analytics requirement — each customer needed fast, ad-hoc reporting over their own sizeable dataset.

The obvious approach — one big shared analytical warehouse with a CustomerId column on every table — creates a pile of problems at scale: noisy-neighbour contention (one customer's expensive query slows everyone's), row-level-security complexity, ballooning index sizes, and an always-on warehouse bill that grows with the whole dataset rather than with who's actually querying.

So we flipped it: each customer gets their own DuckDB file. One customer, one self-contained .duckdb file holding that tenant's OLAP data. With EF Core, that's just a connection string built per request:

public sealed class TenantAnalyticsDbContextFactory(ITenantContext tenant)
{
    public AnalyticsDbContext Create()
    {
        // One DuckDB file per customer — complete physical isolation.
        var path = Path.Combine("/data/tenants", $"{tenant.CustomerId}.duckdb");

        var options = new DbContextOptionsBuilder<AnalyticsDbContext>()
            .UseDuckDB($"Data Source={path}")
            .Options;

        return new AnalyticsDbContext(options);
    }
}

Why this scales so well:

• Total isolation. A tenant’s data lives in its own file — no cross-tenant contention, and no chance of a mis-written WHERE CustomerId = ... leaking one customer's data into another's report. Isolation is physical, not a query predicate you have to get right every time.
• Horizontal by default. Files are just files. Store them on disk or object storage, open one on demand, close it when done. Ten thousand customers is ten thousand files, not one ten-thousand-times-bigger database. Load scales with active tenants, not total data.
• Cost tracks usage. There’s no always-on per-tenant server. A dormant customer costs a few bytes of storage; you only spend compute when someone actually runs a query, in-process, for the duration of that query.
• Trivial lifecycle. Onboarding a customer is creating a file. Backup/restore/export is copying a file. Off-boarding (and GDPR “delete my data”) is deleting a file. Want to hand a customer a full extract? Give them the file — it’s a portable, self-describing database.
• Fast, every time. Each query runs against a right-sized, single-tenant dataset on DuckDB’s columnar engine — no giant shared indexes to fight, no contention to queue behind.

The trade-offs are real but manageable: DuckDB is single-writer per file, which fits this model perfectly (ingestion for a tenant is one writer; reads are what scale out). You own file placement and storage. And cross-tenant questions (“total revenue across all customers”) become a fan-out: query each file and roll the results up, rather than one big GROUP BY — usually a background job that writes an aggregate file. For per-customer analytics specifically, one-file-per-customer turned a scaling headache into a filesystem problem, which is a much nicer problem to have.

When not to reach for it

• You need a high-concurrency, multi-writer OLTP system-of-record. DuckDB is single-writer and analytical; that’s Postgres/SQL Server territory.
• You need many processes writing to the same database file simultaneously.

The honest framing: DuckDB is an analytical database with embedded ergonomics. Match it to analytical, embedded workloads and it’s a joy. Push it into high-concurrency transactional duty and you’ll fight the engine.

Wrapping up

DuckDB brings something genuinely new to the .NET data story: an embedded, columnar, analytics-first SQL engine you can talk to with the same DbContext and LINQ you already know — plus the ability to query data files directly as tables. For reporting, ETL, embedded analytics, and data exploration, it's a remarkably good fit, and the EF Core provider makes it feel native.

Get it / get involved:

• GitHub — DuckDB.EFCoreProvider: https://github.com/skuirrels/DuckDB.EFCoreProvider — stars, issues, and pull requests all welcome.
• NuGet: [DuckDB.EFCoreProvider](https://www.nuget.org/packages/DuckDB.EFCoreProvider) · [DuckDB.EFCoreProvider.NTS](https://www.nuget.org/packages/DuckDB.EFCoreProvider.NTS) (spatial)
• DuckDB itself: https://duckdb.org

Give it a spin: dotnet add package DuckDB.EFCoreProvider, point a DbContext at Data Source=:memory:, and run a GroupBy over a few million rows. The speed tends to sell itself. If it saves you a warehouse, a ⭐ on the repo is appreciated.