EF Core N 1 Problem while getting 1-N data-CodePudding

We have 2 tables called Blogs and Comments. Blogs have N Comments. We have a method that takes a list of Blogs' Ids as a parameter. This method should return some basic information about the blogs and their comments. The method is defined as following;

public List<BlogDto> GetAllBlogs(List<long> blogIds)
{
    var query = from blogId in blogIds
                join blog in dbContext.Blogs.Include(blog => blog.Comments) on blogId equals blog.Id
                select new BlogDto()
                {
                    Id = blog.Id,
                    Name = blog.Name,
                    .
                    .
                    .
                    // Comments prop is List<CommentDto>
                    Comments = blog.Comments.Select(comment => new CommentDto
                                                               {
                                                                   Id = comment.Id,
                                                                   Content = comment.Content 
                                                               }).ToList()

                }

     return query.ToList();
}

My question is here why EF Core turns this into N 1 queries even though I have included Comments.

EF Core Version : 2.x.

.NET Version : 4.7.x

We are using WCF service so we could not upgrade .NET version and EF version.

CodePudding user response：

I believe its because you started with a join. You don't need a join for this query. My EF is a little rusty, but change your query to be something like this:

var query = for blog in dbContext.Blogs.Include(blog => blog.Comments)
            where blogIds.Contains(blogId)
            select new BlogDto()
            {
                Id = blog.Id,
                Name = blog.Name,
                .
                .
                .
                // Comments prop is List<CommentDto>
                Comments = blog.Comments.Select(comment => new CommentDto
                                                           {
                                                               Id = comment.Id,
                                                               Content = comment.Content 
                                                           }

            }

CodePudding user response：

As weird as it sounds, just for EF Core 2.1, you need to add ToList() in the subquery:

    var query = from blog in dbContext.Blogs
                where blogIds.Contains(blog.Id)
                select new BlogDto()
                {
                    Id = blog.Id,
                    Name = blog.Name,
                    Comments = blog.Comments.Select(comment => new CommentDto 
                               {
                                   Id = comment.Id,
                                   Content = comment.Content 
                               }).ToList()
                }

This was a known problem of EF Core 2.x. EF Core 1 and 2 lacked a lot of functionality. The missing features were covered up either by using client-side evaluation or executing multiple queries. EF Core 2.1 was the first that could handle GroupBy and mitigate the N 1 problem

The Optimization of correlated subqueries section explains that to avoid N 1 queries applications need to explicitly call ToList() in the correlated subquery.

The optimization requires buffering the results from the subquery, and we require that you modify the query to opt-in the new behavior.

As an example, the following query normally gets translated into one query for Customers, plus N (where "N" is the number of customers returned) separate queries for Orders:

var query = context.Customers.Select(
    c => c.Orders.Where(o => o.Amount  > 100).Select(o => o.Amount));

By including ToList() in the right place, you indicate that buffering is appropriate for the Orders, which enable the optimization:

var query = context.Customers.Select(
    c => c.Orders.Where(o => o.Amount  > 100)
                 .Select(o => o.Amount)
                 .ToList());

Upgrading to .NET 6

WCF isn't a blocker to migration any more. CoreWCF is an open-source port of WCF to .NET Core. Version 1 was released in April 2022. It's not just a recompilation though:

Usage in code is similar to WCF, but updated to use ASP.NET Core as the service host, and to work with .NET Core. This is the first major release of the project, and provides WCF functionality for .NET Core, .NET Framework, and .NET 5 .

The 1.0 release of CoreWCF is compatible with .NET Standard 2.0 so that it will work with:

.NET Framework 4.6.2 (and above)
.NET Core 3.1
.NET 5 & 6