EF Core: how to share foreign key properties between several zero/one-to-many relationships

c# entity-framework entity-framework-core inheritance

Question

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.

First, here are my POCO classes:

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<Commission> Commissions { get; set; }
}

public class Carrier : Party
{
    public string CarrierCode { get; set; }
    public ICollection<Commission> Commissions { get; set; }
}

public class Principal : Party
{
    public string Website { get; set; }
    public string DistrictCode { get; set; }
}

And my context class just in case:

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

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

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.

The resulting database is as follows: Resulting EF Database

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:

  • Parties to Commissions_AgencyPartyId
  • Parties to Commissions_CarrierPartyId

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?

EDIT

Using Dmitry's suggestion and using the [InverseProperty] attribute, I ended up with the following database design which is not the desired output: Resulting Database from InverseProperty

It actually made a third field (PartyId1). So I started looking at the EF documentation on relationships 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:

Resulting Database from ForeignKey attribute

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

Here's the populating 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<Commission>
        {
            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<Commission>
        {
            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;
        }

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'."

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!

1
1
1/16/2017 11:03:32 PM

Accepted Answer

If you configure both relationships to use the same property as the foreign key you still have two relationships. So when adding a Commission with PartyIdequal to 1 EF interprets it as being related to an Agency with PartyId equal to 1 and a Carrier with PartyId equal to 1, obviously this would be impossible.

What you would need to do is create a relationship between Commission and Party, however this would mean that the Commissions navigation property would need to be moved to Party as well. But you can still hide it on Principal and other derived classes by making it protected and only exposing it on Agency and Carrier:

public class PartyContext : DbContext
{
    public PartyContext(DbContextOptions options)
        : base(options)
    {
    }

    public DbSet<Agency> Agencies { get; set; }
    public DbSet<Carrier> Carriers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Party>().HasMany(typeof(Commission), "Commissions").WithOne();
    }
}

public class Party
{
    public int PartyId { get; set; }
    protected ICollection<Commission> Commissions { get; set; }
}

public class Agency : Party
{
    public new ICollection<Commission> Commissions
    {
        get { return base.Commissions; }
        set { base.Commissions = value; }
    }
}

public class Carrier : Party
{
    public new ICollection<Commission> Commissions
    {
        get { return base.Commissions; }
        set { base.Commissions = value; }
    }
}

public class Commission
{
    public int Id { get; set; }
}
4
1/16/2017 10:11:39 PM

Popular Answer

Try this:

public class Commission
{
    public int Id { get; set; }
    public string Description { get; set; }
    public double Rate { get; set; }

    public Party Party { get; set; } // <-- "parent" party link here
}

public class Agency : Party
{
    public string AgencyCode { get; set; }

    [InverseProperty("Party")] // <-- link this collection with Party.Party
    public ICollection<Commission> Commissions { get; set; }
}

public class Carrier : Party
{
    public string CarrierCode { get; set; }

    [InverseProperty("Party")] // <-- link this collection with Party.Party
    public ICollection<Commission> Commissions { get; set; }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow