EFCore 2.2.2 filtering on related child entities

Some background: what Include doesn't include

The Include method is a bitch. The most commonly used overload accepting an expression parameter (existing since Entity Framework 4.1 if memory serves) looks suspiciously like those versatile LINQ methods doing all kinds of wonderful stuff with the most wild expressions we feed them.

In reality it --and ThenInclude-- aren't LINQ methods. (Then)Include is nothing but a sturdy old method refusing to do anything outside of its single task: passing a navigation property name to the EF query engine, instructing it to eagerly load a collection or a reference with the root entity. Think of it as the strong-typed version of Include("PropertyName"). Its only purpose is enabling compile-type type checking.

Which means: you can only use expressions that represent a navigation property's name: .ThenInclude(p => p.Posts) is OK. Anything added to it, not.

The expression parameter makes people expect it to do much more than that. And why not? The code compiles alright, why shouldn't it run? But no. Common disappointments are that Include can't be filtered or sorted. Efforts to combine Include and Skip/Take are less common, but equally understandable.

As for me, the EF team might as well consider ditching this overload of (Then)Include altogether now we have the nameof keyword that does a similar thing and could be used likewise. That would put an end to all confusion, to an endless influx of Stack Overflow questions, and to the never-ending requests for change that, so far, have never even been road-mapped. [Also understandable, but that's way beyond the scope of this question].

The issue

In the mean time you still have your issue. The reason that Skip/Take isn't often combined with Include is that it's hard to imagine what it should do. Just suppose you had two nested Includes in there, or ThenInclude(x => x.Topics.Skip().Take().ThenInclude(p => p.Posts)) which, if supported, should all have been equally legal. In fact, only paging on the root entity of a query is well-defined.

So you can only get paged posts by querying posts. For example like so:

CoDBContext.Posts
    .Where(p => p.Topic.Forum.ComlexId = Id)
    .Skip((pageIndex-1)*pageSize).Take(pageSize))

This is also a much leaner query than the one with Includes.

If you need more context information, for example on the Complex, you could consider querying the Complex in one call, keep it on the page (if this is a SPA) and query the posts in subsequent ajax calls.