EF Core: how to share foreign key properties between several zero/one-to-many relationships
<p>So I am dipping into EF Core a little bit and experimenting with inheritance and the TPH pattern (I have no prior experience with this).  The resulting database that EF creates is not what I expected and I am wondering if it's possible to get the result I am looking for using fluent-api, or if I am just missing the point altogether.</p>

<p>First, here are my POCO classes:</p>

<pre><code>public class Commission
{
    public int Id { get; set; }
    public string Description { get; set; }
    public double Rate { get; set; }
}

public class Party
{
    public int PartyId { get; set; }
    public string Name { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
}

public class Agency : Party
{
    public string AgencyCode { get; set; }
    public ICollection&lt;Commission&gt; Commissions { get; set; }
}

public class Carrier : Party
{
    public string CarrierCode { get; set; }
    public ICollection&lt;Commission&gt; Commissions { get; set; }
}

public class Principal : Party
{
    public string Website { get; set; }
    public string DistrictCode { get; set; }
}
</code></pre>

<p>And my context class just in case:</p>

<pre><code>public class PartyContext : DbContext
{
    public DbSet&lt;Agency&gt; Agencies { get; set; }
    public DbSet&lt;Carrier&gt; Carriers { get; set; }
    public DbSet&lt;Party&gt; Parties { get; set; }
    public DbSet&lt;Commission&gt; Commissions { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=LAPTOP-PC\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;");
    }
}
</code></pre>

<p>Basically, Agency, Carrier, and Principal all inherit from Party, so they should have all the same properties as Party.  Agency and Carrier have additional specific properties, and should have a zero or one-to-many relationship with Commissions.  Additionally, Principal has a couple specific properties, but has no relationship to commissions.</p>

<p>The resulting database is as follows:
<a href="https://i.stack.imgur.com/kJPUY.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kJPUY.jpg" alt="Resulting EF Database"></a></p>

<p>I have no issue with the output of the Parties table itself, and understand what the discriminator field is, however I do not understand the two foreign-key relationships it created:</p>

<ul>
<li>Parties to Commissions_AgencyPartyId</li>
<li>Parties to Commissions_CarrierPartyId</li>
</ul>

<p><em>My question is why can't I just have one foreign key relationship from Parties to Commissions_PartyId on the back-end?  And if I can, how can I tell EF to create it that way?</em></p>

<p><strong>EDIT</strong></p>

<p>Using Dmitry's suggestion and using the [InverseProperty] attribute, I ended up with the following database design which is not the desired output:
<a href="https://i.stack.imgur.com/ZYtTx.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZYtTx.jpg" alt="Resulting Database from InverseProperty"></a></p>

<p>It actually made a third field (PartyId1).  So I started looking at the EF <a href="https://docs.microsoft.com/en-us/ef/core/modeling/relationships" rel="nofollow noreferrer">documentation on relationships</a> again and started playing with different annotations.  Using the [ForeignKey("PartyId")] attribute gave me some hope after it produced the design that I am expecting:</p>

<p><a href="https://i.stack.imgur.com/uN2m3.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uN2m3.jpg" alt="Resulting Database from ForeignKey attribute"></a></p>

<p>However, this too had some unexpected effects.  After trying to populate the database with an Agency and a Carrier, I receive an exception.</p>

<p>Here's the populating code:</p>

<pre><code>PartyContext _context = new PartyContext();
        // Add an Agency
        var agencyCommission1 = new Commission
        {
            Description = "Contract",
            Rate = 0.075
        };

        var agencyCcommission2 = new Commission
        {
            Description = "Hauling",
            Rate = 0.10
        };

        var agencyCommissionList = new List&lt;Commission&gt;
        {
            agencyCommission1, agencyCcommission2
        };

        var agency = new Agency
        {
            Name = "Agency International",
            Address1 = "12345 Main Street",
            Address2 = "Suite 100",
            City = "Chicago",
            State = "IL",
            Zip = "31202",
            AgencyCode = "AI",
            Commissions = agencyCommissionList
        };

        // Add Carrier
        var carrierCommission1 = new Commission
        {
            Description = "Coal",
            Rate = 0.15
        };

        var carrierCommission2 = new Commission
        {
            Description = "Mining",
            Rate = 0.115
        };

        var carrierCommissionList = new List&lt;Commission&gt;
        {
            carrierCommission1, carrierCommission2
        };

        var carrier = new Carrier
        {
            Name = "Carrier International",
            Address1 = "54321 Main Street",
            Address2 = "Suite 300",
            City = "Cleveland",
            State = "OH",
            Zip = "44115",
            CarrierCode = "CI",
            Commissions = carrierCommissionList
        };

        _context.Agencies.Add(agency);
        _context.Carriers.Add(carrier);

        try
        {
            _context.SaveChanges();
        }
        catch(Exception ex)
        {
            return;
        }
</code></pre>

<p>The exception when adding the Agency is "Unable to cast object of type 'EFTest.Agency' to type 'EFTest.Carrier'." and the exception when trying to add the Carrier is "Unable to cast object of type 'EFTest.Carrier' to type 'EFTest.Agency'."</p>

<p>I will add that when using the original EF design, the program does work as expected, however the additional fields and foreign keys are making my OCD a little crazy :)  Any more thoughts are welcome!</p>