Why individual delete calls with delete on cascade

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
kussaued
Posts: 7
Joined: Fri 08 Jul 2011 06:39

Why individual delete calls with delete on cascade

Post by 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();
}

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Why individual delete calls with delete on cascade

Post by 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.

Post Reply