Why individual delete calls with delete on cascade?

Question

I've got two tables with an 1:n relation (one animal has many animal_events) and a delete on cascade in my database:

CREATE TABLE animals (
   Id             INTEGER         PRIMARY KEY   AUTO_INCREMENT,
   Description    VARCHAR(512),
   Timestamp      TIMESTAMP       
);

CREATE TABLE animal_events (
   Id            INTEGER         PRIMARY KEY   AUTO_INCREMENT,
   Begin         DATETIME      NOT NULL,
   AnimalId      INT,
   Timestamp     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?

DELETE FROM rfidfarm.animal_events WHERE Id = 1 
DELETE FROM rfidfarm.animal_events WHERE Id = 2 
DELETE FROM rfidfarm.animal_events WHERE Id = 3 

DELETE FROM animals WHERE Id = 1

Isn't this superfluous, since the database would take care of animal_events or is it just a bug in my EF setup/configuration?

Updates

Extract for the edml file

<Association Name="animal_events_ibfk_2">
  <End Role="animals" Type="Model.Store.animals" Multiplicity="0..1">
    <OnDelete Action="Cascade" />
  </End>

If I use this test code, I see many individual deletes:

using (var uow = _modelFactory.CreateUnitOfWork())
{
    var animal = uow.Animals.GetById(2);
    var animalEventCount = animal.AnimalEvents.Count;
    uow.Animals.Delete(animal);
    uow.SaveChanges();
}

If I use this code there is only one delete as hoped for:

using (var uow = _modelFactory.CreateUnitOfWork())
{
    var animal = uow.Animals.GetById(2);
    uow.Animals.Delete(animal);
    uow.SaveChanges();
}

Well that kind of depends on how you actually delete these records, and how you generated your DbContext (assuming EF database first). — CodeCaster, Sep 21 '16 at 10:47
Does your DbContext know about the cascade rules? Didn't you add the cascades after generating the DbContext? — CodeCaster, Sep 21 '16 at 10:49
As for the creation, I used the devart DbContext template to generate teh classes (database first). — nabulke, Sep 21 '16 at 10:50
@CodeCaster: The DbContext knows about the cascades, at least I see some `Action=Cascade` in the edml file. I didn't add any cacade rules after the generation. — nabulke, Sep 21 '16 at 10:52
What table engine do you use in MySQL? Innodb? Myisam? Sg else? — Shadow, Sep 21 '16 at 10:59
I've got different behavior if I access the animal_events before deletion (see my update in the question). Is lazy loading doing something here? — nabulke, Sep 21 '16 at 11:11

Why individual delete calls with delete on cascade?

0 Answers0