“过度应用”后，实体框架失去对导航属性的跟踪

我有一个类似于人的模型，它可以居住在街道上，街道在城市中，而城市则在该国 . 还有一些人的活动记录 .

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; }
}

我需要列出一部分人员信息，包括他们在该国家的地址以及其中一项活动（如果有的话）（否则为空） . 我故意删除所有细节和列以简化示例 .

所以，我在我的代码中谈到了这个：

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();

当我检查EF生成的查询时，我在同一个表上多次看到左外连接（街道，城市）：

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]

当我只删除组连接中的单行连接时

...（p，ar）=> new {p = p，ar = ar} ...

它工作正常，并加入每个表一次 .

我尝试在加入之前包含表，但没有成功 . 唯一有用的是在加入之前选择所有人 - 街道 - 城市，但问题是它加载了所有的记录 before 切割TOP 10，实际上我有很多重的计算列（这个查询，当问题仍然可见时，如果删除Skip（）和Take（），则更简单，但将GroupJoin（）保留为FirstOrDefault（）） .

所以，我的问题是：如何通知EF不要多次包含相同的表，是否有解决方法？为什么在单行连接后开始失去对导航属性链的跟踪？