Entity Framework Core Provider

CSharpDB.EntityFrameworkCore is an embedded-only Entity Framework Core 10 provider built on top of CSharpDB.Data. Use standard EF Core DbContext, migrations, change tracking, and LINQ patterns against local CSharpDB databases.

This guide documents the provider's supported behavior, current limits, and production guidance.

Install

dotnet add package CSharpDB.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Design is recommended in the application project so dotnet ef can run design-time commands cleanly.

Basic Usage

Configure your context with UseCSharpDb(...).

using CSharpDB.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

public sealed class BloggingContext : DbContext
{
    private readonly string? _connectionString;

    public BloggingContext(string databasePath)
        => _connectionString = $"Data Source={databasePath}";

    public BloggingContext(DbContextOptions<BloggingContext> options)
        : base(options)
    {
    }

    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured && _connectionString is not null)
            optionsBuilder.UseCSharpDb(_connectionString);
    }
}

public sealed class Blog
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Post> Posts { get; set; } = [];
}

public sealed class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public string Title { get; set; } = string.Empty;
    public Blog Blog { get; set; } = null!;
}

Then use EF Core as usual.

await using var db = new BloggingContext("blogging.db");
await db.Database.EnsureCreatedAsync();

db.Blogs.Add(new Blog
{
    Name = "Engineering",
    Posts = [new Post { Title = "Hello from CSharpDB EF Core" }]
});

await db.SaveChangesAsync();

var blogs = await db.Blogs
    .Include(blog => blog.Posts)
    .OrderBy(blog => blog.Name)
    .ToListAsync();

CLR Type Mappings

Convention-based properties use the following canonical SQL declarations:

Nullable CLR properties use the same declaration with nullable column metadata. Explicit HasColumnType(...) can select another compatible CSharpDB declaration; see the complete SQL data type reference for aliases, facets, and logical semantics.

Existing Connections and In-Memory Databases

You can pass an existing CSharpDbConnection. This is required for a private :memory: database because the database lives as long as the connection stays open.

using CSharpDB.Data;
using CSharpDB.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

await using var connection = new CSharpDbConnection("Data Source=:memory:");
await connection.OpenAsync();

var options = new DbContextOptionsBuilder<BloggingContext>()
    .UseCSharpDb(connection)
    .Options;

await using var db = new BloggingContext(options);
                await db.Database.EnsureCreatedAsync();

Provider-created file connections enable pooling unless the connection string explicitly sets Pooling=false. EF Core can continue its normal logical open/close pattern while CSharpDB retains one warm embedded engine. Logical close rolls back unfinished transactions and clears session-scoped temporary state; CSharpDbConnection.ClearPool and ClearAllPools perform the physical close and WAL cleanup.

Explicit Transactions

SaveChanges, commit, and rollback work inside explicit EF Core transactions. CSharpDB does not implement transaction savepoints, so the provider advertises SupportsSavepoints == false and EF Core skips its automatic pre-SaveChanges savepoint.

await using var transaction = await db.Database.BeginTransactionAsync();
db.Blogs.Add(new Blog { Name = "Transactional" });
await db.SaveChangesAsync();
await transaction.CommitAsync();

Supported ASP.NET Core Identity Configuration

Provider integration tests cover Identity schema v1 with integer user and role keys. Configure that exact model explicitly.

using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;

public sealed class AppUser : IdentityUser<int>;

public sealed class AppIdentityContext(
    DbContextOptions<AppIdentityContext> options)
    : IdentityDbContext<AppUser, IdentityRole<int>, int>(options)
{
    protected override Version SchemaVersion => new(1, 0);
}

builder.Services.AddDbContext<AppIdentityContext>(options =>
    options.UseCSharpDb(
        builder.Configuration.GetConnectionString("CSharpDB")!));

builder.Services
    .AddIdentity<AppUser, IdentityRole<int>>()
    .AddEntityFrameworkStores<AppIdentityContext>();

The tested workflows cover the seven schema-v1 tables, users, roles, memberships, claims, external logins, tokens, persistence across reopen, cascade cleanup, concurrency stamps, transaction rollback, and cancellation.

Embedded Storage Tuning

The EF Core provider can pass embedded engine tuning down into the CSharpDbConnection it creates. Use named presets and embedded open modes when you want discoverable, compile-checked settings.

using CSharpDB.Data;
using CSharpDB.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

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

Use full engine options when you need exact storage composition.

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

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

var options = new DbContextOptionsBuilder<BloggingContext>()
    .UseCSharpDb(
        "Data Source=blogging.db",
        csharpdb => csharpdb.UseDirectDatabaseOptions(directOptions))
    .Options;

Provider Builder Methods

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

Explicit DirectDatabaseOptions override Storage Preset. Explicit HybridDatabaseOptions override Embedded Open Mode. When EF Core is given an existing CSharpDbConnection, provider builder tuning is validated against that connection instead of mutating it.

For the full ADO.NET and EF Core tuning surface, see ADO.NET and EF storage tuning notes.

Migrations

For file-backed databases, the normal EF Core design-time workflow is supported.

dotnet ef migrations add InitialCreate
dotnet ef database update
dotnet ef migrations script
dotnet ef migrations script --idempotent

Database.Migrate() is supported for file-backed databases. EnsureCreated() is supported for file-backed and private in-memory databases. Migrations use the standard __EFMigrationsHistory table plus a simple __EFMigrationsLock row to serialize concurrent migration runs across processes. Idempotent scripts guard migration commands with history-table checks, so one script can be applied to empty, partially migrated, or current databases.

Provider CI freezes and independently replays a representative three-version Up/Down SQL corpus. The lifecycle coverage includes empty and populated databases, downgrade/re-upgrade, failed-migration rollback and recovery, database reopen, runtime CRUD, and ADO.NET inspection of rewritten columns, keys, indexes, checks, and named relationships. The corpus is plain SQL and can be executed without EF.

EF migration execution remains intentionally embedded/direct. The ordinary SQL ADO.NET metadata contract is separately compared across direct, HTTP, and gRPC connections; this does not imply that Database.Migrate() accepts a remote endpoint.

Exact Decimal Foundation

decimal and nullable decimal properties no longer require an application value converter. The provider declares DECIMAL(precision,scale) and sends CLR decimal values through the exact DbValue.Decimal representation. Round trips, parameters, arithmetic, equality/range comparisons, ordering, aggregates, defaults, keys, and ordinary indexes therefore do not pass through binary floating point or a scaled surrogate integer.

modelBuilder.Entity<Invoice>()
    .Property(invoice => invoice.Amount)
    .HasPrecision(18, 4);

The default is decimal(18, 2). Precision must be from 1 through 18, and scale must be from 0 through precision. Values with excess fractional digits are rejected instead of rounded, and values outside the configured precision fail as overflow. Raw SQL sees the same exact decimal value. Precision/scale changes use the engine's validated transactional table rewrite and fail without publishing partial data if an existing value cannot fit the target declaration.

Database-Generated RowVersion

CSharpDB supports one nonnullable byte[] property per table configured with the standard [Timestamp] attribute or fluent IsRowVersion() API.

using System.ComponentModel.DataAnnotations;

public sealed class Document
{
    public int Id { get; set; }
    public string Contents { get; set; } = string.Empty;

    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;
}

The provider creates the column as ROWVERSION; the legacy BLOB ROWVERSION NOT NULL spelling remains accepted. The engine allocates an opaque eight-byte token from a persisted database-wide counter and returns it to EF after inserts and updates. Raw SQL, trigger-issued updates, and updates that leave all other values unchanged advance the token too. EF includes the original token in update and delete predicates, so stale tracked writes throw DbUpdateConcurrencyException.

LINQ Translation

The provider supports a deliberately bounded server-side LINQ surface. Basic support includes Where, ordering, Skip/Take, scalar projections, Single, Any, Count, non-decimal constant/parameter collection Contains, and simple Include queries.

String members and methods

• string.Length
• Parameterless ToLower(), ToLowerInvariant(), ToUpper(), and ToUpperInvariant()
• Parameterless Trim(), TrimStart(), and TrimEnd()
• Replace(string, string)
• Substring(start) and Substring(start, length); the provider converts .NET's zero-based start index to CSharpDB's one-based SQL index
• Contains(string) with ordinal semantics
• StartsWith(string, StringComparison.Ordinal), EndsWith(string, StringComparison.Ordinal), and Contains(string, StringComparison.Ordinal) when the comparison argument is a literal
• EF.Functions.Like(match, pattern) and EF.Functions.Like(match, pattern, escape) over one directly mapped, converter-free TEXT property

Both culture-sensitive and invariant CLR casing methods map to CSharpDB LOWER/UPPER. They therefore use invariant server semantics, not the application's CurrentCulture.

