So far the Kladde loads everything for every request: KladdeBook.OpenAsync fetches all contacts, appointments and notes, and a search runs with KladdeSearch.Find from lesson 1.5.2 over the list in memory. With twelve contacts nobody notices. With the 50 invented ones from lesson 2.4.8, not yet either. With 5,000, though, every search reads 5,000 rows to show 20 of them.

A database can do better: it searches by itself and sends only what was asked for. In this lesson you write queries in LINQ, read the SQL that EF Core makes of them, and find the most common brake on an application with an O/R mapper, the N+1 problem. At the end the Kladde searches its contacts in SQL Server, one page at a time.

A playground with a database of its own

First try things out on a small model that has nothing to do with the Kladde: companies and their contacts, with a navigation in both directions. In k2-exercises/ef-playground, create the file queries.cs:

C#
#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.14
#:package System.Security.Cryptography.Pkcs@10.0.14
#:property PublishAot=false
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;

// A playground of its own: every run builds the database "playground" anew and fills it.
await using (var setup = new PlaygroundContext(log: false))
{
    await setup.Database.EnsureDeletedAsync();
    await setup.Database.EnsureCreatedAsync();
    setup.Companies.AddRange(PlaygroundData.Companies());
    await setup.SaveChangesAsync();
}

await using var db = new PlaygroundContext(log: true);
switch (args.FirstOrDefault())
{
    case "n+1":
        List<Company> companies = await db.Companies.OrderBy(c => c.Name).ToListAsync();
        foreach (Company company in companies)
        {
            int count = await db.Contacts.CountAsync(c => c.CompanyId == company.Id);
            Console.WriteLine($"{company.Name}: {count} contacts");
        }

        break;

    case "include":
        foreach (Company company in await db.Companies.Include(c => c.Contacts).OrderBy(c => c.Name).ToListAsync())
        {
            Console.WriteLine($"{company.Name}: {company.Contacts.Count} contacts");
        }

        break;

    case "projection":
        var counts = await db.Companies
            .OrderBy(c => c.Name)
            .Select(c => new { c.Name, Contacts = c.Contacts.Count })
            .ToListAsync();
        foreach (var row in counts)
        {
            Console.WriteLine($"{row.Name}: {row.Contacts} contacts");
        }

        break;

    case "tracking":
        List<Contact> all = await db.Contacts.ToListAsync();
        Console.WriteLine($"ToListAsync: {all.Count} loaded, {db.ChangeTracker.Entries().Count()} tracked");
        db.ChangeTracker.Clear();
        List<Contact> readOnly = await db.Contacts.AsNoTracking().ToListAsync();
        Console.WriteLine($"AsNoTracking: {readOnly.Count} loaded, {db.ChangeTracker.Entries().Count()} tracked");
        break;

    case "move":
        int moved = await db.Companies
            .Where(c => c.Name == "Kranich Software")
            .ExecuteUpdateAsync(s => s.SetProperty(c => c.City, "Lübeck"));
        int removed = await db.Contacts
            .Where(c => c.Company != null && c.Company.Name == "Harbor Office East")
            .ExecuteDeleteAsync();
        Console.WriteLine($"moved: {moved}, deleted: {removed}");
        break;

    case "sql":
        string term = "an";
        Console.WriteLine(db.Contacts.Where(c => c.Name.Contains(term)).OrderBy(c => c.Name).ToQueryString());
        Console.WriteLine();
        Console.WriteLine(db.Contacts.Where(c => Rules.IsFromHamburg(c)).ToQueryString());
        break;

    default:
        Console.WriteLine("Usage: dotnet run queries.cs -- sql | tracking | n+1 | include | projection | move");
        return;
}

Console.WriteLine($"Commands sent to the database: {db.Commands}");

public static class Rules
{
    public static bool IsFromHamburg(Contact contact) => contact.Company?.City == "Hamburg";
}

public sealed class Company
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required string City { get; set; }
    public List<Contact> Contacts { get; } = [];
}

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

public sealed class PlaygroundContext(bool log) : DbContext
{
    public int Commands { get; private set; }

    public DbSet<Company> Companies => Set<Company>();

    public DbSet<Contact> Contacts => Set<Contact>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        string kladde = Environment.GetEnvironmentVariable("ConnectionStrings__Kladde") ?? "Server=localhost,1433";
        var builder = new SqlConnectionStringBuilder(kladde) { InitialCatalog = "playground" };
        options.UseSqlServer(builder.ConnectionString);
        if (log)
        {
            options.LogTo(
                message => { Commands++; Console.WriteLine(message); },
                [RelationalEventId.CommandExecuted],
                LogLevel.Information,
                DbContextLoggerOptions.None);
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Company>().Property(c => c.Name).HasMaxLength(200);
        modelBuilder.Entity<Company>().Property(c => c.City).HasMaxLength(100);
        modelBuilder.Entity<Contact>().Property(c => c.Name).HasMaxLength(100);
        modelBuilder.Entity<Contact>().Property(c => c.Email).HasMaxLength(254);
    }
}

public static class PlaygroundData
{
    public static List<Company> Companies()
    {
        (string Name, string City, string[] People)[] firms =
        [
            ("Kranich Software", "Hamburg", ["Ada Sample", "Bernd Example", "Ivo Annahme", "Jana Kurz", "Kai Lang", "Lena Wolf"]),
            ("Lindenhof Practice", "Bremen", ["Clara Probst", "Dora Fink", "Emil Stein"]),
            ("Northern Lights Print", "Kiel", ["Fritz Ober", "Gina Roth", "Hugo Beck", "Inge Sauer", "Jens Blum"]),
            ("Harbor Office East", "Rostock", ["Karl Weise", "Lisa Kern"]),
            ("Feld & Partner", "Lübeck", ["Mara Feld", "Nils Hahn", "Olga Pohl", "Paul Rau"]),
        ];

        var companies = new List<Company>();
        foreach ((string name, string city, string[] people) in firms)
        {
            var company = new Company { Name = name, City = city };
            company.Contacts.AddRange(people.Select(p => new Contact { Name = p, Email = p.ToLowerInvariant().Replace(' ', '.') + "@testdata.example" }));
            companies.Add(company);
        }

        return companies;
    }
}

Three things about it are new:

  • A navigation in both directions. Company.Contacts and Contact.Company are navigations: properties that point not to a column but to other entities. EF Core recognizes the foreign key for them from CompanyId. The Kladde deliberately has hardly any such navigations (lesson 2.4.6); here they show what Include does.
  • EnsureDeletedAsync and EnsureCreatedAsync. The playground deletes its database on every run and creates it anew without migrations. That is the one case from lesson 2.4.7 where EnsureCreated fits: a database that nobody wants to keep. The Kladde never does this. As in the notebook, the connection comes from ConnectionStrings__Kladde, with a database of its own, playground.
  • LogTo writes every command that EF Core sends to the database to the console. The list with RelationalEventId.CommandExecuted lets through only this one kind of message, and DbContextLoggerOptions.None leaves out date and category. On the side, the playground counts how many commands there were.