Multiple added entities may have the same primary key

Question

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

the following method fails and get exception when commit it:

 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;
        }

When doing:

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

I got:

Unable to determine the principal end of the 'SimTaskModel.FK_T_STF_SUB_LOCATION_IN_ROUTE_T_STF_LOCATION_location_id' relationship. Multiple added entities may have the same primary key.

When splitting the commit and insert in into the methos - it 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 it fails in the first example and what does this exception means?

Answer 1

The error is caused by a foreign key ID (as opposed to a reference) which cannot be resolved. In your case, you have a LocationInRole that references a Location with an ID of 0. There are multiple Locations with this ID.

The Locations have not yet been assigned an ID because they have not yet been saved to the database which is when the ID is generated. In your second example, the Locations are saved before their IDs are accessed which is why this works.

You will not be able to rely on the Location IDs to define the relationships if you want to SaveChanges only later.

Swap the following line...

LocationId = locations[i].Id

...for this...

Location = locations[i]

The relationships will then be based on object references which are not dependent on the LocationIDs.

Answer 2

In case this is of any use to future readers, in my case this error was due to an incorrectly configured foreign key in my database (and model generated from DB).

I had tables:

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

and the Grandchild table had inadvertently received a foreign key up to it's parent (Child) and it's grandparent (Parent). On saving multiple Parent entities from new, I received this error. Fix has been to correct the foreign key.

Answer 3

Having run into the same error I highly suspect the actual issue was the definition of Location. Put simply, in EF Code First I bet 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 reference back to this table.

The ParentLocationId is not Nullable. That means it's going to be inserted with a 0, and EF will complain on Insert, rather than when you Migrate - even though the truth is once that Migration runs you have a table EF will never let you insert into.

The only way to make a recursive reference back to the same table work is to make the recursive reference nullable:

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

Note the ? after the int.