EF Core InMemory Provider Caveats and When SQLite, Docker Databases, or Testcontainers Are Safer

Overview

EF Core applications often need tests that verify database-related behavior. The challenge is deciding whether the test should use the real production database provider, a lightweight relational substitute such as SQLite, the EF Core InMemory provider, Docker-based databases, Testcontainers, or a mocked repository layer.

The EF Core InMemory provider is tempting because it is simple and fast to configure. However, it is not a relational database. It does not behave like SQL Server, PostgreSQL, MySQL, or SQLite in many important ways. This means tests can pass with the InMemory provider but fail in production.

This topic is about choosing a safe database testing strategy.

The main options are:

1. EF Core InMemory provider: A non-relational in-memory provider for simple testing scenarios.
2. SQLite in-memory: A lightweight relational provider that can run fully in memory.
3. Docker database: A real database server running in a container.
4. Testcontainers: A test library that starts and stops Docker containers from test code.
5. Repository or data-access abstraction: A layer that allows unit tests to mock query results without running EF Core queries.
6. Dedicated test database: A real database instance or database-per-test setup.

This topic matters because database behavior is full of details that fake providers may not reproduce:

• Foreign key constraints.
• Unique indexes.
• Transactions.
• Rollbacks.
• Raw SQL.
• Provider-specific SQL functions.
• Case sensitivity.
• Collations.
• Null comparison semantics.
• Query translation.
• Migrations.
• Computed columns.
• Default values.
• Concurrency tokens.
• Cascade delete behavior.
• Date/time functions.
• JSON columns.
• Stored procedures.
• Performance and indexes.

This topic is important for interviews because it tests whether a developer understands the difference between fast tests and trustworthy tests. A strong candidate should know when the InMemory provider is acceptable, when it is dangerous, why SQLite is safer but still not perfect, and when a real containerized database is the best option.

Core Concepts

Why Database Testing Strategy Matters

Database code is not just normal C# logic. When using EF Core, important behavior depends on the database provider.

Example:

var users = await context.Users
    .Where(u => u.Email == "[email protected]")
    .ToListAsync();

This query may behave differently depending on the provider:

• SQL Server may compare strings case-insensitively depending on collation.
• SQLite is often case-sensitive by default.
• PostgreSQL is usually case-sensitive unless configured otherwise.
• EF Core InMemory compares using .NET behavior, not SQL behavior.

A test that passes against one provider does not always prove the code works against another provider.

Database testing strategy matters because tests should catch production bugs, not hide them.

What the EF Core InMemory Provider Is

The EF Core InMemory provider stores data in memory instead of using a relational database.

Typical setup:

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseInMemoryDatabase("TestDatabase")
    .Options;

using var context = new AppDbContext(options);

It is useful for very simple tests where you only need to store and retrieve objects through EF Core APIs.

Example:

context.Products.Add(new Product
{
    Id = 1,
    Name = "Keyboard"
});

await context.SaveChangesAsync();

var product = await context.Products
    .SingleAsync(p => p.Id == 1);

However, the InMemory provider is not relational. It is not a SQL database running in memory. It behaves more like an in-memory object store behind EF Core.

That distinction is critical.

Why InMemory Is Not a Relational Database

A relational database enforces relational rules and executes SQL-like behavior.

Relational behavior includes:

• Foreign keys.
• Unique constraints.
• Required columns.
• Transactions.
• Rollbacks.
• SQL translation.
• SQL null semantics.
• Collation.
• Joins executed by the database engine.
• Raw SQL.
• Migrations.
• Computed columns.
• Default constraints.
• Cascade delete constraints.

The InMemory provider does not behave like a relational engine. This means it can hide bugs that would occur in a real database.

Example: foreign key issue.

context.Orders.Add(new Order
{
    Id = 1,
    CustomerId = 999
});

await context.SaveChangesAsync();

If no customer with Id = 999 exists, a relational database with a foreign key constraint should reject this. The InMemory provider may allow it because it does not enforce relational constraints the same way.

This can make tests pass even though production would fail.

InMemory Provider Caveats

Important caveats include:

The key problem is false confidence. Tests may pass because the fake provider is more forgiving than production.

Example: Unique Constraint Not Caught

Model configuration:

modelBuilder.Entity<User>()
    .HasIndex(u => u.Email)
    .IsUnique();

Production behavior should reject duplicate emails.

Test using InMemory:

context.Users.Add(new User
{
    Email = "[email protected]"
});

context.Users.Add(new User
{
    Email = "[email protected]"
});

await context.SaveChangesAsync();

A relational database should fail because of the unique index. The InMemory provider may not enforce this the same way.

Safer test:

• Use SQLite in-memory if SQLite constraint behavior is enough.
• Use the same production provider in Docker/Testcontainers if provider accuracy matters.

Example: Foreign Key Constraint Not Caught

Model:

public sealed class Customer
{
    public int Id { get; set; }
    public List<Order> Orders { get; } = new();
}

public sealed class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
}

Configuration:

modelBuilder.Entity<Order>()
    .HasOne(o => o.Customer)
    .WithMany(c => c.Orders)
    .HasForeignKey(o => o.CustomerId)
    .IsRequired();

Problem test:

context.Orders.Add(new Order
{
    Id = 1,
    CustomerId = 12345
});

await context.SaveChangesAsync();

A real relational database should reject this if no customer exists. InMemory may allow it.

This is dangerous because many production bugs are constraint bugs.

Example: Transaction Behavior Not Tested

Application code:

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

try
{
    context.Orders.Add(order);
    await context.SaveChangesAsync(cancellationToken);

    context.AuditLogs.Add(audit);
    await context.SaveChangesAsync(cancellationToken);

    await transaction.CommitAsync(cancellationToken);
}
catch
{
    await transaction.RollbackAsync(cancellationToken);
    throw;
}

A test using InMemory cannot reliably verify real rollback behavior because it does not support relational transactions like a real database.

If the code depends on transaction behavior, use a real relational provider.

Good choices:

• SQLite in-memory for simple relational transaction behavior.
• SQL Server/PostgreSQL/MySQL in Docker for provider-accurate transaction behavior.
• Testcontainers for automated container lifecycle.

Example: Raw SQL Not Tested

Application code:

var recentOrders = await context.Orders
    .FromSqlRaw("""
        SELECT *
        FROM Orders
        WHERE CreatedAtUtc >= {0}
        """, startDate)
    .ToListAsync();

The InMemory provider cannot execute raw SQL.

If your application uses raw SQL, stored procedures, database views, functions, or provider-specific SQL, InMemory is not suitable for testing that behavior.

Use:

• SQLite if the SQL is compatible with SQLite.
• The real database provider if SQL is provider-specific.
• Testcontainers if you want tests to create the real database automatically.

Example: Query Translation Not Tested

EF Core query:

var users = await context.Users
    .Where(u => EF.Functions.Like(u.Email, "%@example.com"))
    .ToListAsync();

Relational providers translate this into SQL. InMemory does not execute real SQL translation in the same way.

Another example:

var orders = await context.Orders
    .Where(o => EF.Functions.DateDiffDay(o.CreatedAtUtc, DateTime.UtcNow) <= 7)
    .ToListAsync();

This is SQL Server-specific. It cannot be meaningfully validated using InMemory or SQLite.

If the purpose of the test is to verify that LINQ translates and executes correctly against SQL Server, the test must use SQL Server.

Example: Case Sensitivity Differences

Suppose production uses SQL Server with case-insensitive collation.

var user = await context.Users
    .SingleOrDefaultAsync(u => u.Email == "[email protected]");

If the database contains:

[email protected]

SQL Server may match it depending on collation. SQLite or InMemory may behave differently.

This matters for:

• Login by email.
• Search features.
• Unique indexes.
• Normalized fields.
• Usernames.
• Tags.
• Codes.
• Filtering and sorting.

Tests should use the provider behavior that matters for production.

Example: Default Values and Computed Columns

Model configuration:

modelBuilder.Entity<Order>()
    .Property(o => o.CreatedAtUtc)
    .HasDefaultValueSql("SYSUTCDATETIME()");

In production SQL Server, the database sets the default value.

With InMemory, there is no SQL Server default constraint. Unless your code sets CreatedAtUtc, the behavior may not match production.

Computed column example:

modelBuilder.Entity<Order>()
    .Property(o => o.SearchText)
    .HasComputedColumnSql("[OrderNumber] + ' ' + [CustomerName]");

This cannot be properly tested with InMemory.

Use the real provider when database-generated values are important.

Example: Navigation Fix-Up Can Hide Missing Include

A common issue in EF tests is accidentally relying on tracked entities from arrange setup.

Example:

// Arrange
var customer = new Customer
{
    Id = 1,
    Name = "Alice",
    Orders =
    {
        new Order { Id = 1, OrderNumber = "ORD-001" }
    }
};

context.Customers.Add(customer);
await context.SaveChangesAsync();

// Act
var loadedCustomer = await context.Customers
    .SingleAsync(c => c.Id == 1);

// Assert
Assert.NotEmpty(loadedCustomer.Orders);

This test may pass because the same context is tracking customer and its Orders. In real application code using a new context, Orders may not be loaded unless Include is used.

Better test pattern:

context.ChangeTracker.Clear();

var loadedCustomer = await context.Customers
    .Include(c => c.Orders)
    .SingleAsync(c => c.Id == 1);

Assert.NotEmpty(loadedCustomer.Orders);

Even better, use a relational provider and design tests around realistic query behavior.

When the InMemory Provider Is Acceptable

The InMemory provider can still be useful in narrow cases.

Acceptable use cases:

• Simple tests that do not depend on relational behavior.
• Tests for application logic that only needs basic persistence.
• Tests where database correctness is not the focus.
• Legacy test suites that already use it.
• Very small prototypes.
• Tests for simple EF-based services where constraints, transactions, SQL, and provider behavior do not matter.

Example acceptable test:

[Fact]
public async Task AddProduct_AddsProductToContext()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseInMemoryDatabase(Guid.NewGuid().ToString())
        .Options;

    await using var context = new AppDbContext(options);

    context.Products.Add(new Product
    {
        Name = "Keyboard"
    });

    await context.SaveChangesAsync();

    Assert.Single(context.Products);
}

But even here, ask whether the test provides meaningful confidence.

If the test is supposed to verify database behavior, InMemory is usually the wrong tool.

SQLite In-Memory

SQLite in-memory is a lightweight relational database option.

Setup:

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

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

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

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

Important rule:

The SQLite in-memory database exists only while the connection remains open. If the connection is closed, the database is destroyed.

This is why tests often keep a shared open connection for the lifetime of the test or fixture.

SQLite In-Memory Example with Test Fixture

Example xUnit fixture:

public sealed class SqliteTestDatabase : IAsyncLifetime
{
    private SqliteConnection _connection = null!;

    public DbContextOptions<AppDbContext> Options { get; private set; } = null!;

    public async Task InitializeAsync()
    {
        _connection = new SqliteConnection("Data Source=:memory:");
        await _connection.OpenAsync();

        Options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(_connection)
            .Options;

        await using var context = new AppDbContext(Options);
        await context.Database.EnsureCreatedAsync();

        context.Categories.Add(new Category
        {
            Id = 1,
            Name = "Default"
        });

        await context.SaveChangesAsync();
    }

    public async Task DisposeAsync()
    {
        await _connection.DisposeAsync();
    }
}

Test:

public sealed class ProductRepositoryTests
    : IClassFixture<SqliteTestDatabase>
{
    private readonly SqliteTestDatabase _database;

    public ProductRepositoryTests(SqliteTestDatabase database)
    {
        _database = database;
    }

    [Fact]
    public async Task AddProduct_WithValidCategory_SavesProduct()
    {
        await using var context = new AppDbContext(_database.Options);

        context.Products.Add(new Product
        {
            Name = "Keyboard",
            CategoryId = 1
        });

        await context.SaveChangesAsync();

        var exists = await context.Products
            .AnyAsync(p => p.Name == "Keyboard");

        Assert.True(exists);
    }
}

