In K2 you wrote tests against SQL Server that read their connection string from an environment variable and were visibly skipped without it. That was honest but inconvenient: someone had to start the database beforehand, and in the pull request the test only ran if the pipeline remembered it. In this lesson the test starts its dependencies itself, in containers, on your own machine just as in the pipeline. First you see why the effort pays off.

Why stand-ins lie

A stand-in is everything that takes the place of the real dependency in a test: a fake HttpMessageHandler, a store in a list, EF Core's in-memory provider. For rules in the model that is right, because there the test should be fast and check nothing but the rule. It becomes dangerous when a test is supposed to prove that the interplay with the real dependency works. Then it checks only what the stand-in can do, and that is less.

The following program uses the in-memory provider to create two contacts with the same email address, even though the model has a unique index on the address:

C#
// inmemory-lies.cs
#:property PublishAot=false
#:package Microsoft.EntityFrameworkCore.InMemory@10.0.14

using Microsoft.EntityFrameworkCore;

var options = new DbContextOptionsBuilder<ContactsContext>()
    .UseInMemoryDatabase("kladde")
    .Options;

await using var db = new ContactsContext(options);
db.Contacts.Add(new Contact { Name = "Ada Sample", Email = "ada@example.com" });
db.Contacts.Add(new Contact { Name = "Ada Duplicate", Email = "ada@example.com" });
await db.SaveChangesAsync();

Console.WriteLine($"Saved: {await db.Contacts.CountAsync()} contacts with the same email address");

public sealed class Contact
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required string Email { get; set; }
}

public sealed class ContactsContext(DbContextOptions<ContactsContext> options) : DbContext(options)
{
    public DbSet<Contact> Contacts => Set<Contact>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Contact>().HasIndex(c => c.Email).IsUnique();
}
Bash
dotnet run inmemory-lies.cs

Saved: 2 contacts with the same email address

The line #:property PublishAot=false turns off, for this file-based app, the compilation to native code that file-based apps otherwise default to; EF Core needs reflection at run time and does not run that way. The in-memory provider is not a database but a collection of lists. What it does not do:

  • It checks no unique indexes, no foreign keys and no CHECK constraints.
  • It knows no transactions and no isolation. Two simultaneous bookings of the same appointment both succeed.
  • It does not translate LINQ into SQL. A query that SQL Server rejects or evaluates differently runs in memory without complaint.
  • It compares text the way C# does. SQL Server compares by the column's collation, and the default collation does not distinguish between uppercase and lowercase.

The EF Core documentation therefore advises against using the in-memory provider for tests. For the Kladde the rule from now on is: whatever the database is supposed to guarantee, you test against the same database that runs in production, so SQL Server. SQLite is no substitute either, because it is a different database with different rules.