Entity Framework loses track of navigation properties after "over apply"

Question

I have a model like Person, which can live on Street, and Street is in the City, and City in it's turn is in the Country. Also there is some ActivityRecords for some Persons.

public class Country
{
    [Key]
    public int id { get; set; }
    public string name { get; set; }
}
public class City
{
    [Key]
    public int id { get; set; }
    public string name { get; set; }

    [ForeignKey("Country")]
    public int country_id { get; set; }
    public virtual Country Country { get; set; }
}
public class Street
{
    [Key]
    public int id { get; set; }
    public string name { get; set; }

    [ForeignKey("City")]
    public int city_id { get; set; }
    public virtual City City { get; set; }
}

public class Person
{
    [Key]
    public int id { get; set; }

    ForeignKey("Street")]
    public int? street_id { get; set; }
    public virtual Street Street { get; set; }

    //no connection to ActivityRecord[]
}

public class ActivityRecord
{
    [Key]
    public int id { get; set; }
    public string desc { get; set; }

    [ForeignKey("Person")]
    public int? person_id { get; set; }
    public virtual Person Person { get; set; }
}

I need to list a portion of persons info, including their addresses up to the country plus one of the activities if there's any (otherwise null). I have deliberately dropped all of the details and columns to simplify the example.

So, I have come to this in my code:

context.Persons.AsNoTracking().OrderBy(p => p.id).Skip(0).Take(10).GroupJoin(
    context.ActivityRecords,
    p => p.id,
    ar => ar.person_id,
    (p, ar) => new { p = p, ar = ar.FirstOrDefault() } //join 1 or 0 entries
).Select(result => new
{
    id = result.t.id,
    street = result.t.Street.name,
    city = result.t.Street.City.name,
    country = result.t.Street.City.Country.name
}).ToArray();

When I check EF-generated query, I see left-outer-joins on same tables multiple times (streets, cities):

SELECT 
    [Limit1].[id] AS [id], 
    [Extent3].[name] AS [name], 
    [Extent5].[name] AS [name1], 
    [Extent8].[name] AS [name2]
    FROM         (SELECT TOP (10) [Extent1].[id] AS [id], [Extent1].[street_id] AS [street_id]
        FROM ( SELECT [Extent1].[id] AS [id], [Extent1].[street_id] AS [street_id], row_number() OVER (ORDER BY [Extent1].[id] ASC) AS [row_number]
            FROM [dbo].[Persons] AS [Extent1]
        )  AS [Extent1]
        WHERE [Extent1].[row_number] > 0
        ORDER BY [Extent1].[id] ASC ) AS [Limit1]
    OUTER APPLY  (SELECT TOP (1) [Extent2].[id] AS [id]
        FROM [dbo].[ActivityRecords] AS [Extent2]
        WHERE [Limit1].[id] = [Extent2].[person_id] ) AS [Limit2]
    LEFT OUTER JOIN [dbo].[Streets] AS [Extent3]
            ON [Limit1].[street_id] = [Extent3].[id]
    LEFT OUTER JOIN [dbo].[Streets] AS [Extent4]  /* <-- Seriously? */
            ON [Limit1].[street_id] = [Extent4].[id]
    LEFT OUTER JOIN [dbo].[Cities] AS [Extent5]
            ON [Extent4].[city_id] = [Extent5].[id]
    LEFT OUTER JOIN [dbo].[Streets] AS [Extent6] /* <-- Come on! */
            ON [Limit1].[street_id] = [Extent6].[id]
    LEFT OUTER JOIN [dbo].[Cities] AS [Extent7] /* <-- Hey! */
            ON [Extent6].[city_id] = [Extent7].[id]
    LEFT OUTER JOIN [dbo].[Countries] AS [Extent8]
            ON [Extent7].[country_id] = [Extent8].[id]

When I just remove the single row connection in group join

...(p, ar) => new { p = p, ar = ar } ...

it works fine and joins each table once.

I tried to include tables before join, but with no success. The only thing that work is to make selection of all person-streets-cities before join, but the problem is that it loads all of that records before cutting TOP 10, and in reality I have there lots of heavy calculated columns (this query, when problem is still visible, is much simpler if you remove Skip() and Take(), but leave GroupJoin() with FirstOrDefault()).

So, my question is: how to inform EF not to include same tables multiple times, is there a workaround? Why it start loosing track of navigation properties chain after single row connection?

Answer 1

That was really a bug in EF query compilation, so upgrading from EF 6.1.1 to EF 6.1.2 fixed the problem.