SQLite gives more realistic relational behavior than InMemory, but it is still not the same as SQL Server or PostgreSQL.

SQLite Is Safer Than InMemory, But Not Perfect

SQLite is relational, so it can test many behaviors InMemory cannot.

SQLite can help test:

• Foreign key constraints.
• Unique constraints.
• Transactions.
• Relational query translation.
• Basic raw SQL if SQL is SQLite-compatible.
• Real SQL execution.
• Migrations in some simple cases.
• Database-generated values in SQLite-compatible ways.

However, SQLite is not SQL Server, PostgreSQL, or MySQL.

SQLite differences may include:

• SQL dialect.
• Data types.
• Date/time functions.
• Case sensitivity.
• Collation.
• Decimal precision behavior.
• Schema support.
• Computed columns.
• Stored procedures.
• JSON capabilities.
• Provider-specific EF functions.
• Migration behavior.
• Concurrency behavior.
• Full-text search features.
• Index behavior.

Use SQLite when you want a fast relational fake and can accept provider differences.

Use the real database provider when provider behavior matters.

SQLite Foreign Key Behavior

SQLite supports foreign keys, but they must be enabled. EF Core's SQLite provider usually enables them when opening a connection, but if you manage connections manually or use raw SQLite settings, verify behavior.

Example:

context.Orders.Add(new Order
{
    CustomerId = 999
});

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

This kind of test is meaningful with a relational provider, but not with InMemory.

SQLite Caveat: SQL Server-Specific Functions

SQL Server-specific EF function:

var orders = await context.Orders
    .Where(o => EF.Functions.DateDiffDay(o.CreatedAtUtc, DateTime.UtcNow) < 7)
    .ToListAsync();

This is not portable to SQLite.

If the application uses provider-specific EF functions, the tests should use that provider.

For SQL Server:

• LocalDB on Windows.
• SQL Server Docker container.
• Testcontainers SQL Server module.
• Dedicated SQL Server test database.

For PostgreSQL:

• PostgreSQL Docker container.
• Testcontainers PostgreSQL module.
• Dedicated PostgreSQL test database.

Docker Databases

A Docker database means running the real database engine in a container.

Example:

docker run -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=Your_password123 \
  -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

Or PostgreSQL:

docker run -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 postgres:16

Benefits:

• Uses the real database engine.
• Tests real SQL translation.
• Tests real constraints.
• Tests migrations.
• Tests provider-specific behavior.
• Closer to production.
• Good for integration and CI tests.

Trade-offs:

• Requires Docker.
• Slower startup than InMemory or SQLite.
• Needs cleanup.
• Requires test isolation.
• CI pipeline must support containers.
• More infrastructure complexity.
• Must manage connection strings and ports.

Docker databases are safer when correctness matters more than minimal setup.

Testcontainers

Testcontainers is a library that lets tests start and stop Docker containers programmatically.

Instead of requiring a manually running database, test code can create the container.

Example packages:

dotnet add package Testcontainers.PostgreSql
dotnet add package Testcontainers.MsSql

Example PostgreSQL fixture:

using DotNet.Testcontainers.Builders;
using Testcontainers.PostgreSql;

public sealed class PostgreSqlDatabaseFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _container =
        new PostgreSqlBuilder()
            .WithImage("postgres:16")
            .WithDatabase("app_test")
            .WithUsername("postgres")
            .WithPassword("postgres")
            .Build();

    public string ConnectionString => _container.GetConnectionString();

    public async Task InitializeAsync()
    {
        await _container.StartAsync();

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

        await using var context = new AppDbContext(options);
        await context.Database.MigrateAsync();
    }

    public Task DisposeAsync()
    {
        return _container.DisposeAsync().AsTask();
    }
}

Example SQL Server fixture:

using Testcontainers.MsSql;

