I'm replacing an old database management UI (circa 2005) with EF Core 3.1 and WPF (.NET Core 3.1). Because it's an existing database with a schema I can't change, I used Scaffold-DbContext to create my entity classes for me. So far so good.
I then used the following code to get some data to work with:
_mainFood = DbContext.MainFoods.Include(m => m.MainFoodsFoodGroupsBridge)
.Include(m => m.MainFoodsSourcesBridge).FirstOrDefault(m => m.RefNum == Id);
I get the single MainFoods record back that I'm expecting, along with a handful of rows in MainFoodsFoodGroupsBridge and MainFoodsSourcesBridge, also as I would expect.
The problem I'm running into has to do with the relationship between MainFoodsFoodGroupsBridge and MainFoods. Scaffold-DbContext created the relationship like this:
modelBuilder.Entity<MainFoodsFoodGroupsBridge>(entity =>
{
entity.HasKey(e => e.TableKey);
entity.HasIndex(e => new { e.RefNum, e.FoodGroupId })
.HasName("IX_UniqueRefNum_FoodGroupID")
.IsUnique();
entity.Property(e => e.FoodGroupId).HasColumnName("FoodGroupID");
entity.HasOne(d => d.FoodGroup)
.WithMany(p => p.MainFoodsFoodGroupsBridge)
.HasForeignKey(d => d.FoodGroupId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_MainFoodsFoodGroupsBridge_FoodGroupLookup");
entity.HasOne(d => d.RefNumNavigation)
.WithMany(p => p.MainFoodsFoodGroupsBridge)
.HasForeignKey(d => d.RefNum)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_MainFoodsFoodGroupsBridge_MainFoods");
});
In SSMS it looks like this:
If I add a MainFoodsFoodGroupBridge entity to _mainFood.MainFoodsFoodGroupBridge and call DbContext.SaveChanges() everything works. If I remove a MainFoodsFoodGroupBridge entity from _mainFood.MainFoodsFoodGroupBridge with this code:
var result =
_mainFood.MainFoodsFoodGroupsBridge.FirstOrDefault(m => m.FoodGroup.Equals(SelectedFoodGroup));
if (result != null)
{
_mainFood.MainFoodsFoodGroupsBridge.Remove(result);
}
I get the error message:
The association between entities 'MainFoods' and 'MainFoodsFoodGroupsBridge' with the key value '{RefNum: 2167}' has been severed but the relationship is either marked as 'Required' or is implicitly required because the foreign key is not nullable. If the dependent/child entity should be deleted when a required relationship is severed, then setup the relationship to use cascade deletes.
How do I correctly remove a row from _mainFood.MainFoodsFoodGroupBridge?
From my research it looks like the .OnDelete(DeleteBehavior.ClientSetNull) in
entity.HasOne(d => d.RefNumNavigation)
.WithMany(p => p.MainFoodsFoodGroupsBridge)
.HasForeignKey(d => d.RefNum)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_MainFoodsFoodGroupsBridge_MainFoods");
is the culprit, with a change to DeleteBehavior.Cascade fixing things, but I'm hoping to not have to go in an manually tweak a bunch of entity code by hand if I don't have to, as there are a significant number of similar relationships all over the database where I will be replicating this Delete pattern. Plus, would Scaffold-DbContext get all of those relationships wrong?
This is my first experience with EF Core (I've done some NHibernate in the past), so I'm guessing I've not acquired my data correctly or have otherwise misconfigured something.
EDIT: I should add that I can completely get around this error by not using the .Include to load the associated tables, but by loading them when I need them and then managing the Save of those tables myself. Which completely defeats the purpose of the above, but at least it works.
