Testing with EF Core: SQLite In-Memory and InMemory Provider Pitfalls

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: Does This Test Pass?

A book's ISBN must be unique. We said so in the model with a unique index:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>().HasIndex(b => b.Isbn).IsUnique();
}

Now let's write a fast test for this rule that needs no setup. For that we use EF Core's InMemory provider:

public class InMemoryTests
{
    private static BookstoreContext CreateContext(string name)
        => new(new DbContextOptionsBuilder<BookstoreContext>()
            .UseInMemoryDatabase(name)
            .Options);

    [Fact]
    public async Task InMemory_duplicate_isbn_is_rejected()
    {
        using var db = CreateContext(Guid.NewGuid().ToString());
        db.Books.Add(TestData.Book(isbn: "978-0441172719"));
        db.Books.Add(TestData.Book(title: "Dune (copy)", isbn: "978-0441172719"));

        await Assert.ThrowsAsync<DbUpdateException>(() => db.SaveChangesAsync());
    }
}

We add two books with the same ISBN and expect the save to be rejected. Does the test pass? The dotnet test output (EF Core 6; on EF Core 10 only the duration differs):

Failed Bookstore.Tests.InMemoryTests.InMemory_duplicate_isbn_is_rejected [3 ms]
  Error Message:
   Assert.Throws() Failure: No exception was thrown
Expected: typeof(Microsoft.EntityFrameworkCore.DbUpdateException)

No exception at all. Both books were saved happily. The production database would reject this save, yet the test environment accepts it. In other words, the test cannot measure the very rule we want to protect. In this post we talk about what we should test EF Core code against, realistic tests with SQLite in-memory, the false greens that come from asserting on the same context, and the debate about abstracting the DbContext.

What Is the InMemory Provider, Really?

The InMemory provider is not a relational database. It is a simple store that keeps entities in memory, written by the EF Core team for their own test infrastructure. It generates no SQL, knows no constraints and knows no transactions. The EF documentation explicitly calls using it as a database substitute in application tests "highly discouraged". I ran a few experiments with the same model; here are the results on EF Core 6 and 10:

