title: EntityFramework Core relation to composite key
body: <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>