Testing with EF Core in C#: In-Memory vs SQLite for Unit Tests

Database code is some of the hardest code to test well. Ef core unit testing gives you two fast, dependency-free options -- the built-in in-memory provider and SQLite in-memory mode -- but picking the wrong one for the wrong scenario will give you tests that lie to you. This guide walks through both providers, when to use each, how to structure your xUnit test classes, and what pitfalls to avoid.

Why Testing Database Code Is Hard

Real databases make tests slow. They make them brittle. And they couple your test suite to infrastructure that may not even be available in CI.

Think about it: every test that hits SQL Server has to spin up a connection, execute DDL, insert rows, run queries, and clean up. That's fine for a handful of integration tests. It is not fine when you have hundreds of repository tests that should finish in under a second. Add in the fun of parallel test runners clobbering each other's data, and you have a recipe for flaky builds that erode team confidence over time.

The EF Core team knew this was a problem. The in-memory provider was their first answer. SQLite in-memory mode is the more production-faithful answer. Understanding the trade-offs between them is the key to a maintainable test strategy.

Option 1 -- UseInMemoryDatabase

The Microsoft.EntityFrameworkCore.InMemory package ships a provider that stores your entities in memory using plain .NET dictionaries. No SQL. No file I/O. Just objects.

Setting It Up

// Package: Microsoft.EntityFrameworkCore.InMemory (v10.x)
// Package: xunit (v2.x)

public class BlogDbContext(DbContextOptions<BlogDbContext> options) : DbContext(options)
{
    public DbSet<Post> Posts => Set<Post>();
    public DbSet<Tag> Tags => Set<Tag>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasMany(p => p.Tags)
            .WithMany();
    }
}

public record Post
{
    public int Id { get; init; }
    public required string Title { get; init; }
    public required string Slug { get; init; }
    public bool IsPublished { get; init; }
    public DateTimeOffset PublishedAt { get; init; }
    public List<Tag> Tags { get; init; } = [];
}

public record Tag
{
    public int Id { get; init; }
    public required string Name { get; init; }
}

// In your test:
private static BlogDbContext CreateInMemoryContext(string dbName = "TestDb")
{
    var options = new DbContextOptionsBuilder<BlogDbContext>()
        .UseInMemoryDatabase(dbName)
        .Options;

    return new BlogDbContext(options);
}

Pass a unique dbName per test and you get a fresh isolated store. Tests don't bleed into each other.

Pros of the In-Memory Provider

• Blazing fast. No SQL parsing, no disk I/O, no connection pooling.
• Zero setup. No SQLite native library, no connection string, no file path.
• Great for logic tests. If you're testing a service layer that uses EF Core to query and filter data, the in-memory provider works perfectly fine.

Cons -- and They Are Real Cons

This is where developers get into trouble. The in-memory provider is not a relational database. It does not enforce:

• Foreign key constraints. You can insert a Post referencing a non-existent Tag.Id and EF Core will happily save it.
• Unique constraints. Duplicate values on a column marked [Index(IsUnique = true)]? No error.
• Transactions. BeginTransaction() compiles but is a no-op.
• Raw SQL. FromSqlRaw("SELECT ...") throws at runtime.
• Database-generated values with real sequences. Auto-increment works, but the behavior can differ from SQL Server identity columns.

The in-memory provider is ideal for testing business logic that lives above the data-access boundary. It is a poor choice for testing repository or data-access code where constraint behavior and SQL correctness matter.

Option 2 -- SQLite In-Memory

SQLite is a real SQL engine. It parses SQL, enforces constraints (when you enable them), and runs transactions. Running it fully in memory -- no file on disk -- keeps tests fast while giving you something much closer to production behavior.

Setup: Keep the Connection Open

The most common mistake with SQLite in-memory mode is letting the connection close. When the last connection to an in-memory SQLite database closes, the database is destroyed. EF Core opens and closes connections internally, so you must create the connection yourself and keep it alive for the lifetime of your tests.

// Package: Microsoft.EntityFrameworkCore.Sqlite (v10.x)
// Package: Microsoft.Data.Sqlite (v10.x)

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

    public SqliteInMemoryFixture()
    {
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();

        var options = CreateOptions();
        using var context = new BlogDbContext(options);
        context.Database.EnsureCreated();
    }

    public DbContextOptions<BlogDbContext> CreateOptions() =>
        new DbContextOptionsBuilder<BlogDbContext>()
            .UseSqlite(_connection)
            .Options;

    public BlogDbContext CreateContext() =>
        new BlogDbContext(CreateOptions());

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

