From dotnet-data
Optimizes EF Core queries by fixing N+1 issues, using NoTracking, split queries, projections, and compiled queries. For slow queries, excessive SQL, or high DB load.
npx claudepluginhub dotnet/skills --plugin dotnet-dataThis skill uses the workspace's default tool permissions.
- EF Core queries are slow or generating too many SQL statements
Guides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Builds scalable data pipelines, modern data warehouses, and real-time streaming architectures using Spark, dbt, Airflow, Kafka, and cloud platforms like Snowflake, BigQuery.
Builds production Apache Airflow DAGs with best practices for operators, sensors, testing, and deployment. For data pipelines, workflow orchestration, and batch job scheduling.
| Input | Required | Description |
|---|---|---|
| Slow EF Core queries | Yes | The LINQ queries or DbContext usage to optimize |
| SQL output or logs | No | EF Core generated SQL or query execution logs |
// In Program.cs or DbContext configuration:
optionsBuilder
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging() // shows parameter values (dev only!)
.EnableDetailedErrors();
Or use the Microsoft.EntityFrameworkCore log category:
{
"Logging": {
"LogLevel": {
"Microsoft.EntityFrameworkCore.Database.Command": "Information"
}
}
}
The #1 EF Core performance killer. Happens when loading related entities in a loop.
Before (N+1 — 1 query for orders + N queries for items):
var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
// Each access triggers a lazy-load query!
var items = order.Items.Count;
}
After (eager loading — 1 or 2 queries total):
// Option 1: Include (JOIN)
var orders = await db.Orders
.Include(o => o.Items)
.ToListAsync();
// Option 2: Split query (separate SQL, avoids cartesian explosion)
var orders = await db.Orders
.Include(o => o.Items)
.AsSplitQuery()
.ToListAsync();
// Option 3: Explicit projection (best - only fetches needed columns)
var orderSummaries = await db.Orders
.Select(o => new OrderSummary
{
OrderId = o.Id,
Total = o.Items.Sum(i => i.Price),
ItemCount = o.Items.Count
})
.ToListAsync();
When to use Split vs Single query:
| Scenario | Use |
|---|---|
| 1 level of Include | Single query (default) |
| Multiple Includes (Cartesian risk) | AsSplitQuery() |
| Include with large child collections | AsSplitQuery() |
| Need transaction consistency | Single query |
Change tracking overhead is significant. Disable it when you don't need to update entities:
// Per-query
var products = await db.Products
.AsNoTracking()
.Where(p => p.IsActive)
.ToListAsync();
// Global default for read-heavy apps
services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(connectionString)
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
Use AsNoTrackingWithIdentityResolution() when the query returns duplicate entities to avoid duplicated objects in memory.
// Define once as static
private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
EF.CompileAsyncQuery((AppDbContext db, int id) =>
db.Orders
.Include(o => o.Items)
.FirstOrDefault(o => o.Id == id));
// Use repeatedly — skips query compilation overhead
var order = await GetOrderById(db, orderId);
| Trap | Problem | Fix |
|---|---|---|
ToList() before Where() | Loads entire table into memory | Filter first: .Where().ToList() |
Count() to check existence | Scans all rows | Use .Any() instead |
.Select() after .Include() | Include is ignored with projection | Remove Include, use Select only |
string.Contains() in Where | May not translate, falls to client eval | Use EF.Functions.Like() for SQL LIKE |
Calling .ToList() inside Select() | Causes nested queries | Use projection with Select all the way |
When LINQ can't express it efficiently:
var results = await db.Orders
.FromSqlInterpolated($@"
SELECT o.* FROM Orders o
INNER JOIN (
SELECT OrderId, SUM(Price) as Total
FROM OrderItems
GROUP BY OrderId
HAVING SUM(Price) > {minTotal}
) t ON o.Id = t.OrderId")
.AsNoTracking()
.ToListAsync();
AsNoTracking()| Pitfall | Solution |
|---|---|
| Lazy loading silently creating N+1 | Remove Microsoft.EntityFrameworkCore.Proxies or disable lazy loading |
| Global query filters forgotten in perf analysis | Check HasQueryFilter in model config; use IgnoreQueryFilters() if needed |
DbContext kept alive too long | DbContext should be scoped (per-request); don't cache it |
| Batch updates fetching then saving | EF Core 7+: use ExecuteUpdateAsync / ExecuteDeleteAsync for bulk operations |
String interpolation in FromSqlRaw | SQL injection risk — use FromSqlInterpolated (parameterized) |