c# - multiple added entities may have the same primary key on database seed

Question

assuming I have the following model structures for an asp.net mvc 5 app using entity framework 6

class Athlete {
 int AthleteID {get; set;}
 List<YearsAsAthlete> YearsAsAthlete {get;set;}
}

class YearsAsAthlete {
 int YearsAsAthleteID {get;set;}
 int AthleteID {get;set;}
 [ForeignKey("AthleteID")]
 Athlete Athlete {get;set;}
 List<ContractRevenue> ContractRevenue {get;set;}
 List<AdvertisementRevenue> AdvertisementRevenue {get;set;}
}

class ContractRevenue {
 int ContractRevenueID {get;set;}
 int YearsAsAthleteID {get;set;}
 [ForeignKey("YearsAsAthleteID")]
 YearsAsAthlete YearsAsAthlete {get;set;}

 List<RevenueAmounts> RevenueAmounts {get;set;}
}

class AdvertisementRevenue {get;set;}
 int AdvertisementRevenueID {get;set;}
 int YearsAsAthleteID {get;set;}
 [ForeignKey("YearsAsAthleteID")]
 YearsAsAthlete YearsAsAthlete {get;set;}

 List<RevenueAmounts> RevenueAmounts {get;set;}
}

class RevenueAmounts {
 int RevenueAmountsID {get;set;}
 int AmountPaid {get;set;}
 date DateOfPayment {get;set;}
}

These models work fine when I have them like this, they have relationships and everything is delicious like a hot fudge sundae. When I run this, the database creates these tables and the RevenueAmounts table get 2 auto-generated foreign key columns for ContracRevenue and AdvertisementRevenue.

However, I don't want these as they're named strangely (ContractRevenue_ContractRevenueID) and I need some way to access the foreginkey id property in my post controller method for adding new values that correlate with the right type of revenue.

When I change the RevenueAmounts model to the following:

class RevenueAmounts {
 int RevenueAmountsID {get;set;}
 int AmountPaid {get;set;}
 date DateOfPayment {get;set;}

 // ***NOTE adding foreign keys here

 int ContractRevenueID {get;set;}
 [ForeginKey("ContractRevenueID")]
 ContractRevenue ContractRevenue {get;set;}

 int AdvertisementRevenueID {get;set;}
 [ForeignKey("AdvertisementRevenueID")]
 AdvertisementRevenue AdvertisementRevenue {get;set;}
}

I start getting an exception:

[SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_dbo.AdvertisementRevenue_dbo.YearsAsAthlete_YearsAsAthleteID' on table 'AdvertisementRevenue' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

** EDIT **

I've turned off the cascading delete functionality using the fluent API however now I'm getting a different exception:

Unable to determine the principal end of the 'GIP.DAL.ContractRevenue_RevenueAmounts' relationship. Multiple added entities may have the same primary key.

By the way, the exception is being thrown when I'm trying to seed a bunch of info into the database and then doing context.SaveChanges() at the bottom (only doing it once at the end)

Answer 1

In your edit, you mention the 'Multiple added entities may have the same primary key.' error. Without knowing all of the details of what you are doing here, it sounds like you are creating a relationship with an entity - of which there are two in the context with the same ID. These are probably new entities which have not yet been saved which is where they get an automatically generated ID from the database. If the relationship is based on the ID then there is some ambiguity because Entity Framework is unable to determine which of the new entities the relationship is actually pointing to - they both have the ID that the relationship is pointing to.

There are two potential fixes.

1. Generate a temporary, unique identifier for entities as they are created in the context. Entity Framework will discard this as the entity is saved but up until that point, it can use it to tell one new entity apart from the other. I have used negative integers for this purpose in the past.

2. Do not create the relationships using IDs but rather on entity references. If Entity Framework has a direct reference to the entity, then it does not need to go through the process of identifying the entity based on non-unique identifiers and should not have this problem.