Entity framework : Recursive update Parent-Child gives me duplicates

Question

Given the following tables (in screenshot FK from Locations to Customers is not visible, but it's there, just didn't refresh...):

And my mapping:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().ToTable("CUSTOMERS");
        modelBuilder.Entity<Customer>().HasKey(c => c.Id).Property(c => c.Id).HasColumnName("CUSTNO");
        modelBuilder.Entity<Customer>().Property(c => c.Name).HasColumnName("NAME");
        modelBuilder.Entity<Customer>().HasMany(c => c.AllLocations).WithRequired(l => l.Customer).Map(x => x.MapKey("CUSTNO"));
        modelBuilder.Entity<Customer>().Ignore(c => c.RootLocations);

        modelBuilder.Entity<Location>().ToTable("LOCATIONS");
        modelBuilder.Entity<Location>().HasKey(c => c.Id);
        modelBuilder.Entity<Location>().Property(c => c.Id).HasColumnName("ID");
        modelBuilder.Entity<Location>().Property(c => c.LocationCode).HasColumnName("LOCATION_CODE");
        modelBuilder.Entity<Location>().HasMany(l => l.Children).WithOptional(ch => ch.Parent).Map(x => x.MapKey("PARENT_ID"));


        modelBuilder.Entity<Product>().ToTable("PRODUCTS");
        modelBuilder.Entity<Product>().HasKey(p => new { p.Partno, p.LocationId, p.Quantity, p.SellUnit });
        modelBuilder.Entity<Product>().Property(p => p.Partno).HasColumnName("PARTNO");
        modelBuilder.Entity<Product>().Property(p => p.LocationId).HasColumnName("LOCATION_ID");
        modelBuilder.Entity<Product>().Property(p => p.PartDescription).HasColumnName("PART_DESCRIPTION");
        modelBuilder.Entity<Product>().Property(p => p.Quantity).HasColumnName("QUANTITY");
        modelBuilder.Entity<Product>().Property(p => p.SellUnit).HasColumnName("SELL_UNIT");
        modelBuilder.Entity<Product>().HasRequired(p => p.Location).WithMany(l => l.Products).HasForeignKey(p => p.LocationId);
    }

And my update code:

    public void UpdateLocations(string customerId, IEnumerable<Location> locations)
    {
        using (var context = new CustomerWarehouseContext(connectionString))
        {
            foreach (var location in locations)
                RecursiveUpdate(location, context);
            context.SaveChanges();
        }
    }

    private void RecursiveUpdate(Location location, CustomerWarehouseContext context)
    {
        if (location != null)
        {
            bool locationIsNew = location.Id.Equals(Guid.Empty);
            if (locationIsNew)
            {
                location.Id = Guid.NewGuid();
                context.Locations.Add(location);
            }
            else
            {
                context.Entry(context.Locations.Single(l => l.Id.Equals(location.Id))).CurrentValues.SetValues(location);
            }
            if (location.Children != null)
            {
                foreach (var childLocation in location.Children)
                {
                    childLocation.Parent = location;
                    RecursiveUpdate(childLocation, context);
                }
            }
            if (location.Products != null)
            {

                foreach (var product in location.Products)
                {
                    if (locationIsNew)
                    {
                        location.Products.Add(product);
                    }
                    else
                    {
                        //How to update product when key is changed? I cannot use contex.Entry here?
                    }
                }
            }
        }
    }

I execute the following code:

        Customer customer = null;
        using (var context = new AwesomeContext("myAwesomeConnectionString"))
        {
            customer = (from c in context.Customers.Include(c => c.AllLocations.Select(l => l.Products))
                        where c.Id.Equals("100100")
                        select c).FirstOrDefault();
        }


        Location locationToUpdate = customer.RootLocations.Single(l => l.Id.Equals(Guid.Parse("1a2ad52e-84cc-bf4c-b14d-dc57b6d229a6")));
        locationToUpdate.LocationCode = "Parent " + DateTime.Now.Millisecond; //Goes  OK
        locationToUpdate.Children[0].LocationCode = "Child " + DateTime.Now.Millisecond; //Goes  OK
        locationToUpdate.Children[0].Products[0].Quantity = 200;  
        locationToUpdate.Children.Add(new Location() { LocationCode = "XXX", Customer = customer, Parent = locationToUpdate }); //Creates duplicates?
        UpdateLocations(customer.Id, newList);

So, I'm changing the Location code of the parent and a child, update the product quantity of a product in the child location. Quantity is part of the key of the product table. Also, I'm adding a new location to the parent.

Questions:

• How can I update my product? Because I changed part of the key, I cannot use context.Entry(...) to retrieve the old one.
• Why do I have a duplicate customer/locations in my context after adding a second child location? I end up with 5 locations and 2 customers instead of 3 locations and 1 customer, so it gives me a PK exception. Somehow it creates a new customer after adding a child to a location. So it has a customer entity with 2 locations, and one with 3 locations. Why???

Answer 1

How can I update my product? Because I changed part of the key, I cannot use context.Entry(...) to retrieve the old one.

You cannot change or update the key of an entity with Entity Framework. If you need to do this you must use a handwritten SQL command:

context.Database.ExecuteSqlCommand("UPDATE...");

Why do I have a duplicate customer/locations in my context after adding a second child location?

You pass in a detached object graph into your UpdateLocations. If you add any node of the graph to the context all other detached entities that are reachable by navigation properties from that node will be added to the context and they all have EntityState Added. If you call SaveChanges the entities will be inserted into the database. This happens here for example:

context.Locations.Add(location);

Because your new location has a reference to the customer the customer will be put into Added state as well and this will duplicate the customer in the database. You can avoid this by attaching the customer first to the context. Calling Attach will put the entity into state Unchanged and avoid the duplication of the customer:

context.Customers.Attach(location.Customer);
context.Locations.Add(location);