A few things to notice here:

1. _connection.Open() is called once and the connection is kept alive.
2. EnsureCreated() builds the schema from your model. Do not call Migrate() in tests -- that requires migration history tables and real migration files.
3. Each call to CreateContext() returns a new DbContext instance over the same open connection, so the schema and seeded data persist across contexts during the test run.

Pros of SQLite In-Memory

• Real SQL. FromSqlRaw works (for SQLite-compatible SQL).
• FK constraints enforced when you run PRAGMA foreign_keys = ON (EF Core's SQLite provider does this automatically when opening a connection (since EF Core 3.x)).
• Transactions work exactly as you'd expect.
• Much closer to production than the in-memory provider for repository-layer tests.

Cons

• SQLite syntax differs from SQL Server. If your production queries use JSON_VALUE, OPENJSON, STRING_AGG with SQL Server syntax, or SQL Server-specific functions, those queries will fail against SQLite. Your tests may pass while production-specific SQL still has bugs.
• SQLite type affinity is looser than SQL Server's strict type system. Edge cases around decimal precision, date storage, and computed columns can behave differently.
• Native dependency. The Microsoft.Data.Sqlite package ships native SQLite binaries for each platform. This is rarely a problem in practice, but worth knowing.

The IDbContextFactory Pattern for Test Isolation

When using dependency injection in production code, your repositories and services often depend on IDbContextFactory<T> rather than a raw DbContext. This is the right pattern for Blazor Server and background services where a single long-lived DbContext causes concurrency issues.

In tests, you want to control exactly what IDbContextFactory<T> returns. Here's a lightweight implementation:

public sealed class TestDbContextFactory<TContext>(
    Func<TContext> factory) : IDbContextFactory<TContext>
    where TContext : DbContext
{
    public TContext CreateDbContext() => factory();
}

Combine it with the SQLite fixture:

public sealed class SqliteTestBase : IDisposable
{
    private readonly SqliteConnection _connection;
    protected readonly IDbContextFactory<BlogDbContext> DbContextFactory;

    protected SqliteTestBase()
    {
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();

        var options = new DbContextOptionsBuilder<BlogDbContext>()
            .UseSqlite(_connection)
            .Options;

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

        DbContextFactory = new TestDbContextFactory<BlogDbContext>(
            () => new BlogDbContext(options));
    }

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

Any test class that inherits SqliteTestBase gets a ready-to-use factory. Clean, reusable, and consistent across your entire test suite.

A Complete xUnit Test Class

Here's a realistic repository test using the base class pattern above. This is the style of test that catches real bugs -- constraint violations, query logic, LINQ filtering -- rather than just verifying that EF Core works.

public sealed class PostRepositoryTests : SqliteTestBase
{
    private readonly PostRepository _sut;

    public PostRepositoryTests()
    {
        _sut = new PostRepository(DbContextFactory);
    }

    [Fact]
    public async Task GetPublishedPostsAsync_WhenPostsExist_ReturnsOnlyPublished()
    {
        // Arrange
        await using var context = DbContextFactory.CreateDbContext();
        context.Posts.AddRange(
            new Post { Id = 1, Title = "Draft Post", Slug = "draft-post", IsPublished = false, PublishedAt = DateTimeOffset.UtcNow },
            new Post { Id = 2, Title = "Live Post", Slug = "live-post", IsPublished = true, PublishedAt = DateTimeOffset.UtcNow.AddDays(-1) },
            new Post { Id = 3, Title = "Another Live Post", Slug = "another-live-post", IsPublished = true, PublishedAt = DateTimeOffset.UtcNow.AddDays(-7) }
        );
        await context.SaveChangesAsync();

        // Act
        var results = await _sut.GetPublishedPostsAsync();

        // Assert
        Assert.Equal(2, results.Count);
        Assert.All(results, p => Assert.True(p.IsPublished));
    }

    [Fact]
    public async Task AddPostAsync_WithDuplicateSlug_ThrowsUniqueConstraintException()
    {
        // Arrange
        await using var context = DbContextFactory.CreateDbContext();
        context.Posts.Add(new Post { Id = 10, Title = "Existing", Slug = "my-slug", IsPublished = false, PublishedAt = DateTimeOffset.UtcNow });
        await context.SaveChangesAsync();

        // Act & Assert -- SQLite enforces the unique index unlike the in-memory provider
        await Assert.ThrowsAnyAsync<DbUpdateException>(
            () => _sut.AddPostAsync(new Post { Id = 11, Title = "Duplicate", Slug = "my-slug", IsPublished = false, PublishedAt = DateTimeOffset.UtcNow }));
    }
}

Notice the second test -- AddPostAsync_WithDuplicateSlug_ThrowsUniqueConstraintException. This test passes with SQLite and would silently pass without throwing if you used the in-memory provider. That's the difference in one concrete example.

For more on how LINQ filtering works in the underlying queries, or how to compose complex LINQ expressions against EF Core DbSets, those fundamentals apply directly in your test arrange and act steps.

Seeding Test Data Correctly

The arrange step in a data-access test is seeding. Do it via a fresh context -- separate from the one your system under test will use -- to avoid change-tracker contamination.

// Arrange: seed via one context
await using (var seedContext = DbContextFactory.CreateDbContext())
{
    seedContext.Tags.AddRange(
        new Tag { Id = 1, Name = "csharp" },
        new Tag { Id = 2, Name = "dotnet" }
    );
    await seedContext.SaveChangesAsync();
}

// Act: exercise via a second context (no shared change tracker)
await using var actContext = DbContextFactory.CreateDbContext();
var tags = await actContext.Tags
    .Where(t => t.Name.StartsWith("dot"))
    .ToListAsync();

Using a separate context per operation is the same discipline that the repository pattern enforces in production code. Keeping it consistent in tests prevents the change tracker from returning stale tracked entities and masking real query bugs.

Note: Using explicit IDs in seed data works but requires care: if a later operation uses auto-increment and the DB sequence hasn't advanced past your explicit IDs, you'll get duplicate key violations. Either always use explicit IDs in tests or always rely on auto-increment -- don't mix both approaches.

xUnit Test Structure: Class Fixture vs IDisposable

You have two main options for sharing setup between xUnit tests.

IDisposable on the test class -- simple, each test class manages its own setup and teardown. Use this when tests in a class need full isolation (separate SQLite databases per class).

IClassFixture<T> -- the fixture is created once and shared across all tests in the class. Use this when schema creation is expensive and tests don't mutate shared state in conflicting ways. For SQLite in-memory, the fixture owns the open connection, so the schema persists across tests in the fixture's lifetime.

For most repository test suites, the class fixture approach with per-operation context creation gives the right balance. The schema is created once; each test seeds its own data and queries against isolated rows.

Logging in Tests

When a test fails and you can't tell why, EF Core's query logging is your best friend. Hook it up through the options builder:

var options = new DbContextOptionsBuilder<BlogDbContext>()
    .UseSqlite(_connection)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging()
    .Options;

LogTo writes generated SQL to Console.WriteLine, which xUnit captures and includes in test output. You'll see exactly what SQL EF Core generated and where it went wrong. For a deeper dive into structured logging patterns in .NET, the complete .NET logging guide covers the full ILogger pipeline.

Integration Tests: When to Hit a Real Database

In-memory and SQLite tests cover a lot of ground. They are not a substitute for integration tests against a real database engine.

Run integration tests against a real SQL Server or PostgreSQL instance in CI when you need to verify:

• Migration correctness. EnsureCreated() and real Migrate() produce different schemas. Test migrations against a real engine.
• SQL Server-specific queries. FromSqlRaw with T-SQL, JSON_VALUE, window functions, full-text search.
• Performance characteristics. Query plans, index usage, and execution time only matter against a real engine with real data volumes.
• Concurrency and isolation levels. Serializable vs read committed behavior is an engine-level concern.

The practical split for most .NET projects:

Common Pitfalls

Shared DbContext Between Tests

Never reuse the same DbContext instance across tests. The change tracker holds references to every entity it has seen. Your second test will start with stale tracked entities, broken navigation properties, and query results that reflect in-memory cache rather than the actual database state.

Always call CreateDbContext() at the start of each logical operation.

Forgetting EnsureDeleted

If you use a named in-memory database (not SQLite) and run tests in the same process, the in-memory database persists between test runs in the same process. Add EnsureDeleted() in test teardown or switch to unique names per test:

var dbName = $"TestDb_{Guid.NewGuid()}";
var options = new DbContextOptionsBuilder<BlogDbContext>()
    .UseInMemoryDatabase(dbName)
    .Options;

Unique names per test prevent bleed between tests without needing teardown logic. The garbage collector handles cleanup.

Using EF Core Migrations in Tests

context.Database.Migrate() runs migration history checks against a __EFMigrationsHistory table. That table doesn't exist in a fresh in-memory or SQLite database unless you've created it. Use EnsureCreated() in tests. It creates the full schema from the current model snapshot without migration tracking overhead.

Async Tests Without Await

EF Core's async methods (SaveChangesAsync, ToListAsync, FirstOrDefaultAsync) return Task or ValueTask. Forgetting await in xUnit async tests produces tests that always pass because they don't actually execute. Mark your test methods async Task and await everything.

Decision Guide

Here's the practical decision tree:

• Testing business logic or service classes that happen to use EF Core? Use UseInMemoryDatabase. It's fast, simple, and the lack of SQL constraints doesn't matter when you're testing logic above the data layer.
• Testing repository methods, query correctness, or constraint behavior? Use SQLite in-memory. You get a real SQL engine with near-zero setup overhead.
• Testing migrations, SQL Server-specific queries, or production data volumes? Hit a real database in CI. There's no substitute.

For the abstraction layer design, the facade pattern is worth considering when you want to hide EF Core behind a clean interface that's easy to replace in tests.

FAQ

What is the difference between UseInMemoryDatabase and SQLite in-memory for EF Core unit testing?

UseInMemoryDatabase stores entities in .NET dictionaries with no SQL engine. It's extremely fast but enforces no relational constraints (no FK checks, no unique index enforcement, no transactions). SQLite in-memory runs a real SQL engine entirely in RAM. It enforces constraints, supports transactions, and accepts FromSqlRaw queries -- making it a much better choice for testing data-access code.

Does EF Core in-memory database support transactions?

Not in any meaningful way. BeginTransaction() compiles and runs without throwing, but it has no effect. Commit and rollback are no-ops. If your code under test relies on transactional behavior, you must use SQLite or a real database engine for those tests.

Why does the SQLite connection need to stay open for in-memory tests?

SQLite destroys an in-memory database the moment the last connection to it closes. EF Core opens and closes connections internally per operation. If you let EF Core manage the connection lifecycle, your schema disappears between operations. Creating the connection yourself, calling Open() once, and passing that open connection to your DbContextOptionsBuilder keeps the database alive for the full test run.

Can I use EF Core migrations with SQLite in-memory tests?

You can, but it's almost never the right call. Migrate() checks for a __EFMigrationsHistory table and runs each migration in order. That's overhead you don't need in unit tests. Use EnsureCreated() instead -- it creates the full schema from your current model without migration tracking, which is what you want for fast isolated tests.

How do I prevent state leakage between EF Core unit tests?

Three practices eliminate state leakage. First, create a new DbContext instance for every logical operation (seed, act, assert). Second, use unique in-memory database names per test class when using UseInMemoryDatabase. Third, for SQLite, use a single open connection scoped to the test class (via IClassFixture<T>) and seed data in each test's Arrange step rather than sharing seeded data across tests.

When should I use IDbContextFactory instead of a raw DbContext in tests?

Use IDbContextFactory<T> in tests whenever your production code depends on it -- which it should for anything running in Blazor Server, background services, or any scoped DI context that outlives a single request. Injecting IDbContextFactory<T> in tests gives you the same short-lived context pattern your production code uses, which means your tests exercise the same lifetime semantics and catch the same concurrency bugs.

Are SQLite in-memory tests good enough to replace integration tests against SQL Server?

No, and you shouldn't try to make them so. SQLite is a different SQL dialect. SQL Server-specific syntax (OPENJSON, STRING_AGG with the SQL Server signature, columnstore index hints, NOLOCK hints) will fail against SQLite. Treat SQLite in-memory tests as a fast sanity check for your data-access logic, and keep a separate integration test suite that runs against a real SQL Server or PostgreSQL in CI for full correctness verification.

Wrapping Up

Ef core unit testing doesn't have to mean hitting a real database or maintaining a full SQL Server instance in CI. The in-memory provider and SQLite in-memory together cover the vast majority of test scenarios -- quickly, deterministically, and without infrastructure dependencies.

Use the in-memory provider for logic tests above the data layer. Use SQLite in-memory for repository and data-access tests where constraint behavior and SQL correctness matter. Keep the IDbContextFactory<T> pattern consistent between tests and production code. And reserve real database integration tests for migrations, SQL Server-specific queries, and performance validation.

Get those layers right, and your test suite becomes a safety net you actually trust.