EF - Remove a relationship from parent entity

Question

EF - Remove a relationship from parent entity

asp.net-mvc c# entity-framework entity-framework-6

Question

Let's say I have two classes in my model: Product and Category.

public class Product
{
    public Product() {
        this.Categories = new HashSet<Category>();
    }

    [...]

    public virtual ICollection<Category> Categories { get; set; }
}

public class Category
{
    public Category() {
        this.Products = new HashSet<Product>();
    }

    [...]

    public virtual ICollection<Product> Products { get; set; }
}

A product has many categories and a categories apply to many products. To model this relationship I have the following code in my OnModelCreating method:

    modelBuilder.Entity<Product>()
        .HasMany( p => p.Categories )
        .WithMany( p => p.Products )
        .Map( m => {
            m.MapLeftKey( "ProductID" );
            m.MapRightKey( "CategoryID" );
            m.ToTable( "CategoriesPerProduct" );
        } );
    modelBuilder.Entity<Category>()
        .HasMany( p => p.Products )
        .WithMany( p => p.Categories )
        .Map( m => {
            m.MapLeftKey( "CategoryID" );
            m.MapRightKey( "ProductID" );
            m.ToTable( "CategoriesPerProduct" );
        } );

This create a new table, CategoriesPerProduct, which splits the M-N relationship in two 1-N relationships which is good for my needs.

I have now the requirement to update categories related to a product and, just to simplify my code, I did decided to remove all the existing categories and then adding back the new ones like in the following sample:

ICollection<Category> productCategories = product.Categories;
//First remove all existing categories 
foreach ( var category in productCategories ) {
    product.Categories.Remove( category );
}
// ..then add the new ones from input
foreach ( string categoryName in categories ) {
    Category c = await _ctx.Categories.SingleOrDefaultAsync( p => p.Description == categoryName );
    if ( c != null ) {
        product.Categories.Add( pc );
    }
    else {
        c = new ProductCategory() { Description = categoryName };
        _ctx.Categories.Add( c );
        await _ctx.SaveChangesAsync();
        product.Categories.Add( c );
    }
}
await _ctx.SaveChangesAsync();

Unfortunately, when the code hits the transaction Commit() method I get the following error:

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.

Could anybody drive me in the right direction to solve this error?

Lorenzo

Popular Answer

I believe you are getting that exception because you are inserting repeated items. The repeated item is converted to ProductCategoryID = 0, so you get the exception.

Make these changes in your current code:

ICollection<Category> productCategories = product.Categories;

//Make sure that you are iterating a copy of categories, using ToList()
//If you do not use ToList() you might get an exception saying that you changed the list you are iterating

foreach ( var category in productCategories.ToList()) {
    product.Categories.Remove( category );
}

// ..then add the new ones from input
// ... but remember to add only the new ones
var currentCategories = productCategories.Select(i => i.Name);
// you could use another strategy here

foreach ( string categoryName in categories.Except(currentCategories)) {
    Category c = await _ctx.Categories.SingleOrDefaultAsync( p => p.Description == categoryName );
    if ( c != null ) {
        product.Categories.Add( pc );
    } else {
        c = new ProductCategory() { Description = categoryName };
        //_ctx.Categories.Add( c ); NOT NECESSARY - IT WILL BE AUTOMATICALLY INSERTED 
        //await _ctx.SaveChangesAsync(); NOT NECESSARY
        product.Categories.Add( c );
    }
}

Untested code, but it should work fine.

Hope it helps!

Fabio Luz

View more on Stack Overflow

funbrigade · Accepted Answer · 5/23/2017 12:34:53 PM

Edited once more

Something else that occurred to me is that you may be orphaning the other side of the relationship -- you're removing the Category entity from the Product, but are you removing the Product from that Category? Look into WillCascadeOnDelete (Entity Framework (EF) Code First Cascade Delete for One-to-Zero-or-One relationship) or try including the relationships in your queries -- instead of _ctx.Products.Where(...), use _ctx.Products.Include(p => p.Categories).Where(...)

Let us know how that works out for you!

Edited

So, based on Lorenzo's response, I understand what he's trying to do. This is something that's bit me (and probably a few people) in the past.

You're probably looking at a Cascade Delete problem -- go with option 3 (which is now not so much an option as a requirement...).

Take a look at The relationship could not be changed because one or more of the foreign-key properties is non-nullable

The main issue is this (as far as I can tell since we don't know the schema of CategoriesPerProduct: our perpetrator): you're putting the entry from CategoriesPerProduct in an impossible state where it requires a Category to exist, but the Category has been effectively set to null.

Therefore, you have a few options:

Make the foreign key nullable (not really recommended)
Remove the item directly from your DbContext (something like _ctx.Categories.Remove(category);)
Mark the State as Deleted (something like ctx.Entry(category).State = EntityState.Deleted)

EF - Remove a relationship from parent entity

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related