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:
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:
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:
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:
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!
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 PartyId
equal 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; }
}
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; }
}