You can use table splitting to partition a DB record into two or more EF Core entities. You do this via shared primary key – the dependent entities’ PK is also a FK to the principal entity’s PK. Include only the commonly uses fields from the DB as properties in the principal entity to minimize the fields returned. When fields in the dependent entities are required, you can simply include them as you would any other navigation property.

public class Principal
{
    public int Id { get; set; }
    public string PrincipalProperty { get; set; }
}

public class Dependent
{
    public int Id { get; set; }
    public string DependentProperty { get; set; }
}

// In OnModelCreating method
modelBuilder.Entity<Principal>( u =>
{
    u.ToTable("TableNameHere");
    ...
});

modelBuilder.Entity<Dependent>( u =>
{
    // use same table name
    u.ToTable("TableNameHere")
        .HasOne<Principal>()
        .WithOne()
        // dependent PK is the FK
        .HasForeignKey<Dependent>( d => d.Id );
});

The first consideration would be the reason for fetching all parents at once. Whether displaying information or even to iterate through data to process you should consider paginating the data so that you are fetching only a limited number of top-level parent records at a time, via Skip and Take.

If you are leveraging Split Queries that will help, but you only need a few columns from Children1 and Children2 then you can also leverage projection to just fetch those details. For example:

var query = context.Parents
        .AsNoTracking()
        .Select(p => new
        {
            p.ParentId,
            p.Description,
            Child1 = d.Children1s
            .OrderByDescending(c => c.JoiningDate)
            .Select(c => new
            {
                c.ChildrenId,
                c.Description
                // other properties
                Children2 = c.Children2.Select(c2 => new 
                {
                   c2.Children2Id,
                   c2.Description,
                   // other properties
                }).ToList()
            }).FirstOrDefault()
        });

This assumes you want just the most recent Children1 record, and then select a set of properties from the related Children2 of that child. If you intend to send this data to a view then I would recommend populating a ViewModel class with the details rather than the anonymous types. (new { }}

Further investigation into performance cost would involve running the query through profiling tools for inspecting the execution plan and potential indexing improvements. I wouldn’t normally get too hung up on what the SQL looks like in terms of the joins it uses. EF is usually pretty good at composing effective queries but it can get hung up on parameter sniffing or stale execution plans. These usually show up when you execute the resulting SQL and it’s reasonably fast but EF is significantly slower to fetch the data.

The other key factor that can affect performance is mixing projections with entities. For instance you need to be careful if doing something like:

        .Select(p => new
        {
            p.ParentId,
            p.Description,
            Child1 = d.Children1s
            .OrderByDescending(c => c.JoiningDate)
            .Select(c => new
            {
                c.ChildrenId,
                c.Description
                // other properties
                Children2 = c.Children2
            }).FirstOrDefault()
        });

Here not only would the Child.Children2 fetch all columns from Children2, but if serializing and Child2 has navigation properties, if lazy loading is enabled then this can introduce extra Select N+1 costs.

Edit: I have checked with both AsSplitQuery() and with the UseQuerySplittingBehaviour and bot resulted in the projections being respected. Running the following test:

var data = context.Projects.Select(x => new
{
    x.ProjectId,
    x.Name,
    Jobs = x.Jobs.Select(j => new { j.Name })
}).AsSplitQuery()
.ToList();

the generated SQL was two statements:

SELECT [p].[ProjectId], [p].[Name]
FROM [Projects] AS [p]
ORDER BY [p].[ProjectId]

SELECT [j].[Name], [p].[ProjectId]
FROM [Projects] AS [p]
INNER JOIN [Jobs] AS [j] ON [p].[ProjectId] = [j].[ProjectId]
ORDER BY [p].[ProjectId]

It did not return all columns from either the parent (Project) or the child (Job).

The updated example you provided:

var query = context.Parent             
    .AsNoTracking()             
    .Select(d => new             
    {                 
        d.ParentId,
        d.Description,
        children = d.Children.Select(l => new
        {                     
            l.ChildrenId,
            l.Type
        })             
    });

… This statement does not yet execute a query as it lacks a .ToList() or .First() etc. so it wouldn’t result in SQL being generated yet.

To help narrow down what is going on, add a .First() to that test line and set a breakpoint on that line. Run a profiler to capture the SQL then run your code up to that line with a debugger, stopping on that breakpoint. Next, flush your profiler trace to clear the window and step over that single line to capture the SQL executed for that statement. You should see only two SQL Statements being run and that the projections are being preserved. Also be sure that this is while connected to a database and not a substitute like an in-memory database provider. If there are additional SQL statements appearing after you continue execution then you have other code/operations running that are ignoring your projection code and fetching entire entities.