public sealed class SqlServerDatabaseFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container =
        new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
            .WithPassword("Your_password123")
            .Build();

    public string ConnectionString => _container.GetConnectionString();

    public async Task InitializeAsync()
    {
        await _container.StartAsync();

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

        await using var context = new AppDbContext(options);
        await context.Database.MigrateAsync();
    }

    public Task DisposeAsync()
    {
        return _container.DisposeAsync().AsTask();
    }
}

Testcontainers is often the best balance between realistic integration testing and automated test setup.

Testcontainers with WebApplicationFactory

A common pattern is to use Testcontainers with ASP.NET Core integration tests.

Custom factory:

public sealed class CustomWebApplicationFactory
    : WebApplicationFactory<Program>,
      IAsyncLifetime
{
    private readonly PostgreSqlContainer _postgres =
        new PostgreSqlBuilder()
            .WithImage("postgres:16")
            .WithDatabase("app_test")
            .WithUsername("postgres")
            .WithPassword("postgres")
            .Build();

    public async Task InitializeAsync()
    {
        await _postgres.StartAsync();
    }

    public new async Task DisposeAsync()
    {
        await _postgres.DisposeAsync();
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");

        builder.ConfigureServices(services =>
        {
            var descriptor = services.SingleOrDefault(
                service => service.ServiceType ==
                    typeof(DbContextOptions<AppDbContext>));

            if (descriptor is not null)
            {
                services.Remove(descriptor);
            }

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseNpgsql(_postgres.GetConnectionString());
            });

            using var serviceProvider = services.BuildServiceProvider();
            using var scope = serviceProvider.CreateScope();

            var context = scope.ServiceProvider
                .GetRequiredService<AppDbContext>();

            context.Database.Migrate();
        });
    }
}

Test:

public sealed class OrdersApiTests
    : IClassFixture<CustomWebApplicationFactory>
{
    private readonly CustomWebApplicationFactory _factory;

    public OrdersApiTests(CustomWebApplicationFactory factory)
    {
        _factory = factory;
    }

    [Fact]
    public async Task CreateOrder_WithValidRequest_ReturnsCreated()
    {
        using var client = _factory.CreateClient();

        var response = await client.PostAsJsonAsync("/api/orders", new
        {
            CustomerId = 1,
            ProductId = 10,
            Quantity = 2
        });

        Assert.Equal(HttpStatusCode.Created, response.StatusCode);
    }
}

This pattern tests the ASP.NET Core pipeline and the real database provider.

When Testcontainers Are Safer

Testcontainers are safer than InMemory or SQLite when your test needs to verify provider-specific behavior.

Use Testcontainers when testing:

• EF Core migrations.
• Raw SQL.
• Stored procedures.
• Views.
• Database functions.
• JSON columns.
• Case-insensitive/case-sensitive behavior.
• SQL Server DateDiff functions.
• PostgreSQL arrays or JSONB.
• Foreign keys.
• Unique constraints.
• Transactions and rollbacks.
• Cascade deletes.
• Optimistic concurrency tokens.
• Database-generated values.
• Computed columns.
• Integration with WebApplicationFactory.
• Real query translation.
• Production-like behavior.

Testcontainers are also useful in CI because they remove the need for a manually provisioned database.

Testcontainers Trade-Offs

Testcontainers are powerful but have trade-offs.

Trade-offs:

• Requires Docker or a compatible container runtime.
• Slower startup than InMemory or SQLite.
• More complex setup.
• Requires container support in CI.
• Tests may be harder to debug if container logs are not captured.
• Parallel tests need isolation strategy.
• Images should be pinned to avoid version drift.
• First test run may be slower due to image pull.
• Test data cleanup must be designed carefully.

Best practices:

• Pin image versions.
• Reuse a container per test class when safe.
• Use one database per test when isolation matters.
• Reset database state between tests.
• Apply migrations at startup.
• Capture container logs when debugging.
• Keep test data deterministic.
• Avoid depending on test order.
• Limit parallelism when the database is shared.

Dedicated Test Database

A dedicated test database can be a real SQL Server/PostgreSQL/MySQL database used only for tests.

Options:

• One database for all tests with cleanup.
• One database per test class.
• One database per test.
• One schema per test.
• One transaction per test with rollback.
• One tenant/test partition per test.

Benefits:

• Real provider behavior.
• Can be faster than starting a container each time.
• Useful for local development teams.
• Useful in CI with managed database services.

Trade-offs:

• Requires setup and maintenance.
• Tests can interfere without cleanup.
• Connection strings must be managed.
• Parallel execution needs design.
• Risk if tests point to wrong database.

Never run destructive tests against production or shared development databases.

Test Isolation Strategies

Database tests need isolation.

Common strategies:

Choose based on test speed, reliability, and database behavior.

Repository Pattern as a Test Double Boundary

Another option is to avoid EF Core in unit tests by introducing a repository or data-access abstraction.

Example:

public interface IOrderRepository
{
    Task<Order?> GetByIdAsync(int id, CancellationToken cancellationToken);
    Task AddAsync(Order order, CancellationToken cancellationToken);
    Task SaveChangesAsync(CancellationToken cancellationToken);
}

Application service:

public sealed class OrderService
{
    private readonly IOrderRepository _orders;

    public OrderService(IOrderRepository orders)
    {
        _orders = orders;
    }

    public async Task SubmitAsync(int orderId, CancellationToken cancellationToken)
    {
        var order = await _orders.GetByIdAsync(orderId, cancellationToken);

        if (order is null)
        {
            throw new InvalidOperationException("Order not found.");
        }

        order.Submit();

        await _orders.SaveChangesAsync(cancellationToken);
    }
}

Unit test can mock the repository:

var repository = new Mock<IOrderRepository>();

repository
    .Setup(r => r.GetByIdAsync(1, It.IsAny<CancellationToken>()))
    .ReturnsAsync(new Order());

var service = new OrderService(repository.Object);

await service.SubmitAsync(1, CancellationToken.None);

repository.Verify(r => r.SaveChangesAsync(It.IsAny<CancellationToken>()));

This is useful for application logic tests. But it does not test EF Core queries. You still need integration tests for the repository implementation.

Why Mocking DbSet Is Usually a Bad Idea

Mocking DbSet query behavior is usually unreliable.

Reason:

• EF Core queries are LINQ expression trees translated by a provider.
• Mocked DbSet often executes LINQ in memory.
• In-memory LINQ does not match SQL translation.
• Provider-specific behavior is not tested.
• Raw SQL is not tested.
• Includes and tracking behavior may not match.
• Tests can pass while production queries fail.

Instead of mocking DbSet:

• Test application logic through a repository abstraction.
• Test EF Core query code against a real provider.
• Use SQLite in-memory if a lightweight relational fake is acceptable.
• Use Testcontainers for provider-accurate tests.

InMemory vs SQLite vs Testcontainers

Comparison:

Rule of thumb:

• Use InMemory rarely and narrowly.
• Use SQLite in-memory when you need fast relational behavior but can accept provider differences.
• Use Testcontainers when provider correctness matters.
• Use repository mocks for pure application unit tests.
• Use a real test database for critical data-access behavior.

Choosing the Right Option

Choose InMemory when:

• The test does not care about relational behavior.
• You only need a simple fake store.
• You are testing simple application flow.
• You accept that database behavior is not validated.

Choose SQLite in-memory when:

• You want fast relational behavior.
• You want constraints and transactions.
• Your queries are provider-neutral.
• You do not use provider-specific SQL.
• Production provider differences are acceptable for that test.

Choose Testcontainers or Docker database when:

• You need production-provider behavior.
• You use migrations.
• You use raw SQL.
• You use provider-specific functions.
• You rely on constraints, transactions, computed columns, or concurrency.
• The test should prove the app works with the real database engine.

Choose repository mocks when:

• You are unit testing application/domain logic.
• You do not want EF Core involved.
• You can mock query results at a higher abstraction.
• You still have separate integration tests for repository queries.

