Why individual delete calls with delete on cascade

Why individual delete calls with delete on cascade

Postby kussaued » Wed 21 Sep 2016 10:36

I've got two tables with an 1:n relation and a delete on cascade in my database:

Code: Select all
CREATE TABLE animals (
   Id            INTEGER         PRIMARY KEY   AUTO_INCREMENT,
   Description      VARCHAR(512),
   Timestamp      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE animal_events (
   Id            INTEGER         PRIMARY KEY   AUTO_INCREMENT,
   Begin         DATETIME      NOT NULL,
   AnimalId      INT,
   Timestamp      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY animal_fk(AnimalId) REFERENCES animals(Id) ON DELETE CASCADE
);


So when I delete an animal, all the corresponding animal_events should be deleted also.
This is something that is done by the database, triggered by the delete on cascade clause whenever I delete an animal. Correct?

But in the sql trace I see many individual delete commands. Why is the entity framework/devart doing this?

Code: Select all
DELETE FROM rfidfarm.animal_events WHERE Id = 1
DELETE FROM rfidfarm.animal_events WHERE Id = 2
DELETE FROM rfidfarm.animal_events WHERE Id = 3
...


Isn't this superfluous, since the database would take care of it?

My test code:
Code: Select all
using (var uow = _modelFactory.CreateUnitOfWork())
{
    var animal = uow.Animals.GetById(2);
    var animalEventCount = animal.AnimalEvents.Count;
    uow.Animals.Delete(animal);
    uow.SaveChanges();
}
kussaued
 
Posts: 5
Joined: Fri 08 Jul 2011 06:39

Re: Why individual delete calls with delete on cascade

Postby Shalex » Fri 23 Sep 2016 11:31

It is EF engine (designed by Microsoft) which creates the list of operations to be executed. EF provider translates these operations to the database specific SQL statements. We cannot control the logic of operations.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL