Entity Framework - enable Take Top N for each parent

Question

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

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

I have this code to start with, but it currently includes all the comments with each post. How to load only the first K comments with each message without making K calls in 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 restrict related objects in any way Included.

, , SQL. : TVF (, ), , . . , ( DbContext)

, , : .

. " Include". , , .Take.

EF Core github issue 1833: . , :

• EF 6: EF Plus IncludeFilter
• EF 6:
• EF Core:

Answer 2

, Include . . , foreach , ForumPost:

List<ForumPost> result = ctx
                        .ForumPosts                            
                        .Where(i => i.Thread.ID == threadID)
                        .OrderByDescending(i => i.Date)
                        .Take(N).ToList();
//start loading top K comments from each post
foreach(var post in result){
    ctx.Entry<ForumPost>(post).Collection("Comments")
                              .Query()
                              .Take(k);//assume k is a constant
}

: ( db)

, , --, , , EF6. -- , , , reset all MemberEntries Unchanged, , .

ctx.Configuration.LazyLoadingEnabled = false;
var stateManager = ((IObjectContextAdapter)ctx).ObjectContext.ObjectStateManager;
var result = ctx.ForumPosts.Where(i => i.Thread.ID == threadID)
                .Select(e => new { e, Comments = e.Comments.Take(k) })
                .AsEnumerable()
                .Select(e => {
                   //set the Comments manually
                   e.e.Comments = e.Comments;
                   //Reset RelationshipEntries' state
                   foreach(var c in e.Comments) {
                      stateManager.ChangeRelationshipState(e.e, c, o => o.Comments, 
                                                           EntityState.Unchanged);
                   }
                   return e.e;
                }).ToList();

Local. db ( AsEnumerable()).