Entity Framework Core

The EF Core provider is the usual way into WitDatabase. Your model, your DbContext, your LINQ and your migrations are ordinary EF Core, and one line names the provider.

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseWitDb("Data Source=app.witdb");

Or with dependency injection:

services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(configuration.GetConnectionString("Default")));

Swap UseWitDb for UseNpgsql, UseSqlServer or UseMySql and everything else compiles unchanged. That property is the point of the provider, and the rest of this page is about the places where it is worth knowing more than that.

If EF Core itself is new to you, Microsoft's documentation is the place to start; nothing below assumes anything WitDatabase-specific about the model.

Migrations

The standard workflow applies. dotnet ef migrations add, dotnet ef database update, migrations script, migrations remove, and context.Database.Migrate() from code all behave as they do with any other provider, and the __EFMigrationsHistory table is created and maintained the same way.

Four operations are refused, with an exception naming the table, the change and the way around it:

• adding a primary key to an existing table
• dropping a primary key from an existing table
• renaming an index
• changing a column's type

EF Core's SQLite provider refuses the same four. All of them mean rebuilding the table: create the new one, copy the rows across, drop the old one, rename. Write that in the migration and it works.

The refusal matters more than the restriction. These used to emit a SQL comment, which is a valid script that changes nothing, so the migration was recorded as applied while the database kept its old schema and the model disagreed with it silently from then on.

EnsureSchema is ignored rather than refused, since there is one schema and nothing to create.

For anything short-lived, EnsureCreated builds the schema straight from the model and skips migrations entirely. It is the right call for a test fixture and the wrong one for a database that has to be upgraded in place later.

Bulk operations

AddRange followed by SaveChanges goes through change tracking, which costs more than it is worth when you are writing thousands of rows you do not intend to track. The provider ships bulk extensions for that case:

using OutWit.Database.EntityFramework.Extensions;

await context.BulkInsertAsync(users);
await context.BulkUpdateAsync(users);
await context.BulkDeleteAsync(users);

await context.BulkDeleteAsync<User>(u => u.Name.StartsWith("Test"));
await context.BulkInsertOrUpdateAsync(users);

BulkOptions covers the rest:

var options = new BulkOptions
{
    BatchSize = 1000,
    BatchProgress = count => logger.LogInformation("{Count} rows written", count),
    SetOutputIdentity = true,
    PropertiesToExclude = ["Notes"]
};

await context.BulkInsertAsync(users, options);

BulkDeleteAsync has two shapes. Given entities, it deletes by primary key one statement at a time. Given a predicate, it goes through EF Core's own ExecuteDelete, which is a single statement.

Two behaviours worth knowing, both of which took a defect to establish. Shadow properties are written like any other column, which matters because EF Core creates one for any relationship whose foreign key has no CLR property. And a store-generated column is written only when you actually supplied a value for it, so an explicitly assigned key reaches the table while an unset one is left to the generator.

The shape follows EFCore.BulkExtensions closely enough that moving between them is mostly renaming.

Testing

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseWitDbInMemory()
    .Options;

using var context = new AppDbContext(options);
context.Database.EnsureCreated();

UseWitDbInMemory() opens a connection and holds it, which is what makes the fixture work. An in-memory database is private to its connection and EF Core opens and closes one per operation, so UseWitDb("Data Source=:memory:") gives each operation a fresh empty database: EnsureCreated returns true and the next SaveChanges fails with Table not found. SQLite's provider documents the same recipe for the same reason.

Every call makes its own database, so two fixtures in one suite cannot see each other. The connection lives as long as the options object; pass your own WitDbConnection to the overload that takes one when the lifetime has to be yours.

Constraints are enforced, transactions roll back, and SQL is parsed and planned exactly as it would be against a file. EF Core's own in-memory provider does none of that, and will accept a save that a real database rejects.

Several DbContext instances in one process share a single engine per database and see each other's committed work, which is what a web application with a scoped context per request needs. A second process is turned away; see choosing an API.

Things the provider does its own way

Autoincrement is annotated as WitDb:Autoincrement in generated migrations and is tied to the row counter, so it works on a single-column key. A composite key containing a store-generated property is rejected when the model is built, naming the entity and the key, rather than failing on the first insert.

Index filters and descending columns reach the SQL, so HasFilter and IsDescending mean what they say. Note that the planner never selects a filtered index, so HasFilter builds something that costs writes and answers no query; see indexing.

StartsWith and EndsWith escape wildcards in the search term, so StartsWith("a_") matches what literally begins with a_ rather than everything beginning with a.

Isolation levels are passed through to the engine:

using var transaction = await context.Database
    .BeginTransactionAsync(IsolationLevel.Snapshot);

Where the edges are

Joins are the weak spot, and the cost grows with each table added to a query. A LINQ query with several Includes over a large model is the case to measure rather than assume. AsSplitQuery() is often the answer, since several small queries suit this engine better than one large join.

A correlated Take or Skip, and a filtered or limited collection Include, are refused at translation time. Both need CROSS APPLY or OUTER APPLY, which the provider will not emit. The engine has LATERAL and APPLY in SQL; the translation from LINQ to them does not exist yet. The message names the LINQ shape and suggests rewriting as a join or a subquery, or materialising the outer query with AsEnumerable().

Spatial types are not supported. Lazy loading needs proxy generation and is worth testing against your model before relying on it.

If you are moving an existing model here from another provider, compatibility lists what a migration will refuse and, more usefully, what it will apply cleanly and go wrong about later.

Where to go next

• Connection strings, every parameter
• ADO.NET provider, for the parts you would rather write in SQL
• Indexing, what the planner will and will not use
• Benchmarks, what the engine is quick at