Are you still writing ad‑hoc SQL queries to glue your entities together? 90 % of the time there’s a cleaner way—and EF Core can do it for you automatically.
Understanding Loading‑Related Data
Before you start sprinkling Include()
calls everywhere, it helps to know why EF Core offers three distinct strategies for bringing related entities into memory:
Strategy | When EF Core Pulls Data | Typical Use Case |
---|---|---|
Eager loading | Immediately with the main query via Include /ThenInclude | You need the full graph right away (e.g., rendering a view). |
Lazy loading | On‑demand the first time a navigation property is accessed | You don’t know up front which branches you’ll touch |
Explicit loading | Manually by calling Entry(...).Collection(...).Load() | You want precise control inside a prepared unit of work |
Eager Loading in EF Core
Eager loading is straightforward—decorate your query with Include()
and optionally ThenInclude()
:
// Pull orders *and* their items in one round‑trip
var orders = await _db.Orders
.Include(o => o.Items)
.Where(o => o.PlacedAt.Date == DateOnly.FromDateTime(DateTime.UtcNow))
.ToListAsync();
Pros
Single query, predictable SQL, ideal for read models.
Cons
Risk of SELECT N+1 if you forget a needed branch; big cartesian joins when you include too much.
Lazy Loading in EF Core
Lazy loading defers the SQL until the navigation property is touched. To enable it you need:
Microsoft.EntityFrameworkCore.Proxies
NuGet package.optionsBuilder.UseLazyLoadingProxies()
in yourDbContext
.virtual
on each navigation you want lazily loaded.
public class BlogContext : DbContext
{
public DbSet<Post> Posts => Set<Post>();
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options
.UseSqlServer(_connection)
.UseLazyLoadingProxies();
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; } = string.Empty;
public virtual ICollection<Comment> Comments { get; set; } = [];
}
// Somewhere in your application layer
var post = await ctx.Posts.FirstAsync();
// No SQL fired yet for Comments …
Console.WriteLine(post.Comments.Count); // ← Boom, proxy triggers SELECT for comments
Watch‑outs
- Hidden database chatter—profile your queries.
- Lazy loading proxies ignore
AsNoTracking()
; the entities are always tracked.
Explicit Loading in EF Core
Sometimes you want lazy loading’s precision without magical proxies. That’s where explicit loading shines:
var author = await ctx.Authors.SingleAsync(a => a.Id == id);
await ctx.Entry(author)
.Collection(a => a.Posts)
.Query() // still IQueryable – filter if you like
.Where(p => p.Published)
.LoadAsync();
You choose exactly when the extra query runs—perfect for background jobs or CQRS handlers where predictability matters.
Handling Circular References
Bidirectional navigation properties (Order → Customer
and Customer → Orders
) can cause infinite JSON loops—or worse, depth‑first lazy loading cascades.
Strategies for Handling Circular References
- DTO projection (
select new { o.Id, o.Customer!.Name }
)—my default. - JsonSerializerOptions.ReferenceHandler = Preserve when you must dump EF entities to JSON.
- Ignore cycles in AutoMapper with
.PreserveReferences()
. - Disable lazy loading on one side of the relationship.
Example Handling Circular References
builder.Services.AddControllers()
.AddJsonOptions(o =>
{
o.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.Preserve;
o.JsonSerializerOptions.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;
});
Working with Views and Stored Procedures
EF Core Views
Map a view like any other table, but mark it keyless:
modelBuilder.Entity<ActiveCustomerView>()
.HasNoKey()
.ToView("v_ActiveCustomers");
Now you can query DbSet<ActiveCustomerView>
with LINQ yet EF will never attempt an INSERT
.
Stored Procedures
Since EF Core 7 you can map CRUD operations to stored procs:
modelBuilder.Entity<Order>()
.InsertUsingStoredProcedure("usp_InsertOrder")
.UpdateUsingStoredProcedure("usp_UpdateOrder");
Or execute ad‑hoc:
await ctx.Database.ExecuteSqlAsync("EXEC dbo.ResetDailyCounters");
Best Practices and Performance Optimization
Utilize AsNoTracking()
for Read‑Only Scenarios
Tracked entities cost ~30 % more memory. When you’re only rendering, call:
var readOnly = await ctx.Products.AsNoTracking().ToListAsync();
Efficiently Manage DbContext
Lifespan
Keep each DbContext
instance short‑lived—per HTTP request, background job, or CLI command. A long‑lived context accumulates change‑tracker sludge that bloats memory and slows SaveChanges()
as the internal state graph grows.
// ASP.NET Core: one context per request (default)
services.AddDbContext<AppDbContext>(opt =>
opt.UseSqlServer(connString)
.EnableSensitiveDataLogging()); // dev only
// Worker service: manually scope the context
await using var scope = scopeFactory.CreateAsyncScope();
var ctx = scope.ServiceProvider.GetRequiredService<AppDbContext>();
// Short‑lived access helper via factory
public record UserService(IDbContextFactory<AppDbContext> Factory)
{
public async Task<User> GetAsync(int id)
{
await using var ctx = await Factory.CreateDbContextAsync();
return await ctx.Users.FindAsync(id);
}
}
Tip: When you must keep a context alive across a workflow, call
ChangeTracker.Clear()
after large write batches to drop stale state and avoid snapshot inflation.
Pre‑filter Data Early
Filter in the query, not in memory:
var recent = ctx.LogEntries
.Where(l => l.Timestamp >= since)
.Select(l => new { l.Id, l.Message });
Batch Operations
For bulk inserts/updates, use EFCore.BulkExtensions
or ExecuteUpdate/ExecuteDelete
(EF Core 7+).
await ctx.Orders
.Where(o => o.Status == OrderStatus.Draft && o.AgeDays > 30)
.ExecuteDeleteAsync();
Indexing
Index choice can make or break query performance. Start with execution plans (SET SHOWPLAN_XML ON
) to see missing‑index hints, then codify them in your OnModelCreating
so they live in source control.
// Composite index aligns with WHERE Status = ? AND CreatedAt > ?
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.Status, o.CreatedAt })
.IncludeProperties(o => new { o.CustomerId }); // Covering index
// Filtered index: only for completed orders (SQL Server)
modelBuilder.Entity<Order>()
.HasIndex(o => o.Status)
.HasFilter($"[{nameof(Order.Status)}] = {(int)OrderStatus.Completed}");
Rule of thumb: Put the most selective column first in multi‑column indexes and avoid overlapping duplicates—they just waste RAM and disk.
Use sp_BlitzIndex
(open‑source) or Azure’s Automatic Tuning recommendations to monitor fragmentation and usage statistics.
Caching
Second‑level caching layers a memory or distributed cache underneath EF so identical LINQ queries are served without touching the database.
// Program.cs
services.AddEFSecondLevelCache(options =>
{
options
.UseMemoryCacheProvider()
.CacheAllQueries(CacheExpirationMode.Absolute, TimeSpan.FromMinutes(5))
.DisableLogging(true);
});
// Query
var hotProducts = await ctx.Products
.Where(p => p.IsActive)
.OrderByDescending(p => p.Sales)
.Cacheable() // extension method from the package
.ToListAsync();
Gotcha: Cache keys include parameter values and tags like
.AsNoTracking()
. If you change tracking mode, you change the cache key.
For read‑heavy apps at scale, plug the interceptor into Redis so multiple pods share the same cache and avoid stale data by tagging cache entries with entity type names when you call SaveChanges()
.
Regularly Review and Optimize Queries
Every sprint, treat your slowest endpoints like failing tests. Three low‑tech tools uncover most issues:
ToQueryString()
– prints raw SQL for any LINQ query.- Command interceptors – log duration & rows affected.
- EF Core Power Tools – visualize the call tree inside Visual Studio.
var query = ctx.Orders.Where(o => o.Status == OrderStatus.Pending);
Debug.WriteLine(query.ToQueryString()); // Inspect SQL before executing
public class ProfilingInterceptor : DbCommandInterceptor
{
private readonly ILogger<ProfilingInterceptor> _log;
public ProfilingInterceptor(ILogger<ProfilingInterceptor> log) => _log = log;
public override async Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
DbCommand command, CommandEventData eventData,
InterceptionResult<DbDataReader> result, CancellationToken ct = default)
{
var sw = Stopwatch.StartNew();
var response = await base.ReaderExecutingAsync(command, eventData, result, ct);
sw.Stop();
_log.LogInformation("{Elapsed} ms | {Sql}", sw.ElapsedMilliseconds, command.CommandText);
return response;
}
}
Tip: Temporarily add
IncludeGraph()
(from EFCore.Debugging) in QA to auto‑detect N+1 patterns before they land in prod.
Use Pagination for Large Datasets
Fetching an entire table can crash the server and the client alike. Implement offset paging for classic grids and keyset (seek) paging for endless‑scroll UIs.
Offset paging
var pageSize = 50;
var page = 3; // zero‑based
var paged = await ctx.Orders
.OrderBy(o => o.Id)
.Skip(page * pageSize)
.Take(pageSize)
.AsNoTracking()
.ToListAsync();
Keyset paging
// API receives ?after=984
var afterId = 984;
var nextChunk = await ctx.Orders
.Where(o => o.Id > afterId)
.OrderBy(o => o.Id)
.Take(50)
.AsNoTracking()
.ToListAsync();
Schema hint: Index the sort column (
Id
here) or a composite key (CreatedAt
,Id
) to keep scans seekable. Combine with a lightweight DTO projection so the wire payload remains slim.
FAQ: Mastering EF Core Data Loading
No. Proxies are only active inside the original DbContext
scope.
Include()
always bad for performance?Only when you over‑include. Profile the generated SQL—sometimes one fat join beats ten tiny round‑trips.
AsNoTracking()
with lazy loading?EF will silently ignore AsNoTracking()
on proxies; use explicit loading instead.
Enable logging and search for duplicated parameterized SQL; if you see dozens of identical queries, it’s time for Include()
or projection.
Conclusion: Your Data, Your Rules—Now Faster than Ever
Lazy loading isn’t a silver bullet, but when you wield it alongside eager and explicit loading, you gain surgical control over your database traffic. Try swapping one noisy endpoint to lazy loading + DTO projection today and watch your logs—and cloud bill—shrink. Ready to share your experience or gotchas? Drop them in the comments and let’s learn together!