Cascade delete not deleting orphan children

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

[flycast]

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

[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.

[flycast]

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

[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.

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

[Shalex]

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;".

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]

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]

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.

If you delete the parent record then all the children are deleted?

Yes, all the children will be deleted.