EF Core 9: Reducing Database Roundtrip!

When it comes to query optimization, one of the considerations is reducing database roundtrip as we need to avoid unnecessary database calls, if possible.

It is easy to make mistakes when using EntityFramework because you're writing C# code, not SQL thus you may forget how EF will translate this code to SQL and sometimes it will split the code into multiple database queries to gather all the data you need.

One of the common examples is using Count() method which is used for getting the number of rows for a specific query and needs a separate database query.

Let's check some code, assume this is our query:

var dotnetPosts = context
    .Posts
    .Where(p => p.Title.Contains(".NET"));

var results = dotnetPosts
    .Where(p => p.Id > 2)
    .Select(p => new { Post = p, TotalCount = dotnetPosts.Count() })
    .Skip(2).Take(10)
    .ToArray();

For getting dotnetPosts.Count() result EF has to do a database call for getting count, so in EF 8 or earlier version this would be the generated SQL query:

-- First roundtrip
SELECT COUNT(*)
FROM [Posts] AS [p]
WHERE [p].[Title] LIKE N'%.NET%'

-- Second roundtrip
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] LIKE N'%.NET%' AND [p].[Id] > 2
ORDER BY (SELECT 1)
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

But EF 9 is trying to make uncorrelated subqueries inline so the IQueryable in the dotnetPosts is inlined, resulting in a single round trip:

SELECT [p].[Id], [p].[Archived], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Discriminator], [p].[PublishedOn], [p].[Title], [p].[PromoText], [p].[Metadata], (
    SELECT COUNT(*)
    FROM [Posts] AS [p0]
    WHERE [p0].[Title] LIKE N'%.NET%')
FROM [Posts] AS [p]
WHERE [p].[Title] LIKE N'%.NET%' AND [p].[Id] > 2
ORDER BY (SELECT 1)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

EF core 9 is trying to solve this issue for us and it is nice to see they care about this small correction for our Linq codes.

It can help to have better query performance as instead of calling the database twice we need only one!

As a tip, always check EF-generated SQL queries even if you're not an SQL expert still you can know how it executes your queries.