EntityFramework Core relation to composite key
<p>Consider the following database tables. Unfortunately the tables cannot be altered in any way.</p>

<p><a href="https://i.stack.imgur.com/TtW1q.png" rel="noreferrer"><img src="https://i.stack.imgur.com/TtW1q.png" alt="Database schema"></a></p>

<p><code>Houses</code> has an auto-increment ID field named <code>Id</code>, a string field named <code>Name</code> and an integer field named <code>AreaId</code>. The latter is not a foreign key to the <code>Areas</code> table.</p>

<p><code>Areas</code> has a composite key consisting of <code>AreaId</code>, <code>CountryId</code> and <code>LangId</code>. An Area with the same <code>AreaId</code> can exist but with different <code>CountryId</code> and <code>LangId</code>. E.g.: There can be two rows with the same <code>AreaId</code> but different <code>LangId</code>.</p>

<p>NOTE: Why does a <code>House</code> have multiple <code>Area</code>s? A <code>House</code> doesn't have multiple <code>Area's, it only has one</code>Area<code>. The</code>Area`s table has a composite key, meaning that a specific row will have multiple translations. E.g.: Area ID 5 might have LangId 5 for English and LangId 3 for Spanish.</p>

<p>The two tables are described by the following two C# classes.</p>

<pre><code>public class House
{
    public int Id { get; set; }

    [MaxLength(80)]
    public string Name { get; set; }

    public int? AreaId { get; set; }

    [ForeignKey("AreaId")]
    public List&lt;Area&gt; Areas { get; set; }
}

public class Area
{
    public int AreaId { get; set; }

    public int CountryId { get; set; }

    public string LangId { get; set; }

    public string Name { get; set; }
}
</code></pre>

<p>The composite key is defined in the context, exactly as stated in the docs.</p>

<pre><code>protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity&lt;Area&gt;()
        .HasKey(a =&gt; new { a.AreaId, a.CountryId, a.LangId });
}
</code></pre>

<p>For example let's get a list of all the Houses in the database, including their respective areas.</p>

<pre><code>_context.Houses.Include(h =&gt; h.Areas).ToList();
</code></pre>

<p>The following SQL is generated in the output window and the resulting List contains Houses incorrectly matched with the Areas.</p>

<pre><code>SELECT [a].[AreaId], [a].[CountryId], [a].[LangId], [a].[Name]
FROM [Areas] AS [a]
WHERE EXISTS (
    SELECT 1
    FROM [Houses] AS [h]
    WHERE [a].[AreaId] = [h].[Id])
ORDER BY [a].[Id]
</code></pre>

<p>As you can see, EntityFramework relates <code>[a].[AreaId]</code> with <code>[h].[Id]</code> and not <code>[h].[AreaId]</code>. How can I express this relationship in EF?</p>