Ordinal string predicates require provider-owned, converter-free TEXT mappings. Search text may be a constant or captured parameter, including an empty string, and is treated literally: %, _, and backslash are not wildcard or escape syntax. The dedicated translations are case-sensitive and propagate SQL NULL.

EF.Functions.Like intentionally uses SQL pattern syntax: % matches zero or more UTF-16 code units and _ matches one UTF-16 code unit. The match must be one direct converter-free TEXT property, while the pattern may be a constant or captured string, including null. CSharpDB LIKE is invariant case-insensitive. The three-string overload requires a compile-time, non-null, one-UTF-16-code-unit escape literal other than %. A positive nullable LIKE predicate excludes SQL NULL; EF Core's normal null compensation makes a negated nullable predicate include NULL rows. SQLite parity covers bounded ASCII patterns; Unicode casing and supplementary-character wildcard behavior are provider-specific.

Date and time components

• DateTime.Year, Month, Day, Hour, Minute, and Second
• DateOnly.Year, Month, and Day
• TimeOnly.Hour, Minute, and Second

Double-precision math

For finite REAL-mapped values, the provider translates Math.Abs(double), Math.Round(double), Math.Floor(double), Math.Ceiling(double), Math.Truncate(double), and Math.Sign(double) in predicates, projections, and ordering. Translated functions propagate SQL NULL, and Math.Round(double) uses midpoint-to-even semantics.

Scalar numeric aggregates

The supported aggregate slice covers Count, LongCount, simple and bounded-shape Any, Sum over int, double, and nullable double, Average over double and nullable double, and Min/Max over int, double, and nullable double. Filtered, empty, and all-NULL cases are cross-checked against SQLite.

Bounded direct inner and left joins

One explicit Queryable.Join or no-comparer Queryable.LeftJoin is supported between sources that normalize to direct mapped entity roots. The outer root may have an optional Where; the inner root must remain unfiltered because EF Core otherwise emits a derived-table join target that CSharpDB's current table-reference grammar does not accept. Each side must use one direct nonnullable int, long, or int/long-backed enum property backed by INTEGER with compatible provider mappings. Supported scalar or entity result projections and post-join filtering, ordering, and Skip/Take include self-joins.

LeftJoin preserves an outer row when no inner row matches. The unmatched inner entity and reference-type members materialize as null; project unmatched inner value-type members to nullable CLR types, for example PostId = (int?)post!.Id. This explicit nullable projection prevents SQL NULL from being interpreted as a value type's CLR default.

Terminal integer set operations

Exactly one terminal no-comparer Queryable.Concat, Queryable.Union, Queryable.Intersect, or Queryable.Except is supported when both branches remain direct mapped entity tables with optional filtering and each projects one compatible, converter-free INTEGER-backed int, long, or nullable equivalent. Concat preserves duplicates. The other three operators use distinct set semantics, including one SQL NULL set value where appropriate. Result order is unspecified; materialize before applying client-side ordering or transformations.

Distinct numeric aggregates

The supported scalar shape is an optional Where, followed by selection of one directly mapped nonnullable int column, Distinct, and Count, LongCount, Sum, Min, or Max.

Grouped numeric aggregates

Direct single-table GroupBy supports an optional pre-filter and direct mapped Boolean, integral, enum, default-BINARY string, or nullable keys. Composite keys must use C# anonymous types or ValueTuple. Boolean key columns must contain canonical provider-written 0/1 storage. One grouped projection can contain direct keys plus bare Count/LongCount, Sum over int/double/nullable double, Average over double/nullable double, Min/Max over int/double/nullable double, and direct nonnullable-int Distinct variants for every listed aggregate except Average. Basic HAVING predicates, including aggregate IS NULL, and ordering by a directly projected key or aggregate are supported.

double, transformed, non-BINARY-collated, or configured-converter keys; aggregate value converters; element/result selector overloads; group materialization; raw group transforms; post-projection filtering, projection, distinct, limits, set, or join operations; nested grouping; predicate/CASE aggregates; casts; and broader types or shapes are rejected with CDBEF1005 before command dispatch.

DateTimeOffset components; integral, decimal, MathF, precision-argument, midpoint-mode, and transcendental math overloads; long- and float-valued Sum/Average/Min/Max variants and other unsupported aggregate variants; broader distinct and grouped aggregate shapes; broader set-operation projections, mappings, nesting, chaining, and post-set composition; composite/chained/right/cross or derived-source joins; and correlated-query shapes remain outside the supported surface.

