CODEWITHSUNDEEP
c#entity-framework-core
Greg
Greg

Reputation: 1024

Avoid cartesian explosion without splitting all includes

I have entity Post that has one-to-many relations with Author and Comment. I would like to load all Posts and joined them with the first Author and all the Comments. The code with Include would look like this:

Post[] posts = ctx.Posts.Include(p => p.Authors.Take(1)).Include(p => p.Comments).ToArray();

There is a cartesian explosion issue with this query. If Post owns n Comments, Author and Comment are going to be repeated n times in the result set.

Solution #1

In EF Core 5.0, I could use a Split Query but then that would generate 3 queries when I would like to load Post with Author first then all Comments.

Solution #2

First, load Post with Author then iterate on the post to explicitly load their comments but that would generate n + 1 queries.

Post[] posts = ctx.Posts.Include(p => p.Authors.Take(1)).ToArray();
foreach (Post post in posts)
  ctx.Entry(post).Collection(p => p.Comments).Load();

Solution #3

First, load Post with Author then gather all post ids to generate a single query to load comments.

Dictionary<int, Post> postsById = ctx.Posts.Include(p => p.Authors.Take(1)).ToDictionnary(p => p.Id);
Comment[] comments = ctx.Comments.Where(c => postsById.ContainsKey(c.PostId)).ToArray();
foreach (Comment comment in comments)
  postsById[comment.PostId].Comments.Add(comment); // How to avoid re-adding comment?

This solution would generate only 2 queries without any duplicated data but how can I avoid the comments to be added again to the post? Is there a better way than the 3 proposed solutions?

Upvotes: 2

Views: 5750

Answers (2)

Greg
Greg

Reputation: 1024

I have found a way for the solution #2 to work with only two queries here: https://github.com/dotnet/efcore/issues/7350.

int postIds = new[] { 3, 4 };
Post[] posts = ctx.Posts
    .Include(p => p.Authors.Take(1))
    .Where(p => postIds.Contains(p.Id))
    .ToArray();

// This line automatically populates posts comments in the same DbContext.
ctx.Comments
    .Where(c => postIds.Contains(c.PostId))
    .Load();

Upvotes: 1

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

I would add another option. Since I'm the author of Eager Loading in linq2db. I'm pretty sure that it will run only two queries.

So just install this extension linq2db.EntityFrameworkCore (3.x version for EF Core 3.1.x and 5.x version for EF Core 5.x)

And try this query:

Post[] posts = ctx.Posts
   .Include(p => p.Author)
   .Include(p => p.Comments)
   .ToLinqToDB()
   .ToArray();

Also this approach should work with AutoMapper's ProjectTo and fully custom projection. I know that custom projection is not working with AsSplitQuery (since I tried it)

Upvotes: 3

Related Questions