ThenInclude ignored and re-joined with all columns

[justdmitry]

Model classes (simplified)

public class Worklog {
    public Guid Id { get; set; }
    public Guid ContactFieldValueId { get; set; }
    [ForeignKey("ContactFieldValueId")]
    public virtual ContactFieldValue ContactFieldValue { get; set; }
    // some other columns
}

public class ContactFieldValue {
    public Guid Id { get; set; }
    public Guid FieldValueId { get; set; }
    [ForeignKey("FieldValueId")]
    public virtual FieldValue FieldValue { get; set; }
    // some other columns
}

public class FieldValue {
    public Guid Id { get; set; }
    public string Value { get; set; }
    // some other columns
}

And I making a query:

var worklogs = await db.Worklogs
    .Where(x => worklogIds.Contains(x.Id)) // it's List<Guid> with ~1000 values
    .Include(x => x.ContactFieldValue)
    .ThenInclude(cf => cf.FieldValue)
    .Select(x => new
    {
        x.Id, // and some other fields from Worklogs
        ContactFieldValueFieldValue = x.ContactFieldValue.FieldValue.Value
    })
    .OrderBy(x => x.Time)
    .ThenBy(x => x.Id)
    .ToListAsync();

So, I'm selecting some fields from first (Worklog) and one from third (FieldValue) entities.

The issue

I have two problems:

1. I see warning in logs: The Include operation for navigation: 'x.ContactFieldValue.FieldValue' was ignored because the target navigation is not reachable in the final query results. - this is not true, because I'm selecting Value from it.

2. Generated SQL SELECT include all columns from ContactFieldValue and all from FiledValue - despite I requested only one:

SELECT 
    [x].[Id], 
    [x].[Comment], [x].[ContactFieldValueId], [x].[IssueId], [x].[ScriptVersionId], [x].[Time], [x].[UserId], 
    [x.ContactFieldValue].[Id], 
    [x.ContactFieldValue].[FieldValueId], 
    // all columns from [x.ContactFieldValue] - why???
    [x.ContactFieldValue.FieldValue].[Id], 
    [x.ContactFieldValue.FieldValue].[Value],
    // all other columns from FieldValue - why???
      FROM [IssueWorklogs] AS [x]
      LEFT JOIN [ContactFieldValues] AS [x.ContactFieldValue] ON [x].[ContactFieldValueId] = [x.ContactFieldValue].[Id]
      LEFT JOIN [FieldValues] AS [x.ContactFieldValue.FieldValue] ON [x.ContactFieldValue].[FieldValueId] = [x.ContactFieldValue.FieldValue].[Id]
      WHERE [x].[Id] IN ('e78183fb-f089-4f99-aaf3-0252679000ae', ...)

Further technical details

EF Core version: Microsoft.EntityFrameworkCore.SqlServer 1.1.0
Operating system: Win 8,
Framework: net461

[maumar]

The Included are not needed in this case, they would only be useful if you projected Worklog entities themselves. If you access navigation as part of a query (in filter, projection etc) EF will automatically add all the joins it needs to perform the query.

[maumar]

Including all columns of entities is due to LEFT JOIN, it's a known issue tracked here: #6647

[justdmitry]

Got it.
Yes, this is duplicate of #6647