c# - 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.