The unique index isn't enforced, as we just saw. Neither is the foreign key: saving an OrderItem with a non-existent BookId = 999 makes SaveChangesAsync return 2, with no error. Trying to open a transaction throws an exception, because InMemory's "I am ignoring the transaction" warning is turned into an error by default (EF Core 10 output; on EF Core 6 the only difference is that the link starts with http://):

System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Database.Transaction.TransactionIgnoredWarning': Transactions are not supported by the in-memory store. See https://go.microsoft.com/fwlink/?LinkId=800142 This exception can be suppressed or logged by passing event ID 'InMemoryEventId.TransactionIgnoredWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

Raw SQL is a surprise that depends on the version. On EF Core 10, db.Books.FromSqlRaw("SELECT * FROM Books") throws Query root of type 'FromSqlQueryRootExpression' wasn't handled by provider code. On EF Core 6 nothing happens: the SQL is silently ignored and every book in the table comes back. The EF Core 7 breaking changes list says it plainly: in EF Core 6, using relational methods on InMemory was a silent no-op. I tried it: with two books in the store, FromSqlRaw("SELECT * FROM Books WHERE 1 = 0") returned two books on EF Core 6. So while you thought you were testing a raw SQL query with a WHERE, you were testing nothing at all.

One last difference is string comparison. InMemory compares case-sensitively, the way C# does; the query Title == "dune" doesn't find "Dune". SQL Server, by contrast, compares case-insensitively by default. The same query can come back empty in the test environment and full in production.

Think of InMemory as a toy flight simulator. The buttons are in place and the screen blinks, but there is no gravity. Good for practicing take-off; don't trust it for landing.

SQLite In-Memory: A Real Database, Zero Setup

A better option is to run a real relational database in memory. SQLite supports this, and it is the provider we have used in every example of the series anyway. Constraints, transactions, SQL generation and raw SQL are all real. The test project's packages:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.36" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="6.0.36" />
  <PackageReference Include="Microsoft.NET.Test.Sdk" Version="17.12.0" />
  <PackageReference Include="xunit" Version="2.9.3" />
  <PackageReference Include="xunit.runner.visualstudio" Version="2.8.2" />
</ItemGroup>

If you are on EF Core 10, just change the version of the two EF packages to 10.0.14; the InMemory package is here only for the comparison tests. A small helper class that sets up a clean database for each test:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

public sealed class SqliteDatabase : IDisposable
{
    private readonly SqliteConnection _connection;

    public SqliteDatabase()
    {
        _connection = new SqliteConnection("DataSource=:memory:");
        _connection.Open();

        using var db = CreateContext();
        db.Database.EnsureCreated();
    }

    public BookstoreContext CreateContext()
        => new(new DbContextOptionsBuilder<BookstoreContext>()
            .UseSqlite(_connection)
            .Options);

    public void Dispose() => _connection.Dispose();
}

The key point is the connection kept open. SQLite's in-memory database disappears the moment the last connection to it is closed. We open the connection once and give the same object to every context; the database lives until Dispose is called. We will see why this is essential, with its real error, in the pitfalls.

You can test the interceptor or the value converter from the earlier posts the same way: add AddInterceptors(...) in CreateContext, save, then read with a new context.

The Code Under Test: OrderService

The target of our tests is a small service that places orders:

public class OrderService
{
    private readonly BookstoreContext _db;

    public OrderService(BookstoreContext db) => _db = db;

    public async Task<Order> PlaceOrderAsync(string customerEmail, int bookId, int quantity)
    {
        var book = await _db.Books.SingleAsync(b => b.Id == bookId);
        if (book.Stock < quantity)
            throw new InvalidOperationException($"Only {book.Stock} copies of '{book.Title}' left.");

        book.Stock -= quantity;
        var order = new Order
        {
            CustomerEmail = customerEmail,
            Total = book.Price * quantity,
            Items = { new OrderItem { BookId = book.Id, Quantity = quantity, UnitPrice = book.Price } }
        };
        _db.Orders.Add(order);
        await _db.SaveChangesAsync();
        return order;
    }
}

In the test we use three separate contexts: one to arrange the data, one to do the work, and one to check the result. In production every request gets its own context too; we want the test to mimic that reality:

public class OrderServiceTests : IDisposable
{
    private readonly SqliteDatabase _database = new();

    public void Dispose() => _database.Dispose();

    [Fact]
    public async Task PlaceOrder_decreases_stock_and_calculates_total()
    {
        int bookId;
        using (var arrange = _database.CreateContext())
        {
            var book = TestData.Book(price: 12.5m, stock: 5);
            arrange.Books.Add(book);
            await arrange.SaveChangesAsync();
            bookId = book.Id;
        }

        using (var act = _database.CreateContext())
        {
            var order = await new OrderService(act).PlaceOrderAsync("ada@example.com", bookId, 2);
            Assert.Equal(25m, order.Total);
        }

        using var assert = _database.CreateContext();
        var stored = await assert.Books.SingleAsync(b => b.Id == bookId);
        Assert.Equal(3, stored.Stock);
        Assert.Equal(1, await assert.Orders.CountAsync());
    }

    [Fact]
    public async Task PlaceOrder_throws_when_stock_is_insufficient()
    {
        using var db = _database.CreateContext();
        var book = TestData.Book(stock: 1);
        db.Books.Add(book);
        await db.SaveChangesAsync();

        var ex = await Assert.ThrowsAsync<InvalidOperationException>(
            () => new OrderService(db).PlaceOrderAsync("ada@example.com", book.Id, 3));
        Assert.Equal("Only 1 copies of 'Dune' left.", ex.Message);
    }

    [Fact]
    public async Task Duplicate_isbn_is_rejected()
    {
        using var db = _database.CreateContext();
        db.Books.Add(TestData.Book(isbn: "978-0441172719"));
        db.Books.Add(TestData.Book(title: "Dune (copy)", isbn: "978-0441172719"));

        await Assert.ThrowsAsync<DbUpdateException>(() => db.SaveChangesAsync());
    }
}

xUnit creates a new instance of the class for every test, so each test gets its own SqliteDatabase, and therefore its own empty database. All three tests pass on both versions. Pay special attention to the last one: the ISBN test that was red on InMemory is green here, because SQLite actually enforces the unique index.

The False Green: Asserting on the Same Context

We wrote another method on the service that adds stock. We wrote it in a hurry and forgot something:

public async Task RestockAsync(int bookId, int amount)
{
    var book = await _db.Books.SingleAsync(b => b.Id == bookId);
    book.Stock += amount;
    // bug: the SaveChangesAsync call is missing
}

Let's write its test with a single context:

[Fact]
public async Task Restock_increases_stock()
{
    using var db = _database.CreateContext();
    var book = TestData.Book(stock: 1);
    db.Books.Add(book);
    await db.SaveChangesAsync();

    await new OrderService(db).RestockAsync(book.Id, 10);

    var reloaded = await db.Books.SingleAsync(b => b.Id == book.Id);
    Assert.Equal(11, reloaded.Stock);
}

The test passes. But in the database the stock is still 1. The reason is identity resolution, which we saw in the change tracking post: SingleAsync sends the SQL, but since the incoming row's key belongs to an entity that is already tracked, EF doesn't create a new object and returns the in-memory object that was modified but never saved. The test read the change tracker, not the database.

Add one line before the assert:

db.ChangeTracker.Clear();
var reloaded = await db.Books.SingleAsync(b => b.Id == book.Id);
Assert.Equal(11, reloaded.Stock);

And let the truth come out (EF Core 6; the same failure on EF Core 10):

Failed Bookstore.Tests.OrderServiceTests.Restock_increases_stock [35 ms]
  Error Message:
   Assert.Equal() Failure: Values differ
Expected: 11
Actual:   1

The rule: always read the result from the database. Either open a separate context or call ChangeTracker.Clear(). That is exactly why the arrange/act/assert contexts in the first test are separate.

Test Data: Small, Explicit Builders

We used TestData.Book(...) throughout the tests. Behind it is just a method with default values:

public static class TestData
{
    public static Book Book(string title = "Dune", decimal price = 10m, int stock = 5, string? isbn = null)
        => new() { Title = title, Price = price, Stock = stock, Isbn = isbn ?? Guid.NewGuid().ToString("N") };
}

This small class does two jobs. First, a test writes only the value that matters to it: stock: 1 in the stock test, price: 12.5m in the price test. Whoever reads it sees immediately which value is there and why. Second, fields that must be unique, like the ISBN, are generated uniquely by default; a test that adds two books but doesn't care about ISBNs won't trip over the unique index.

One big seed file shared by every test looks tempting, but over time it turns into something everyone touches and nobody dares to delete from. Keep a test's data inside that test. Reference data you provide through model seeding (HasData) already arrives in the tables with EnsureCreated; don't mix it up with test data.

Do You Need to Abstract the DbContext?

A common piece of advice: "Don't let the service depend on DbContext; write an IBookstoreContext interface and mock the DbSet in tests." The EF documentation is clear on this point: properly mocking a DbSet for queries isn't possible, because LINQ operators are static extension methods over IQueryable. Someone who says "I mocked the DbSet" has really dressed an in-memory list up as a DbSet and is running the queries as LINQ to Objects. An expression that can't be translated, a wrong Include or a missing SaveChanges won't show up in the test. The documentation's advice is short: avoid mocking DbSet for querying.

So what does it recommend? First, writing tests against your production database system. Set up fast and isolated, these tests are not as slow as you might think; for SQL Server, tools like Testcontainers that spin up a real database in Docker make it easier. If a test double that takes the database out entirely is a must, the documentation recommends putting a repository layer on top of EF Core and mocking that, rather than a fake provider like SQLite or InMemory; it adds that the cost is code and maintenance. We will go deeper into this debate from an architectural angle in the next post.

SQLite in-memory is a practical middle ground between those two ends, as long as you know its limits. It isn't your production database either. The example the documentation gives is exactly what we just saw with InMemory: SQL Server compares strings case-insensitively by default, SQLite case-sensitively. A SQL Server specific function like EF.Functions.DateDiffDay doesn't work on SQLite at all, and there is no rowversion. The balance I prefer: most of the fast tests that exercise business rules and EF Core behavior run on SQLite in-memory, and everything provider-specific plus the critical flows go through a small number of real database tests.

Common Pitfalls

The first pitfall is passing a connection string instead of a connection:

var options = new DbContextOptionsBuilder<BookstoreContext>()
    .UseSqlite("DataSource=:memory:")
    .Options;

using var db = new BookstoreContext(options);
db.Database.EnsureCreated();
db.Books.Add(TestData.Book());
await db.SaveChangesAsync();

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while saving the entity changes. See the inner exception for details.
---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: Books'.

EF opens and closes the connection for each operation. EnsureCreated created the table, the connection closed, and the in-memory database vanished. SaveChangesAsync connected through a new connection to a brand-new, empty database. Open the connection yourself and keep it open.

The second pitfall is sharing the database name with InMemory. UseInMemoryDatabase("Bookstore") gives every context opened with that name the same store; a book added by one test shows up in another, and tests pass or fail depending on the order they run in. If you must use InMemory, give each test a unique name such as Guid.NewGuid().ToString().

The third pitfall is forgetting that EnsureCreated doesn't test your migrations. EnsureCreated builds the schema straight from the model and never looks at migration files. Green tests don't prove your migrations are correct. Try the migrations separately by applying them to a real database with Migrate().

The fourth is using the same context for both act and assert; we saw its false green above. The fifth is not seeing the SQL in tests. When a test breaks unexpectedly, temporarily adding .LogTo(Console.WriteLine) to CreateContext solves half the problem.

A Small Experiment

★☆☆ Add the missing SaveChangesAsync call to RestockAsync and watch the test with ChangeTracker.Clear() turn green.

★★☆ With the InMemory provider, write a test that saves an OrderItem with a non-existent BookId = 999 and expects a DbUpdateException. Run the same test with SqliteDatabase. Compare the two results and say which test reflects production behavior.

★★★ Add the AuditInterceptor from the earlier post to SqliteDatabase.CreateContext and write two tests: one verifying that CreatedAt is filled on a new book, and one verifying that CreatedAt doesn't change on an updated book. To make time testable, add a constructor to the interceptor that takes a Func<DateTime>.

Now we can test EF Core code with confidence. What remains is the big picture: should we put a repository and unit of work on top of DbContext, how is EF Core used on the read side of CQRS, and when is it time to move to Dapper? In the last post of the core block we put EF Core on the table from an architectural angle.