EF 6 Code First, changing a Foreign Key Id with an Include on navigation property causes "A referential integrity constraint violation occurred" error

c# dbcontext ef-code-first entity-framework entity-framework-6

Question

I'm using Entity Framework 6.1.3 and have the scenario where I retrieve an entity with its navigation property (using Include()) and disconnect it from the context, change the foreign key Id then re-attach it to a new DbContext:

// 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'm getting:

A referential integrity constraint violation occurred: The property value(s) of >'ProductWarranty.Id' on one end of a relationship do not match the property >value(s) of 'ProductBrand.DefaultWarranty_Id' on the other end.

However, if I do NOT use the Include() the attach works fine.

I do need the navigation property (DefaultWarranty) in the real-world scenario, but I don't see the difference in including the navigation in a detached entity vs not loading it in a detached entity. From my experience and reading it should be the case of setting the foreign key to the new value and set the navigation property to null.

I've read through Ladislav's blog on Foreign Key vs Independent properties http://www.ladislavmrnka.com/2011/05/foreign-key-vs-independent-associations-in-ef-4/ but it doesn't quite deal with this scenario and from what I can tell I'm using foreign keys in this case.

What is happening and what is the correct way to deal with changing Foreign Keys with Included navigation properties like this scenario?

It's almost like EF hasn't "fully" detached the entity when the Include is used...which would seem odd as well.

Here is the 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 Brand 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 Map

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");
    }
}
1
5
10/13/2015 1:10:25 AM

Accepted Answer

When ProxyCreationEnabled is enabled, even if the entities are not tracked by any context, they are somehow internally connected to each other. I mean, any changes in a FK is recorded carefully by the dynamic proxy entity to enforce referential integrity. So, simply turn off ProxyCreationEnabled:

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

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

4
10/13/2015 5:18:48 AM

Popular Answer

I experienced this same issue when trying to assign a new foreign_key value.

I was doing the attach after having updated the foreign key on the core object this was an issue.

Instead, I now attach to the object, and then apply the 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;
    }
  }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow