Carregamento imediato, tardio e explícito

Três estratégias de carregamento

Carregamento antecipado com Include

// Single query with JOIN
var blogs = await _db.Blogs
    .Include(b => b.Posts)
    .Where(b => b.IsActive)
    .ToListAsync();

// SQL: SELECT * FROM Blogs LEFT JOIN Posts ON ...

ThenInclude para relações aninhadas

var orders = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines)
        .ThenInclude(l => l.Product)
            .ThenInclude(p => p.Category)
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync();

Include com filtro

// Load only published posts for each blog
var blogs = await _db.Blogs
    .Include(b => b.Posts.Where(p => p.IsPublished)
                         .OrderByDescending(p => p.PublishedAt)
                         .Take(5))
    .ToListAsync();

Configuração do carregamento tardio

// 1. Install package: dotnet add package Microsoft.EntityFrameworkCore.Proxies
// 2. Enable in DbContext setup:
builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlite(connStr)
       .UseLazyLoadingProxies());

// 3. Mark navigation properties as virtual:
public class Blog
{
    public int Id { get; set; }
    public virtual ICollection<Post> Posts { get; set; } = new List<Post>();
}

Carregamento tardio em ação (e a armadilha do N+1)

var blogs = await _db.Blogs.ToListAsync(); // 1 query

foreach (var blog in blogs)
{
    // BAD: each access fires a new query!
    var count = blog.Posts.Count; // N queries
    Console.WriteLine(count);
}

// FIX: Use eager loading
var blogs2 = await _db.Blogs.Include(b => b.Posts).ToListAsync();

Carregamento explícito

var blog = await _db.Blogs.FindAsync(1); // no posts loaded

// Load posts only when needed
if (needsPosts)
{
    await _db.Entry(blog!)
             .Collection(b => b.Posts)
             .LoadAsync();
}

// Load single reference
var post = await _db.Posts.FindAsync(99);
await _db.Entry(post!)
         .Reference(p => p.Blog)
         .LoadAsync();

Carregamento explícito com filtro de consulta

var blog = await _db.Blogs.FindAsync(1);

// Only load posts published this year
await _db.Entry(blog!)
         .Collection(b => b.Posts)
         .Query()
         .Where(p => p.PublishedAt.Year == DateTime.UtcNow.Year)
         .LoadAsync();

Console.WriteLine(blog!.Posts.Count);

Consultas divididas para carregamentos extensos com Include

var orders = await _db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Shipments)
    .AsSplitQuery()  // 3 separate SELECTs instead of one giant JOIN
    .ToListAsync();

// Or configure globally:
opt.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);

Projeção para evitar buscar dados em excesso

var summaries = await _db.Blogs
    .Select(b => new BlogSummaryDto
    {
        Name      = b.Name,
        PostCount = b.Posts.Count(p => p.IsPublished),
        LatestPost = b.Posts
            .Where(p => p.IsPublished)
            .OrderByDescending(p => p.PublishedAt)
            .Select(p => p.Title)
            .FirstOrDefault()
    })
    .ToListAsync();
// No Include needed - translated to subqueries

Na prática: escolhendo a estratégia certa

Verificação rápida

Recapitulação: estratégias de carregamento