EF 6 code Firstly, changing the foreign key identifier with the Include on navigation attribute results in an error "Violation of referential integrity constraint"

Question

I am using Entity Framework 6.1.3 and have a scenario in which I get an object with its navigation property (using Include ()) and disconnects it from the context, changes the foreign key identifier, and then attaches it to the new DbContext again:

// Init the Db using (var db = new MyContext()) { var theWarranty = new ProductWarranty { WarrantyName = "The Warranty" }; var newWarranty = new ProductWarranty { WarrantyName = "New Warranty" }; var brand = new ProductBrand { BrandName = "The Brand", DefaultWarranty = theWarranty }; db.ProductBrands.Add(brand); db.ProductWarranties.Add(newWarranty); db.SaveChanges(); } // Load the detached Brand ProductBrand detachedBrand; using (var db = new MyContext()) { detachedBrand = db.ProductBrands.AsNoTracking() .Include(b => b.DefaultWarranty) // <<< If this line is removed the Attach works .First(x => x.Id == 1); } // Modify the Default Warranty Foreign Key detachedBrand.DefaultWarranty = null; detachedBrand.DefaultWarranty_Id = 2; // Attempt to re-attach and save the changes using (var db = new MyContext()) { var entity = db.Set<ProductBrand>().Attach(detachedBrand); // <<< This line throws the exception db.Entry(entity).State = EntityState.Modified; db.SaveChanges(); } 

I get:

A violation of the referential integrity constraint has occurred. Property values> ProductLarranty.Id at one end of the relationship do not match property> ProductBrand.DefaultWarranty_Id 'at the other end.

However, if I DO NOT use Include (), the application works fine.

I really need the navigation property (DefaultWarranty) in the real scenario, but I don’t see the difference in enabling navigation in a separate object, and not in loading it into a separate object. In my experience and reading, this should be the case if the foreign key is set to a new value and the navigation property is null.

I read the Ladislav blog on Foreign Key vs Independent properties http://www.ladislavmrnka.com/2011/05/foreign-key-vs-independent-associations-in-ef-4/ , but it does not quite cope with this scenario and from which I can say that I use foreign keys in this case.

What happens and what is the correct way to change foreign keys with navigation properties enabled, such as this scenario?

It almost looks like EF didn’t “completely” detach the object when Include ... is used, which also seems strange.

Here is a simplified setup:

Product brand

 public partial class ProductBrand { public int Id { get; set; } public string BrandName { get; set; } public Nullable<int> DefaultWarranty_Id { get; set; } public virtual ProductWarranty DefaultWarranty { get; set; } } 

Product Token Map

 public class ProductBrandMap : EntityTypeConfiguration<ProductBrand> { public ProductBrandMap() { // Primary Key this.HasKey(t => t.Id); // Properties this.Property(t => t.BrandName) .IsRequired() .HasMaxLength(40); // Table & Column Mappings this.ToTable("ProductBrands"); this.Property(t => t.Id).HasColumnName("Id"); this.Property(t => t.BrandName).HasColumnName("BrandName"); this.Property(t => t.DefaultWarranty_Id).HasColumnName("DefaultWarranty_Id"); // Relationships this.HasOptional(t => t.DefaultWarranty) .WithMany(t => t.ProductBrands) .HasForeignKey(d => d.DefaultWarranty_Id) .WillCascadeOnDelete(false); } } 

Product Warranty

 public partial class ProductWarranty { public ProductWarranty() { this.ProductBrands = new List<ProductBrand>(); } public int Id { get; set; } public string WarrantyName { get; set; } public virtual ICollection<ProductBrand> ProductBrands { get; set; } } 

Product Warranty Card

 public class ProductWarrantyMap : EntityTypeConfiguration<ProductWarranty> { public ProductWarrantyMap() { // Primary Key this.HasKey(t => t.Id); // Properties this.Property(t => t.WarrantyName) .IsRequired() .HasMaxLength(40); // Table & Column Mappings this.ToTable("ProductWarranties"); this.Property(t => t.Id).HasColumnName("Id"); this.Property(t => t.WarrantyName).HasColumnName("WarrantyName"); } } 

Answer 1

When ProxyCreationEnabled enabled, even if objects are not tracked by any context, they are somehow internally related to each other. I mean, any changes to the FK are carefully recorded by the dynamic proxy object to ensure referential integrity. So just turn off ProxyCreationEnabled :

 public MyContext() { this.Configuration.ProxyCreationEnabled = false; } 

But if you ask my opinion, I prefer to change objects when they are tracked, and after modification I turn them off.

Answer 2

I had the same problem when trying to assign a new value to foreign_key.

I did attach after updating the foreign key on the main object, this was a problem.

Instead, I now snap to the object, and then apply this change to the field.

 public static bool ChangeForeignKeyAssocation(baseobject existing, int newFK, bool throwOnError = true) { try { using (var e = new Entities()) { e.table.Attach(existing); e.Entry(existing).State = EntityState.Modified; existing.related_table_id = newFK; int result = sbe.SaveChanges(); return (result == 1); } } catch (Exception ex) { //LogIt.E(ex); if (throwOnError) { throw ex; } else { return false; } } }