Cascade delete not deleting orphan children

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
flycast
Posts: 15
Joined: Mon 04 Feb 2019 19:39

Cascade delete not deleting orphan children

Post by flycast » Mon 11 Feb 2019 01:10

When I delete the parent record it leaves the children records hanging with a key value in the foreign key. If I delete all the child records it leaves the parent record hanging. I am expecting that when the last record is deleted on either side then the associated records will delete. Delete cascaded is set to true in the association. Both key fields are Int32. The table SQL looks like this in SQLite Browser:

Code: Select all

CREATE TABLE `FirstParts` (
	`FirstPartID`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`DateTime`	TEXT,
	`ToolID`	INT32 NOT NULL,
	CONSTRAINT `FK_FirstParts_Tools` FOREIGN KEY(`ToolID`) REFERENCES `Tools`(`ToolID`) ON DELETE CASCADE
);

Code: Select all

CREATE TABLE `Tools` (
	`ToolID`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`ToolNumber`	TEXT
);
My code looks like this:

Code: Select all

            using (var context = new Entities())
            {
                var query = (from t in context.Tools
                             select t).FirstOrDefault();

                if (query != null)
                {
                    context.Tools.DeleteObject(query);
                    context.SaveChanges();
                }
            }

flycast
Posts: 15
Joined: Mon 04 Feb 2019 19:39

Re: Cascade delete not deleting orphan children

Post by flycast » Tue 12 Feb 2019 15:26

No thoughts on this? Why would my child records not be deleted?

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

Re: Cascade delete not deleting orphan children

Post by Shalex » Wed 13 Feb 2019 19:50

Please, open Association Editor in Entity Developer and make sure that the "Delete Cascaded" option is selected. Save the model to regenerate the code. Does this help? If not, send us a small test project for reproducing the issue.

flycast
Posts: 15
Joined: Mon 04 Feb 2019 19:39

Re: Cascade delete not deleting orphan children

Post by flycast » Wed 13 Feb 2019 21:43

Cascade is already checked. I will try to duplicate in a small test project.

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

Re: Cascade delete not deleting orphan children

Post by Shalex » Wed 20 Feb 2019 17:03

Please add "Foreign Key Constraints=On;" to your connection string to fix the issue. Refer to https://www.devart.com/dotconnect/sqlit ... tring.html.

flycast
Posts: 15
Joined: Mon 04 Feb 2019 19:39

Re: Cascade delete not deleting orphan children

Post by flycast » Mon 25 Feb 2019 16:35

This did not work for me.
I have found connection strings in three different places.

  • DataModel.edps under <Connection />
  • In DataModel1.Designer.cs in public Entities()
  • In app.config. My model was not set to use app config so I deleted that connection string.
I added "Foreign Key Constraints=On;" to both connection strings and it was ignored. When I delete the last child from the child table the record in the parent table is still not deleted.

My connection strings look like this:
DataModel:

Code: Select all

    <Connection ConnectionString="Data Source=C:\Users\erics\source\repos\FLIR\FLIR\Testing.db;FailIfMissing=False;Foreign Key Constraints=On;" Provider="Devart.Data.SQLite" />
DataModel1.Designer.cs:

Code: Select all

        /// <summary>
        /// Initialize a new Entities object.
        /// </summary>
        public Entities() : 
                base(@"metadata=DataModel1.csdl|DataModel1.ssdl|DataModel1.msl;provider=Devart.Data.SQLite;provider connection string=""Data Source=C:\Users\erics\source\repos\FLIR\FLIR\Testing.db;FailIfMissing=False;Foreign Key Constraints=On;""", "Entities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
        }

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

Re: Cascade delete not deleting orphan children

Post by Shalex » Tue 26 Feb 2019 14:32

flycast wrote:
Mon 11 Feb 2019 01:10
When I delete the parent record it leaves the children records hanging with a key value in the foreign key.
The issue is fixed by "Foreign Key Constraints=On;".
flycast wrote:
Mon 25 Feb 2019 16:35
When I delete the last child from the child table the record in the parent table is still not deleted.
This is designed behavior.

flycast
Posts: 15
Joined: Mon 04 Feb 2019 19:39

Re: Cascade delete not deleting orphan children

Post by flycast » Tue 26 Feb 2019 14:49

So perhaps I do not understand?

Using Foreign Key Constraints=On;

If you delete the last child record then the parent is left in the parent table?
If you delete the parent record then all the children are deleted?

Pinturiccio
Devart Team
Posts: 2406
Joined: Wed 02 Nov 2011 09:44

Re: Cascade delete not deleting orphan children

Post by Pinturiccio » Mon 04 Mar 2019 13:07

flycast wrote:If you delete the last child record then the parent is left in the parent table?
Yes, the parent is left in the parent table.
flycast wrote:If you delete the parent record then all the children are deleted?
Yes, all the children will be deleted.

Post Reply