I'm trying to get a certain amount of animals that have the most comments once I try to delete one of them so I'm getting an error of: SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK__Comments__Animal__2EDAF651". The conflict occurred in database "PetShop", table "dbo.Comments", column 'AnimalId'. The statement has been terminated. I want to make it possible that if I delete then you will move on to the next in line

My Controller for disply:

public async Task<IActionResult> Index()
    {
        var animal = _context.Animals.Include(c => c.Comments).OrderByDescending(c => c.Comments.Count).Take(2);
        return View(await animal.ToListAsync());

    }

My Controller for Delete:

 public async Task<Animal> DeleteAnimal(int id)
    {
        var comment = await _context.Comments.FindAsync(id);
        _context.Comments.Remove(comment!);
        var animal = await _context.Animals.FindAsync(id);
        _context.Animals.Remove(animal!);
        await _context.SaveChangesAsync();
        return animal!;
    }

My Context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Comment>(entity =>
        {
            entity.HasOne(d => d.Animal)
                .WithMany(p => p.Comments)
                .HasForeignKey(d => d.AnimalId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK__Comments__Animal__2EDAF651");
        });

        modelBuilder.Entity<Category>(entity =>
        entity.HasData(
         new { CategoryId = 1, Name = "Dogs" },
         new { CategoryId = 2, Name = "Cats" },
         new { CategoryId = 3, Name = "Birds" },
         new { CategoryId = 4, Name = "Rabbits" },
         new { CategoryId = 5, Name = "Hamsters" }
        )
        );

        modelBuilder.Entity<Animal>(entity =>
        {
            entity.HasData(
            new { AnimalId = 1, Name = "Shoko", BirthDate = DateTime.Now.AddYears(-1).AddMonths(-1).AddDays(-12), Description = "Friendly and loyal", CategoryId = 1, PhotoUrl = "ShokoDog.jpg" },
            new { AnimalId = 2, Name = "Bamba", BirthDate = DateTime.Now.AddYears(-2).AddMonths(-2).AddDays(-3), Description = "Furry and neutered", CategoryId = 2, PhotoUrl = "BambaCat.jpg" },
            new { AnimalId = 3, Name = "Regev", BirthDate = DateTime.Now.AddYears(-1).AddMonths(-3).AddDays(-3), Description = "Speak", CategoryId = 3, PhotoUrl = "RegevBird.jpg" },
            new { AnimalId = 4, Name = "Humi", BirthDate = DateTime.Now.AddYears(-3).AddMonths(-4).AddDays(-7), Description = "Cute and furry", CategoryId = 4, PhotoUrl = "HumiRabbit.jpg" },
            new { AnimalId = 5, Name = "Tommy", BirthDate = DateTime.Now.AddYears(-1).AddMonths(-7).AddDays(-9), Description = "Love to play in the facilities", CategoryId = 5, PhotoUrl = "TommyHamster.jpg" });
        });
        OnModelCreatingPartial(modelBuilder);
    }

CodePudding user response：

The error message reads that you are deleting Animal, which has comments associated. You should do one of the following:

1. Remove comments associated with a particular Animal before deleting the Animal.
2. Check EF configuration for cascade on delete
3. Alter FK to have cascade on delete (it depends on whether you are using a database-first or code-first approach)

I would go for the first approach because cascade on delete may be dangerous and silently remove unintentionally referenced data.

CodePudding user response：

You want to remove a parent Animal and related child Comments

To expand on Karlis' suggestions:

1. You have models and context as posted in the question (it's a bit problematic because you say to EF to set null, but the code and DB won't accept null) but you can do:

    var a = context.Animals.Include(a => a.Comments).Find(id):
    context.Comments.RemoveRange(a.Comments);
    context.Animals.Remove(a);
    context.SaveChanges();

This explicitly removes the comments then the animal

2. Change the context to use .OnDelete(DeleteBehavior.ClientCascade) then you can do:

    var a = context.Animals.Include(a => a.Comments).Find(id):
    context.Animals.Remove(a);
    context.SaveChanges();

This causes EF to implicitly remove the comments it knows about when you tell it explicitly to remove the animal

3. Change the DB's foreign key to do an ON DELETE CASCADE, and change .OnDelete(DeleteBehavior.Cascade) then you can skip downloading the Comments (no include):

    var a = context.Animals.Find(id):
    context.Animals.Remove(a);
    context.SaveChanges();

This causes the DB to remove the comments (EF doesn't know about them) when EF instructs to delete the animal

Broadly speaking, these are in order of "how bad of a mistake could you make" from "not very" to "quite a lot"