EF6.0 "The relationship could not be changed because one or more of the foreign-key properties is non-nullable"

Question

If I try to delete a "child" row I always get an exception. Here is a snipset:

using (var context = new CompanyContext())
{
    ItemType itemType = context.ItemTypes.FirstOrDefault(i => i.Name == "ServerType");
    ItemTypeItem itemTypeItem = itemType.Items.FirstOrDefault(i => i.Name == "DatabaseServer");
    itemType.Items.Remove(itemTypeItem);
    context.SaveChanges(); <=== exception!
}

The following exception is thrown on the SaveChanges() method.

"The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted."

Entity Configuration

  public class ItemTypeConfiguration : NamedEntityConfiguration<ItemType>
  {
    public ConfigurationColumn ParentIDColumn;
    public ConfigurationColumn ValidationPatternColumn;
    public ItemTypeConfiguration() : base()
    {
      ParentIDColumn = new ConfigurationColumn() { Name = "ParentID", Ordinal = base.LastOrdinalPosition + 1 };
      ValidationPatternColumn = new ConfigurationColumn() { Name = "ValidationPattern", Length = 1024, Ordinal=base.LastOrdinalPosition + 2};
      this.Property(t => t.ParentID)
        .HasColumnName(ParentIDColumn.Name)
        .HasColumnOrder(ParentIDColumn.Ordinal);
      this.HasOptional(t => t.Parent).WithMany().HasForeignKey(u => u.ParentID).WillCascadeOnDelete(false);
      this.Property(t => t.ValidationPattern)
        .HasColumnName(ValidationPatternColumn.Name)
        .HasColumnOrder(ValidationPatternColumn.Ordinal)
        .HasMaxLength(ValidationPatternColumn.Length);
    }
...


  public class ItemTypeItemConfiguration : NamedEntityConfiguration<ItemTypeItem>
  {
    public ConfigurationColumn ItemTypeIDColumn;
    public ItemTypeItemConfiguration() : base()
    {
      ItemTypeIDColumn = new ConfigurationColumn(){Name="ItemTypeID", IsRequired=true, Ordinal= base.LastOrdinalPosition+1};
      this.Property(t => t.ItemTypeID)
        .HasColumnName(ItemTypeIDColumn.Name)
        .HasColumnOrder(ItemTypeIDColumn.Ordinal);
      this.HasRequired(t => t.ItemType).WithMany(t=>t.Items).HasForeignKey(u => u.ItemTypeID).WillCascadeOnDelete(true);
    }
...

I found the blog but I don't have the "DeleteObject" method.

http://blog.clicdata.com/2013/07/04/the-operation-failed-the-relationship-could-not-be-changed-because-one-or-more-of-the-foreign-key-properties-is-non-nullable/

Any ideas? Thank you.

Chagrin

possible duplicate of The relationship could not be changed because one or more of the foreign-key properties is non-nullable – Darbee

Answer 1

You need to delete the ItemTypeItem. It is not possible to just remove it from the Items list as it cannot exist by itself, because it has a non-nullable foreign key referencing ItemType (ItemTypeID).

To delete the ItemTypeItem add

context.Entry(itemTypeItem).State = EntityState.Deleted;

Petrochemistry

that is weird because I am able to add items without doing EntityState.Added... but that worked man, thank you! Now I need to find out how I change my repository to achieve the same thing. I only tested straight from the context. EF is such a black box, so much to learn!!! – Chagrin

I only understand this answer once I read the answer by @Gerry below.., the key is to delete it from context and not from the entity parent... – Siltstone

I agree with @Chagrin here, while adding new entity you can just add it to collection and db context will tract that for you. You do not have to set state as Added However for deletion you cannot just delete from the collection, you have to delete it from context and mark the state as deleted...its confusing – Flemish

Answer 2

In the entity framework 6.0 if you remove the entity from the main context set it will work. For example to remove an investment entity you would do the following:

context.Investments.Remove(entity);
context.SaveChanges();

This is different than attempting to remove the entity from its parent/owner, as the following:

bankAccount.Investments.Remove(entity);
context.SaveChanges();

This will throw the relationship could not be changed exception listed above. Hope this helps.

Caribou

Answer 3

In entity 6.0 there is a difference between:

context.Investments.Remove(entity);

and

context.Entry(entity).State = EntityState.Deleted;

When using the first and cascading deletes are enabled, EF will internally perform the necessary deletes of child collections. When using the second option, EF will not handle the necessary deletes, but let you handle the rebinding/deletion of these child objects.

Thesis

Saved my day. I have correct cascading delete configured, but I am getting the same exception. More surprising, a green unit test with full blown SQL Server as backend proves, that cascading delete kicks in. This comment made me think and helped me to fix the issue: context.Set<T>.Remove(entity) is preferred when the entity was loaded from this context instance before, while context.Entry(entity).State = Deleted works when just providing a key and constructing a Stub Entity. – Streusel

Answer 4

This issue arise because we try to delete the parent table still child table data is present. We solve the problem with help of cascade delete.

In model Create method in dbcontext class.

 modelBuilder.Entity<Job>()
                .HasMany<JobSportsMapping>(C => C.JobSportsMappings)
                .WithRequired(C => C.Job)
                .HasForeignKey(C => C.JobId).WillCascadeOnDelete(true);
            modelBuilder.Entity<Sport>()
                .HasMany<JobSportsMapping>(C => C.JobSportsMappings)
                  .WithRequired(C => C.Sport)
                  .HasForeignKey(C => C.SportId).WillCascadeOnDelete(true);

After that,In our API Call

var JobList = Context.Job                       
          .Include(x => x.JobSportsMappings)                                     .ToList();
Context.Job.RemoveRange(JobList);
Context.SaveChanges();

Cascade delete option delete the parent as well parent related child table with this simple code. Make it try in this simple way.

Remove Range which used for delete the list of records in the database Thanks

Buzzard

does Entity framework set .WillCascadeOnDelete(true) by default? – Bartholemy

Answer 5

The other answers describing why the error occurs are correct, but it is actually possible to get EF to fully delete the child when .Remove is called on the parent object's collection of children, you don't need to go directly to the child entity's table in the DB context and delete it from that.

To get that to work you need to set up an Identifying Relationship, as described in this answer.

Pleurodynia