Entity Framework - Include Take Top N for each parent [duplicate]

Question

I am implementing a forum where each post can have comments.

I want to load a batch of N posts so each post loads the first K comments.

I've got this code to start with, but it currently includes all the comments with each post. How do I only load first K comments with each post without having to make K calls to the database?

List<ForumPost> result = ctx
                            .ForumPosts
                            .Include("Comments") // <-- ??? How to take first K ???
                            .Where(i => i.Thread.ID == threadID)
                            .OrderByDescending(i => i.Date)
                            .Take(N).ToList();

Thanks!

Answer 1

(Currently, in EF you cannot filter or limit the Included related entities in any way). It's possible in EF Core since 5.0: Filtered include.

If you need to avoid the multiple queries, there are solutions, but they all involve working directly on the SQL side. You can use: TVF (table valued functions), stored procedures, views, or a simple query. In each case there are different solutions to map the results to your entities. And the mapped entities will be non-trackable, so that you cannot modify and write them back to the server (unless you add them explicitly to an existing DbContext)

You can read this article by Julie Lerman to see what I'm speaking about: Use Projections and a Repository to Fake a Filtered Eager Load.

You can vote for a related feature at Data User's voice. Look for "Allow filtering for Include extension method". Most probably, if this is implemented, it will also be possible to use .Take in this situation.

For more information on the evolution of this feature, take a look at EF Core github issue 1833: Support filtered Include. There you can find interesting things like:

• EF 6: EF Plus IncludeFilter
• EF 6: Dynamic global filters
• EF Core: Model-level entity type filters