EF Core Integration

[MaxwellDAssistek]

Is it possible to use DuckDB as an Entity Framework core backend yet?

We use EF core for all our data access and it would be great if it would be possible to use DuckDB!

What kind of sponsorship do you think might be needed for this?

[DBalashov]

Hi, MaxwellDAssistek!

Some time ago I started a provider for EF Core. However, it is too early to use it in production env.
https://github.com/DBalashov/EntityFrameworkCore.DuckDB

[benjaminjnr]

How should we assist?

[benjaminjnr]

Should we give this some attention?

[secirob]

yes please.

[benjaminjnr]

Do we do that here? Or in the DBalashov's repo? I think if we can have a structure around the project, some interested parties will lend their support.

[secirob]

It feels like DBalashov Repo would be wiser or another branch. I am under the impression regarding EFCore that existing Providers for either PostGreSQL or SqLite could be adapted quite easily in opposition of starting from scratch. But take this assumption with a grain of Salt, since i just discovered DuckDB yesterday for a project i was tasked with as an Architect and I am in that terms a complete outsider.

[Giorgi]

I think building a provider based on PostgreSQL makes more sense. From PostgreSQL Compatibility:

DuckDB's SQL dialect closely follows the conventions of the PostgreSQL dialect.

[MovGP0]

The following steps would be required:

Preliminary Research

EF Core Provider Documentation:

• EF Core Provider Design
• EF Core provider samples (e.g., the SQLite provider)
• EF Provider for PostgreSQL

Implement base classes and interfaces:

• Microsoft.EntityFrameworkCore.Storage
• Microsoft.EntityFrameworkCore.Infrastructure
• Microsoft.EntityFrameworkCore.Query
• RelationalDatabaseProvider
• RelationalConnection
• RelationalCommandBuilderFactory
• TypeMappingSource
• MigrationsSqlGenerator

Data Access Layer:

The ADO.NET provider must expose

• DbConnection
• DbCommand
• DbDataReader

Dialect and SQL Generation Layer:

Implement generators that produce valid DuckDB SQL from EF Expressions.

• QuerySqlGenerator
• MigrationsSqlGenerator

Migrations Support (Optional):

Implement classes that provide DuckDB DDL schema migrations:

• HistoryRepository (for storing migration history in DuckDB)
• MigrationsModelDiffer
• MigrationsSqlGenerator

Implement Core Provider Components

Implement DuckDbOptionsExtension and provider options:

Implement types to hold DuckDB-specific configurations (file paths, in-memory mode, etc.).

• DuckDBContextOptions class
• IDbContextOptionsExtension (e.g., DuckDbOptionsExtension)
• UseDuckDb() extension methods for DbContextOptionsBuilder

DuckDbDatabaseProvider Services:

• Register provider services via DuckDbDesignTimeServices or DuckDbDbContextOptionsBuilder.
• Implement DuckDbDatabaseProvider class that sets up service dependencies (SQL generator, type mappings, etc.).

Connection and Command Classes:

Implement wrappers the low-level DuckDB ADO.NET command execution.

• DuckDbConnection (inherits RelationalConnection)
• DuckDbCommandBuilder
• DuckDbCommandExecutor

Type Mapping:

Implement mapping from EF Core’s ClrType and RelationalTypeMapping data to DuckDB column types.

• DuckDbTypeMappingSource

SQL Generation:

• Implement DuckDbSqlGenerationHelper for quoting identifiers and delimiters.
• Implement DuckDbQuerySqlGenerator that translates LINQ expression trees into DuckDB-compatible SQL.

Migrations (optional):

Implement generator to translate EF model changes into DuckDB DDL (CREATE TABLE, ALTER TABLE, etc.).

• DuckDbMigrationsSqlGenerator

Implement migration history tracking in a metadata table

• DuckDbHistoryRepository