title: Entity Framework Core: using navigation properties without foreign key
body: <p>I have following object model:</p>
  
  <pre><code>public class SharingRelation:BaseEntity
  {
      public Guid? Code { get; set; }
      public string Value { get; set; }
  }
  
  public class SecondLevelShareEntity : BaseEntity
  {
      public string Name { get; set; }
      public Guid? SharingCode { get; set; }
      public List&lt;SharingRelation&gt; SharingRelations { get; set; }
  }
  </code></pre>
  
  <p>In my database (it may be poor db design but I need to answer this question for research), <code>SharingRelation</code> is some sort of dependent entity of <code>SecondLevelShareEntity</code> on <code>Code == SharingCode</code> values. I can have two entities of type <code>SecondLevelShareEntity</code> with same <code>SharingCode</code> value. So, for each of them I need to get all related <code>SharingRelation</code> objects depending on <code>Code</code> and <code>SharingCode</code> values. I can do it using SQL and join on this columns. But how can I do it using EF Core and navigation properties (I want to get all dependent entities using <code>Include()</code> for example)? When I configure my entities like this</p>
  
  <pre><code>public class SharingRelationEntityTypeConfiguration : BaseEntityTypeConfiguration&lt;SharingRelation&gt;
  {
      public override void Configure(EntityTypeBuilder&lt;SharingRelation&gt; builder)
      {
          base.Configure(builder);
          builder.HasOne&lt;SecondLevelShareEntity&gt;().WithMany(x =&gt; x.SharingRelations).HasForeignKey(x =&gt; x.Code)
              .HasPrincipalKey(x =&gt; x.SharingCode);
      }
  }
  </code></pre>
  
  <p>EF Core creates foreign key and marks it unique. I am obviously getting an error that that is impossible to have several <code>SecondLevelShareEntity</code> with the same <code>SharingCode</code></p>
  
  <blockquote>
    <p>System.InvalidOperationException : The instance of entity type 'SecondLevelShareEntity' cannot be tracked because another instance with the key value '{SharingCode: 8a4da9b3-4b8e-4c91-b0e3-e9135adb9c66}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.</p>
  </blockquote>
  
  <p>How can I avoid creation of foreign key, but keep using navigation properties (as far, as I see normal queries with navigations generate simple <code>JOIN</code> statements)</p>
  
  <p><strong>UPDATED</strong> I can provide real data in database. <code>SecondLevelShareEntity</code> table looks like this:</p>
  
  <pre><code>_id     Name                        SharingCode
  ----------------------------------------------------------------------
  1       "firstSecondLevelEnt"       "efcb1c96-0ef1-4bb3-a952-4a6511ab448b"
  2       "secondSecondLevelEnt"      "efcb1c96-0ef1-4bb3-a952-4a6511ab448b"
  </code></pre>
  
  <p>And <code>SharingRelation</code> table looks like this:</p>
  
  <pre><code>_id     Value                       Code
  ----------------------------------------------------------------------
  1       "firstSharingRelation"      "efcb1c96-0ef1-4bb3-a952-4a6511ab448b"
  2       "secondSharingRelation"     "efcb1c96-0ef1-4bb3-a952-4a6511ab448b"
  </code></pre>