Unsupported-expression diagnostics

Unsupported expressions retain EF Core's InvalidOperationException and add stable provider guidance before a command is dispatched. Diagnostics identify the construct without adding parameter values.

When client evaluation is intentional, apply selective supported filters first, then call AsEnumerable() explicitly before the unsupported portion. This makes the server/client boundary visible and avoids accidentally loading an entire table.

Supported Surface

Current Limitations

• DECIMAL columns are not identity or rowversion storage; application-converter and explicit legacy scaled-INTEGER decimal mappings retain a narrower query surface than native DECIMAL.
• Complex properties are rejected until their flattened column mappings are supported.
• ExecuteUpdate is rejected until assignment conversions and decimal facets are supported.
• Direct Join and no-comparer LeftJoin are limited to one nonnullable int, long, or int/long-backed enum key; filtered inner sources, derived/composite/chained joins, comparer overloads, classic GroupJoin/SelectMany/DefaultIfEmpty left joins, RightJoin, and cross joins remain unsupported.
• Set operations are limited to one terminal Concat, Union, Intersect, or Except over compatible direct converter-free INTEGER int/long column projections; branch ordering or limits, broader projections and mappings, comparer overloads, nested/chained operations, and server composition after the operation remain unsupported.
• Optional relationships support EF's client-side ClientSetNull. Database-side DeleteBehavior.SetNull/ON DELETE SET NULL requires every dependent FK property to be nullable. EF relationship-model conventions cannot express SET DEFAULT or ON UPDATE; use an explicit migration operation or reviewed raw SQL for those engine-supported actions.
• Schemas are unsupported in runtime and migrations.
• Computed columns and DefaultValueSql are unsupported.
• Rowversion is limited to one nonnullable byte[] property created with its table; standalone add/alter rowversion migrations are unsupported.
• All other string-search overloads—including default StartsWith(string)/EndsWith(string), the Boolean/CultureInfo forms, non-ordinal or captured StringComparison modes, and character overloads—plus transformed/configured-converter LIKE matches, row-derived LIKE patterns, captured or invalid LIKE escapes, and DateTimeOffset component translation are unsupported.
• Integral, MathF, precision-argument, midpoint-mode, and transcendental math overloads are outside the supported translation surface.
• Long- and float-valued Sum/Average/Min/Max variants, integer Average, text Min/Max, and broader distinct/grouped aggregate types and shapes remain outside the supported surface.
• Physical INTEGER primary-key rekeying supports ready ordinary/unique SQL, constraint-owned, foreign-key-support, and complete ready full-text index families; collection, incomplete full-text, and non-ready indexes are rejected.
• Named shared-memory databases (:memory:<name>) are rejected.
• Endpoint, daemon, and non-direct transports are rejected.
• Transaction savepoints are unsupported. The provider reports that capability accurately so ordinary explicit-transaction SaveChanges calls do not issue savepoint SQL.
• ASP.NET Core Identity is supported only for schema v1 with integer user and role keys; default string keys, schema versions 2 and 3, passkeys, and unlisted store APIs remain unsupported.
• Indexed TEXT/BLOB changes, lossy or other type conversions, ordered/range REAL index access, and rewrites involving key/foreign-key/full-text/collection or non-ready dependencies require broader support.
• Broad table-rebuild migration emulation is not implemented; unsupported operations fail explicitly.

DDL Surface

The migrations SQL generator currently supports CreateTable (including one ROWVERSION column, literal defaults, deterministic row-local checks, composite primary keys, alternate keys, named scalar/composite foreign keys, and column collations), DropTable, RenameTable, AddColumn, RenameColumn, DropColumn, composite CreateIndex, DropIndex, RenameIndex, standalone add/drop named check, unique, foreign-key, and bounded primary-key constraints (including populated single-INTEGER rekeying with supported relational-index and complete ready full-text-owned-store rebuilding), and AlterColumn changes to literal defaults, nullability, exact numeric types with ready SQL-index rebuilding, strict dependency-free UTF-8 TEXT/BLOB types, and text collations with inherited ordinary/unique SQL-index rebuilding. The generator emits supported conversions in both Up and Down migrations. Standalone rowversion add/alter operations, broader primary-key rekeys, indexed TEXT/BLOB changes, key/FK/full-text/collection/non-ready-dependent column rewrites, and other type conversions remain explicit rejections.