optimizing-ef-core-queries

Name: optimizing-ef-core-queries
Author: managedcode

461stars35forks

How to install optimizing-ef-core-queries?

managedcode/dotnet-skills/optimizing-ef-core-queries

$npx -y skills add managedcode/dotnet-skills --skill optimizing-ef-core-queries

Installs into the current project.

Summary

Optimize Entity Framework Core queries by fixing N+1 problems, choosing correct tracking modes, using compiled queries, and avoiding common performance traps. Use when EF Core queries are slow, generating excessive SQL, or causing high database load.

Files · 1

View on GitHub

SKILL.md

1	# Optimizing EF Core Queries
2
3	## When to Use
4
5	- EF Core queries are slow or generating too many SQL statements
6	- Database CPU/IO is high due to ORM inefficiency
7	- N+1 query patterns are detected in logs
8	- Large result sets cause memory pressure
9
10	## When Not to Use
11
12	- The user is using Dapper or raw ADO.NET (not EF Core)
13	- The performance issue is database-side (missing indexes, bad schema)
14	- The user is building a new data access layer from scratch
15
16	## Inputs
17
18	\| Input \| Required \| Description \|
19	\|-------\|----------\|-------------\|
20	\| Slow EF Core queries \| Yes \| The LINQ queries or DbContext usage to optimize \|
21	\| SQL output or logs \| No \| EF Core generated SQL or query execution logs \|
22
23	## Workflow
24
25	### Step 1: Enable query logging to see the actual SQL
26
27	```csharp
28	// In Program.cs or DbContext configuration:
29	optionsBuilder
30	.UseSqlServer(connectionString)
31	.LogTo(Console.WriteLine, LogLevel.Information)
32	.EnableSensitiveDataLogging() // shows parameter values (dev only!)
33	.EnableDetailedErrors();
34	```
35
36	Or use the `Microsoft.EntityFrameworkCore` log category:
37
38	```json
39	{
40	"Logging": {
41	"LogLevel": {
42	"Microsoft.EntityFrameworkCore.Database.Command": "Information"
43	}
44	}
45	}
46	```
47
48	### Step 2: Fix N+1 query patterns
49
50	The #1 EF Core performance killer. Happens when loading related entities in a loop.
51
52	Before (N+1 — 1 query for orders + N queries for items):
53	```csharp
54	var orders = await db.Orders.ToListAsync();
55	foreach (var order in orders)
56	{
57	// Each access triggers a lazy-load query!
58	var items = order.Items.Count;
59	}
60	```
61
62	After (eager loading — 1 or 2 queries total):
63	```csharp
64	// Option 1: Include (JOIN)
65	var orders = await db.Orders
66	.Include(o => o.Items)
67	.ToListAsync();
68
69	// Option 2: Split query (separate SQL, avoids cartesian explosion)
70	var orders = await db.Orders
71	.Include(o => o.Items)
72	.AsSplitQuery()
73	.ToListAsync();
74
75	// Option 3: Explicit projection (best - only fetches needed columns)
76	var orderSummaries = await db.Orders
77	.Select(o => new OrderSummary
78	{
79	OrderId = o.Id,
80	Total = o.Items.Sum(i => i.Price),
81	ItemCount = o.Items.Count
82	})
83	.ToListAsync();
84	```
85
86	When to use Split vs Single query:
87
88	\| Scenario \| Use \|
89	\|----------\|-----\|
90	\| 1 level of Include \| Single query (default) \|
91	\| Multiple Includes (Cartesian risk) \| `AsSplitQuery()` \|
92	\| Include with large child collections \| `AsSplitQuery()` \|
93	\| Need transaction consistency \| Single query \|
94
95	### Step 3: Use NoTracking for read-only queries
96
97	Change tracking overhead is significant. Disable it when you don't need to update entities:
98
99	```csharp
100	// Per-query
101	var products = await db.Products
102	.AsNoTracking()
103	.Where(p => p.IsActive)
104	.ToListAsync();
105
106	// Global default for read-heavy apps
107	services.AddDbContext<AppDbContext>(options =>
108	options.UseSqlServer(connectionString)
109	.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
110	```
111
112	Use `AsNoTrackingWithIdentityResolution()` when the query returns duplicate entities to avoid duplicated objects in memory.
113
114	### Step 4: Use compiled queries for hot paths
115
116	```csharp
117	// Define once as static
118	private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
119	EF.CompileAsyncQuery((AppDbContext db, int id) =>
120	db.Orders
121	.Include(o => o.Items)
122	.FirstOrDefault(o => o.Id == id));
123
124	// Use repeatedly — skips query compilation overhead
125	var order = await GetOrderById(db, orderId);
126	```
127
128	### Step 5: Avoid common query traps
129
130	\| Trap \| Problem \| Fix \|
131	\|------\|---------\|-----\|
132	\| `ToList()` before `Where()` \| Loads entire table into memory \| Filter first: `.Where().ToList()` \|
133	\| `Count()` to check existence \| Scans all rows \| Use `.Any()` instead \|
134	\| `.Select()` after `.Include()` \| Include is ignored with projection \| Remove Include, use Select only \|
135	\| `string.Contains()` in Where \| May not translate, falls to client eval \| Use `EF.Functions.Like()` for SQL LIKE \|
136	\| Calling `.ToList()` inside `Select()` \| Causes nested queries \| Use projection with `Select` all the way \|
137
138	### Step 6: Use raw SQL or FromSql for complex queries
139
140	When LINQ can't express it efficiently:
141
142	```csharp
143	var results = await db.Orders
144	.FromSqlInterpolated($@"
145	SELECT o.* FROM Orders o
146	INNER JOIN (
147	SELECT OrderId, SUM(Price) as Total
148	FROM OrderItems
149	GROUP BY OrderId
150	HAVING SUM(Price) > {minTotal}
151	) t ON o.Id = t.OrderId")
152	.AsNoTracking()
153	.ToListAsync();
154	```
155
156	## Validation
157
158	- [ ] SQL logging shows expected number of queries (no N+1)
159	- [ ] Read-only queries use `AsNoTracking()`
160	- [ ] Hot-path q

People who liked this also viewed

6 picks

Type

optimizing-ef-core-queries

461stars35forks

View on GitHub

How to install optimizing-ef-core-queries?

managedcode/dotnet-skills/optimizing-ef-core-queries

$npx -y skills add managedcode/dotnet-skills --skill optimizing-ef-core-queries

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Optimizing EF Core Queries
2
3	## When to Use
4
5	- EF Core queries are slow or generating too many SQL statements
6	- Database CPU/IO is high due to ORM inefficiency
7	- N+1 query patterns are detected in logs
8	- Large result sets cause memory pressure
9
10	## When Not to Use
11
12	- The user is using Dapper or raw ADO.NET (not EF Core)
13	- The performance issue is database-side (missing indexes, bad schema)
14	- The user is building a new data access layer from scratch
15
16	## Inputs
17
18	\| Input \| Required \| Description \|
19	\|-------\|----------\|-------------\|
20	\| Slow EF Core queries \| Yes \| The LINQ queries or DbContext usage to optimize \|
21	\| SQL output or logs \| No \| EF Core generated SQL or query execution logs \|
22
23	## Workflow
24
25	### Step 1: Enable query logging to see the actual SQL
26
27	```csharp
28	// In Program.cs or DbContext configuration:
29	optionsBuilder
30	.UseSqlServer(connectionString)
31	.LogTo(Console.WriteLine, LogLevel.Information)
32	.EnableSensitiveDataLogging() // shows parameter values (dev only!)
33	.EnableDetailedErrors();
34	```
35
36	Or use the `Microsoft.EntityFrameworkCore` log category:
37
38	```json
39	{
40	"Logging": {
41	"LogLevel": {
42	"Microsoft.EntityFrameworkCore.Database.Command": "Information"
43	}
44	}
45	}
46	```
47
48	### Step 2: Fix N+1 query patterns
49
50	The #1 EF Core performance killer. Happens when loading related entities in a loop.
51
52	Before (N+1 — 1 query for orders + N queries for items):
53	```csharp
54	var orders = await db.Orders.ToListAsync();
55	foreach (var order in orders)
56	{
57	// Each access triggers a lazy-load query!
58	var items = order.Items.Count;
59	}
60	```
61
62	After (eager loading — 1 or 2 queries total):
63	```csharp
64	// Option 1: Include (JOIN)
65	var orders = await db.Orders
66	.Include(o => o.Items)
67	.ToListAsync();
68
69	// Option 2: Split query (separate SQL, avoids cartesian explosion)
70	var orders = await db.Orders
71	.Include(o => o.Items)
72	.AsSplitQuery()
73	.ToListAsync();
74
75	// Option 3: Explicit projection (best - only fetches needed columns)
76	var orderSummaries = await db.Orders
77	.Select(o => new OrderSummary
78	{
79	OrderId = o.Id,
80	Total = o.Items.Sum(i => i.Price),
81	ItemCount = o.Items.Count
82	})
83	.ToListAsync();
84	```
85
86	When to use Split vs Single query:
87
88	\| Scenario \| Use \|
89	\|----------\|-----\|
90	\| 1 level of Include \| Single query (default) \|
91	\| Multiple Includes (Cartesian risk) \| `AsSplitQuery()` \|
92	\| Include with large child collections \| `AsSplitQuery()` \|
93	\| Need transaction consistency \| Single query \|
94
95	### Step 3: Use NoTracking for read-only queries
96
97	Change tracking overhead is significant. Disable it when you don't need to update entities:
98
99	```csharp
100	// Per-query
101	var products = await db.Products
102	.AsNoTracking()
103	.Where(p => p.IsActive)
104	.ToListAsync();
105
106	// Global default for read-heavy apps
107	services.AddDbContext<AppDbContext>(options =>
108	options.UseSqlServer(connectionString)
109	.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
110	```
111
112	Use `AsNoTrackingWithIdentityResolution()` when the query returns duplicate entities to avoid duplicated objects in memory.
113
114	### Step 4: Use compiled queries for hot paths
115
116	```csharp
117	// Define once as static
118	private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
119	EF.CompileAsyncQuery((AppDbContext db, int id) =>
120	db.Orders
121	.Include(o => o.Items)
122	.FirstOrDefault(o => o.Id == id));
123
124	// Use repeatedly — skips query compilation overhead
125	var order = await GetOrderById(db, orderId);
126	```
127
128	### Step 5: Avoid common query traps
129
130	\| Trap \| Problem \| Fix \|
131	\|------\|---------\|-----\|
132	\| `ToList()` before `Where()` \| Loads entire table into memory \| Filter first: `.Where().ToList()` \|
133	\| `Count()` to check existence \| Scans all rows \| Use `.Any()` instead \|
134	\| `.Select()` after `.Include()` \| Include is ignored with projection \| Remove Include, use Select only \|
135	\| `string.Contains()` in Where \| May not translate, falls to client eval \| Use `EF.Functions.Like()` for SQL LIKE \|
136	\| Calling `.ToList()` inside `Select()` \| Causes nested queries \| Use projection with `Select` all the way \|
137
138	### Step 6: Use raw SQL or FromSql for complex queries
139
140	When LINQ can't express it efficiently:
141
142	```csharp
143	var results = await db.Orders
144	.FromSqlInterpolated($@"
145	SELECT o.* FROM Orders o
146	INNER JOIN (
147	SELECT OrderId, SUM(Price) as Total
148	FROM OrderItems
149	GROUP BY OrderId
150	HAVING SUM(Price) > {minTotal}
151	) t ON o.Id = t.OrderId")
152	.AsNoTracking()
153	.ToListAsync();
154	```
155
156	## Validation
157
158	- [ ] SQL logging shows expected number of queries (no N+1)
159	- [ ] Read-only queries use `AsNoTracking()`
160	- [ ] Hot-path q

People who liked this also viewed

6 picks

Type