Hallelujah. With current versions of Entity Framework, when developers add a mix of parameters and specific values to their query like this:
C#
12345 | async Task<List<Post>> GetPosts(int id) => await context.Posts .Where( e => e.Title == ".NET Blog" && e.Id == id) .ToListAsync(); |
See how part of the filter is hard-coded (“.NET Blog”) while the other part of the filter is dynamically generated, an ID the user is looking for? That causes Entity Framework to generate a query that is partially parameterized:
Transact-SQL
12345 | info: 2/5/2024 15:43:13.789 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[@__id_0='1'], CommandType='Text', CommandTimeout='30'] SELECT [p].[Id], [p].[Archived], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Discriminator], [p].[PublishedOn], [p].[Title], [p].[PromoText], [p].[Metadata] FROM [Posts] AS [p] WHERE [p].[Title] = N'.NET Blog' AND [p].[Id] = @__id_0 |
This is the worst of both worlds for SQL Server. If the query was fully parameterized, it’d get plan reuse. If the query wasn’t parameterized at all, we could turn on Forced Parameterization and get plan reuse. However, Forced Parameterization won’t do anything for the above query because SQL Server looks at it and says, “That query’s already parameterized – see, it has a parameter for @__id_0 right there! I’ll just skip it.”
In Microsoft’s example above, ‘.NET Blog’ is a hard-coded string, but the situation is much worse when that is dynamically generated. For every variation of the parameter, SQL Server sees a “new” query coming in. End result: increased CPU to compile “new” query plans, unpredictable plans, plan cache bloat, problems with monitoring tools and Query Store, and more.
Good news! The What’s New in Entity Framework 9 rundown shows a new EF.Parameter method to force parameterization:
Transact-SQL
12345 | async Task<List<Post>> GetPostsForceParameter(int id) => await context.Posts .Where( e => e.Title == EF.Parameter(".NET Blog") && e.Id == id) .ToListAsync(); |
Which uses a parameter now instead of a hard-coded string:
Transact-SQL
12345 | info: 2/5/2024 15:43:13.803 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[@__p_0='.NET Blog' (Size = 4000), @__id_1='1'], CommandType='Text', CommandTimeout='30'] SELECT [p].[Id], [p].[Archived], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Discriminator], [p].[PublishedOn], [p].[Title], [p].[PromoText], [p].[Metadata] FROM [Posts] AS [p] WHERE [p].[Title] = @__p_0 AND [p].[Id] = @__id_1 |
Yay! Lower CPU for plan compilation, more reused plans, less memory consumed by redundant plans, and better monitoring tools.
It is a bummer that we have to wait until an estimated November 2024 for EF9 (according to that same What’s New doc), and that developers will have to touch code in order to fix it. I can’t really complain about that, though, because I’m just happy that Microsoft is adding it. EF’s query generation keeps gradually getting better, and that’s awesome.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.