Multiple objects added can have the same primary key

Question

Here is my model of three entities: Route, Location and LocationInRoute.

The following method fails and gets an exception when it is committed:

public static Route InsertRouteIfNotExists(Guid companyId, IListLocation> locations) { //Loop on locations and insert it without commit InsertLocations(companyId, routesOrLocations); RouteRepository routeRep = new RouteRepository(); Route route = routeRep.FindRoute(companyId, locations); if (route == null) { route = new Route() { CompanyId = companyId, IsDeleted = false }; routeRep.Insert(route); LocationInRouteRepository locInRouteRep = new LocationInRouteRepository(); for (int i = 0; i < locations.Count; i++) { locInRouteRep.Insert(new LocationInRoute() { //Id = i, LocationId = locations[i].Id, Order = i, RouteId = route.Id }); } } return route; } 

While doing:

 InsertRouteIfNotExists(companyId, locations); UnitOfWork.Commit(); 

I got:

Unable to determine the primary end of the relationship "SimTaskModel.FK_T_STF_SUB_LOCATION_IN_ROUTE_T_STF_LOCATION_location_id". Multiple objects added can have the same primary key.

When splitting a commit and an insert in a method, this works:

  public static Route InsertRouteIfNotExists(Guid companyId, IListLocation> locations) { //Loop on locations and insert it without commit InsertLocations(companyId, routesOrLocations); UnitOfWork.Commit(); RouteRepository routeRep = new RouteRepository(); Route route = routeRep.FindRoute(companyId, locations); if (route == null) { route = new Route() { CompanyId = companyId, IsDeleted = false }; routeRep.Insert(route); LocationInRouteRepository locInRouteRep = new LocationInRouteRepository(); for (int i = 0; i < locations.Count; i++) { locInRouteRep.Insert(new LocationInRoute() { //Id = i, LocationId = locations[i].Id, Order = i, RouteId = route.Id }); } UnitOfWork.Commit(); } return route; } 

I would like to call commit once and outside the method. Why does the first example fail and what does this exception mean?

Answer 1

The error is caused by a foreign key identifier (as opposed to a link), which cannot be resolved. In your case, you have a LocationInRole that references a location with identifier 0. There are several locations with this identifier.

Locations have not yet been assigned an identifier, since they have not yet been stored in the database that occurs when the identifier is generated. In the second example, locations are stored until their identifiers are accessed, so this works.

You will not be able to rely on location identifiers to determine relationships if you only want to save changes later.

Change the following line ...

 LocationId = locations[i].Id 

... for this...

 Location = locations[i] 

Relationships will then be based on object references that are independent of LocationIDs.

Answer 2

In case it will be useful for future readers, in my case this error occurred due to an incorrectly configured foreign key in my database (and the model generated from the database).

I had tables:

 Parent (1-1) Child (1-many) Grandchild 

and the Grandchild table inadvertently received a foreign key to it of the parent (child) and his grandfather (Parent). When saving multiple parent objects from a new one, I got this error. Fixed fix of foreign key.

Answer 3

Running the same error, I very much suspect that the actual problem was the location. Simply put, in EF Code First, I'm sure it looked like this:

 public class Location { public int Id { get; set; } ... public Location ParentLocation { get; set; } [ForeignKey("ParentLocation")] public int ParentLocationId { get; set; } } 

In other words, in the question ParentLocation / ParentLocationId are a recursive link to this table.

ParentLocationId is not Nullable. This means that it will be inserted from 0, and EF will complain about Insert, not Migrate - even if the truth is that Migration once works, you have an EF table that will never let you insert.

The only way to make a recursive link back to the same table work is to make the recursive link null:

 public class Location { public int Id { get; set; } ... public Location ParentLocation { get; set; } [ForeignKey("ParentLocation")] public int? ParentLocationId { get; set; } } 

Pay attention to ? after int .

Answer 4

For those looking for this exception:
In my case, he could not set the required navigation property.

 public class Question { //... public int QuestionGridItemID { get; set; } public virtual QuestionGridItem GridItem { get; set; } //... public int? OtherQuestionID { get; set; } public Question OtherQuestion { get; set; } } //... question.OtherQuestion = otherQuestion; questionGridItem.Questions.Add(question); dataContext.SaveChanges(); //fails because otherQuestion wasn't added to //any grid item Question collection 

Answer 5

I had the same problem. with the script below. I think you should change your code like below:

 var insertedRoute =routeRep.Insert(route); ..... insertedRoute.LocationInRoute = new List<LocationInRoute>(); for(....){ var lInRoute = new LocationInRoute(){ .... Route=insertedRoute; } insertedRoute.LocationInRoute.Add(lInRoute ); }