Program.cs

//add NuGet package Npgsql.EntityFrameworkCore.PostgreSQL

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

var context = new ClientContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

//fill with random data
for (var i = 0; i < 100; i++)
{
    var author = new Author { Name = $"Author {i}" };
    context.Authors.Add(author);
    for (var j = 0; j < 10; j++)
    {
        var post = new Post { Title = $"Post {j} by Author {author.Name}", Content = $"Content {j} by Author {author.Name}", Author = author };
        context.Posts.Add(post);
    }
}

context.SaveChanges();

var query = context.Authors
    .AsSplitQuery()
    .OrderByDescending(x => x.AlwaysSameValue)
    .Skip(10)
    .Take(3)
    .Select(a => new AuthorDto
    {
        Id = a.Id,
        Name = a.Name,
        PostTitles = a.Posts.Select(p => p.Title).ToList()
    });

var authorDtos = await query.ToListAsync();

foreach (var authorDto in authorDtos)
{
    Console.WriteLine($"Author: {authorDto.Name}");
    foreach (var postTitle in authorDto.PostTitles)
    {
        Console.WriteLine($"  Post: {postTitle}");
    }
}

if (authorDtos.Count != 3)
{
    throw new InvalidOperationException("The query should return 3 authors");
}

if (authorDtos.Any(a => a.PostTitles.Count != 10))
{
    throw new InvalidOperationException("The query should return 10 posts for each author");
}

internal class AuthorDto
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public List<string> PostTitles { get; set; } = null!;
}

internal class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string AlwaysSameValue { get; set; } = "Always the same value";
    public List<Post> Posts { get; set; } = null!;
}

internal class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = null!;
    public string Content { get; set; } = null!;
    public Author Author { get; set; } = null!;
}

internal class ClientContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
        optionsBuilder
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .UseNpgsql(@$"host=localhost;port=5432;database=test;user id=postgres;password=postgres;Include Error Detail=true;");

    public DbSet<Post> Posts { get; set; } = null!;
    public DbSet<Author> Authors { get; set; } = null!;
}