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?
Accepted Answer
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)
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!
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow