asp.net core EF cascade delete error

I have a succesion of 2 related tables, see below

public class HazardsLocation
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string IdHL { get; set; }
[Required]
public string HazLocation { get; set; }
public string OrgID { get; set; }
public ICollection HazardsCategories { get; set; }
}
public class HazardsCategory
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string IdHCA { get; set; }
[Required]
public string HazCategory { get; set; }
public string IdHL { get; set; }
[ForeignKey("IdHL")]
public HazardsLocation HazardsLocation { get; set; }
}

and relation in Context

modelBuilder.Entity()
.HasMany(a => a.HazardsCategories)
.WithOne(a => a.HazardsLocation)
.OnDelete(DeleteBehavior.Cascade);
modelBuilder.Entity()
.HasMany(a => a.HazardsSources)
.WithOne(a => a.HazardsCategory)
.OnDelete(DeleteBehavior.Cascade);

but when I try to delete one item from the first table I get the following error

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_HazardsCategories_HazardsLocations_IdHL". The conflict occurred in database "aspnet-RoSafety-53bc9b9d-9d6a-45d4-8429-2a2761773502", table "dbo.HazardsCategories", column 'IdHL'.

The delete procedure

public async Task OnPostDeleteAsync(string idhl)
{
if (idhl == null)
{
return NotFound();
}
var userId = User.FindFirstValue(ClaimTypes.NameIdentifier);
var orgid = await _context.UsersData.Where(s => s.Id == userId).Select(s => s.OrgID).FirstOrDefaultAsync();
var hazard = await _context.HazardsLocations.FindAsync(idhl);
_context.HazardsLocations.Remove(hazard);
_context.SaveChanges();
return RedirectToPage("/HazId/HazLocation");
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
why are you configuring from both sides?
if a location is deleted then all its categories should also be deleted that's perfect. but when a Category is deleting why do you think the location should delete?

The location has many categories, so no location means no category.
but if a category is deleted that doesn't mean all the locations should delete.
Marius Vasile5y ago
I modified the delete procedure, by adding second table and eliminate duplicate cascade delete. Now it is working
var hazard = await _context.HazardsLocations.Where(s => s.OrgID == orgid && s.IdHL == idhl).Include(s => s.HazardsCategories).FirstOrDefaultAsync();
Marius Vasile5y ago
It may be because when I update the second table I specifically update the foreign key as well?
Marius Vasile5y ago
Well, initially I didn't have the Context configured and I had the same error, I read a little and I found I have to configure it. But it still not working