Testing EF Core Migrations

Do not test migrations with InMemory.

Migrations are relational database schema changes. They should be tested against a relational provider, ideally the real provider.

Migration test example:

await using var context = new AppDbContext(options);

await context.Database.MigrateAsync();

var canConnect = await context.Database.CanConnectAsync();

Assert.True(canConnect);

A stronger migration test may:

• Apply migrations from empty database.
• Seed required data.
• Verify important tables exist.
• Verify indexes/constraints exist.
• Insert rows that should succeed.
• Insert rows that should fail due to constraints.
• Test downgrade only if your team supports downgrade migrations.

Use Testcontainers for realistic migration tests.

Testing Constraints

Constraint tests should use a relational provider.

Example unique constraint test:

[Fact]
public async Task SaveChanges_WhenDuplicateEmail_Throws()
{
    await using var context = CreateContext();

    context.Users.Add(new User
    {
        Email = "[email protected]"
    });

    context.Users.Add(new User
    {
        Email = "[email protected]"
    });

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

This test is meaningful only if the provider enforces the unique index.

Use SQLite or the real provider. Prefer the real provider if the exact exception type, error code, or behavior matters.

Testing Transactions and Rollbacks

Transaction tests require a provider with transaction support.

Example:

await using var context = CreateContext();

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

context.Products.Add(new Product
{
    Name = "Temporary Product"
});

await context.SaveChangesAsync();

await transaction.RollbackAsync();

var exists = await context.Products
    .AnyAsync(p => p.Name == "Temporary Product");

Assert.False(exists);

This should not be tested with InMemory if rollback behavior matters.

Testing Raw SQL and Stored Procedures

If your application uses raw SQL, test it with the actual database provider.

Example:

var users = await context.Users
    .FromSqlInterpolated($"""
        SELECT *
        FROM Users
        WHERE IsActive = 1
        """)
    .ToListAsync();

For SQL Server stored procedure:

var reportRows = await context.ReportRows
    .FromSqlInterpolated($"EXEC dbo.GetMonthlyReport {month}")
    .ToListAsync();

SQLite and InMemory cannot validate SQL Server stored procedures. Use SQL Server in Docker/Testcontainers.

Testing Case Sensitivity and Collation

If your system depends on case-insensitive email lookup, test it against the production provider or configure the test provider to match production behavior.

Example:

var user = await context.Users
    .SingleOrDefaultAsync(u => u.Email == request.Email);

Important questions:

• Is the database collation case-insensitive?
• Is email normalized before storage?
• Is there a unique index on normalized email?
• Does the test provider behave the same?
• Does the query use the same column and index as production?

InMemory tests are not enough for these behaviors.

Testing Optimistic Concurrency

EF Core concurrency tokens depend on provider behavior.

Example SQL Server rowversion:

modelBuilder.Entity<Product>()
    .Property(p => p.RowVersion)
    .IsRowVersion();

This should be tested against SQL Server if production uses SQL Server rowversion.

Concurrency test:

await using var context1 = CreateContext();
await using var context2 = CreateContext();

var product1 = await context1.Products.SingleAsync(p => p.Id == productId);
var product2 = await context2.Products.SingleAsync(p => p.Id == productId);

product1.Name = "Updated by context 1";
await context1.SaveChangesAsync();

product2.Name = "Updated by context 2";

await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
    () => context2.SaveChangesAsync());

Use the real provider for high-confidence concurrency tests.

Testing Query Performance

InMemory and SQLite cannot prove SQL Server/PostgreSQL query performance.

Performance depends on:

• Generated SQL.
• Query plans.
• Indexes.
• Statistics.
• Data volume.
• Collation.
• Join strategy.
• Sorts.
• Locks.
• IO.
• Provider-specific translation.

Use the real database provider for query performance tests.

Useful checks:

• Generated SQL with ToQueryString().
• Query plan in database tooling.
• Row counts.
• Index usage.
• Command duration logs.
• Query tags.
• Realistic data volume.

Testing with WebApplicationFactory and SQLite

Custom factory with SQLite in-memory:

public sealed class CustomWebApplicationFactory
    : WebApplicationFactory<Program>
{
    private SqliteConnection? _connection;

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");

        builder.ConfigureServices(services =>
        {
            var descriptor = services.SingleOrDefault(
                service => service.ServiceType ==
                    typeof(DbContextOptions<AppDbContext>));

            if (descriptor is not null)
            {
                services.Remove(descriptor);
            }

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

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseSqlite(_connection);
            });

            using var provider = services.BuildServiceProvider();
            using var scope = provider.CreateScope();

            var context = scope.ServiceProvider
                .GetRequiredService<AppDbContext>();

            context.Database.EnsureCreated();
            Seed(context);
        });
    }

    protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
        _connection?.Dispose();
    }

    private static void Seed(AppDbContext context)
    {
        context.Customers.Add(new Customer
        {
            Name = "Test Customer"
        });

        context.SaveChanges();
    }
}

This is good for fast API integration tests where SQLite differences are acceptable.

Testing with WebApplicationFactory and Testcontainers

Custom factory with Testcontainers can use the real database provider.

Example concept:

public sealed class ApiFactory
    : WebApplicationFactory<Program>,
      IAsyncLifetime
{
    private readonly MsSqlContainer _sqlServer =
        new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
            .WithPassword("Your_password123")
            .Build();

    public async Task InitializeAsync()
    {
        await _sqlServer.StartAsync();
    }

    public new async Task DisposeAsync()
    {
        await _sqlServer.DisposeAsync();
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");

        builder.ConfigureServices(services =>
        {
            var descriptor = services.SingleOrDefault(
                service => service.ServiceType ==
                    typeof(DbContextOptions<AppDbContext>));

            if (descriptor is not null)
            {
                services.Remove(descriptor);
            }

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseSqlServer(_sqlServer.GetConnectionString());
            });
        });
    }
}

In the test setup, apply migrations:

using var scope = factory.Services.CreateScope();

var context = scope.ServiceProvider
    .GetRequiredService<AppDbContext>();

await context.Database.MigrateAsync();

This tests the API pipeline and real SQL Server behavior.

Common Mistakes

Common mistakes include:

• Using InMemory for tests that depend on relational constraints.
• Assuming InMemory is a faster version of SQL Server.
• Testing queries with InMemory and assuming they will translate to SQL.
• Ignoring case sensitivity and collation differences.
• Using SQLite for SQL Server-specific functions.
• Forgetting to keep SQLite in-memory connection open.
• Using EF InMemory and missing foreign key bugs.
• Using EF InMemory and missing unique constraint bugs.
• Testing transactions with InMemory.
• Testing raw SQL with a provider that cannot execute it.
• Mocking DbSet query behavior and trusting the result.
• Sharing one test database without cleanup.
• Depending on test execution order.
• Using Testcontainers but not pinning image versions.
• Running too many containers in parallel without considering CI capacity.
• Not applying migrations in integration tests.
• Using EnsureCreated when the test is meant to validate migrations.
• Reusing tracked entities in tests and hiding missing Include calls.
• Letting test data leak between tests.
• Treating all tests as integration tests when unit tests would be simpler.

Best Practices

Use the real production database provider for important data-access tests.

Use Testcontainers or Docker databases when provider-specific behavior matters.

Use SQLite in-memory when you want a fast relational fake and can accept provider differences.

Use EF Core InMemory only for narrow tests that do not depend on relational behavior.

Avoid mocking DbSet for query behavior.

Use repository or data-access abstractions for pure application unit tests.

Keep database integration tests focused on important query and persistence behavior.

Test migrations against a real relational provider.

Test constraints, transactions, concurrency, raw SQL, and provider-specific functions against the real provider.

Keep SQLite in-memory connections open for the test lifetime.

Reset database state between tests.

Use deterministic seed data.

Avoid test order dependencies.

Pin Docker image versions.

Balance speed and confidence: not every test needs a real database, but critical database behavior should be tested with one.