Page 1 of 1

Cascade delete not deleting orphan children

Posted: Mon 11 Feb 2019 01:10
by flycast
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();
                }
            }

Re: Cascade delete not deleting orphan children

Posted: Tue 12 Feb 2019 15:26
by flycast
No thoughts on this? Why would my child records not be deleted?

Re: Cascade delete not deleting orphan children

Posted: Wed 13 Feb 2019 19:50
by Shalex
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.

Re: Cascade delete not deleting orphan children

Posted: Wed 13 Feb 2019 21:43
by flycast
Cascade is already checked. I will try to duplicate in a small test project.

Re: Cascade delete not deleting orphan children

Posted: Wed 20 Feb 2019 17:03
by Shalex
Please add "Foreign Key Constraints=On;" to your connection string to fix the issue. Refer to https://www.devart.com/dotconnect/sqlit ... tring.html.

Re: Cascade delete not deleting orphan children

Posted: Mon 25 Feb 2019 16:35
by flycast
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();
        }

Re: Cascade delete not deleting orphan children

Posted: Tue 26 Feb 2019 14:32
by Shalex
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.

Re: Cascade delete not deleting orphan children

Posted: Tue 26 Feb 2019 14:49
by flycast
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?

Re: Cascade delete not deleting orphan children

Posted: Mon 04 Mar 2019 13:07
by Pinturiccio
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.