Page 1 of 1

Cascade delete is not working correctly in EF6 Code-first

Posted: Thu 25 Oct 2018 05:58
by bairog
Hello.
I'm using EF6 Code-first and I have Entites with cascade deleting enabled. Database is genereated correctly (cascade delete is enabled fo relations). Database schema is the following:
Image
When I delete FlightExpress I expect cascade delete for all related ExpressEvents, EventParameters and ParameterValues. But in fact that is working very strange:
- if I put a breakpoint after my delete method (line 27 in BlankDB.DAL.Test\Program.cs) - only FlightExpress is deleted from database. That is working that way even if I begin a transaction in DbAccess.DbStartBlock method and commit it in DbAccess.DbFinallyBlock method.
- if I put a breakpoint after DbAccess.Context.Expresses.Remove(express) but before DbAccess.DbFinallyBlock where I close connection to database and perform SaveChanges() (line 85 in BlankDB.DAL\BlankDB.Interop.cs) and stand on it for some time - FlightExpress with related ExpressEvents, EventParameters and ParameterValues are deleted.
Looks like database (or your driver) needs some wait time before connection closing. But howto calculate it?

I use dotConnect for SQLite 5.11.1202 and EF6.

I've uploaded sample project here. Set BlankDB.DAL.Test as startup project, compile and run.

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Fri 26 Oct 2018 18:52
by Shalex
bairog wrote: Thu 25 Oct 2018 05:58I've uploaded sample project here.
We are getting "unexpected end of archive" when trying to decomprex it. Could you please rezip your project and upload it to Dropbox?

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Sat 27 Oct 2018 06:04
by bairog
Hmm, I just succesfully decompressed it (WinRAR 5.21)
ok - here is .rar at dropbox

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Tue 06 Nov 2018 08:51
by bairog
So, did you obtain my source code?
Did you reproduced the problem?

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Thu 08 Nov 2018 20:02
by Shalex
We have successfully downloaded your source code and are investigating the project. We will notify you about the result.

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Thu 15 Nov 2018 06:05
by bairog
Hello again.
I've made some investigations. Even if I don't use Entity Framework and simply use ADO .NET:

Code: Select all

using (SQLiteConnection conn = new SQLiteConnection(mySQLiteConnectionStringBuilder.ToString()))
private void DeleteFlightExpressADONET(Guid Id)
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
  cmd.CommandText = "DELETE FlightExpress WHERE Id=@1";
  cmd.Parameters.AddWithValue("1", Id);
  conn.Open();
  var rowsAffected = cmd.ExecuteNonQuery();
  conn.Close();
}
}
cascade delete still not working (only FlightExpress is deleted).
While all SQLite manager utilities (SQLIteStudio, SQLite Expert, etc.) handle that SQL script correctly and also delete corresponding ExpressEvents, EventParameters and ParameterValues.
Maybe this is the core of the problem...

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Thu 15 Nov 2018 08:32
by bairog
Looks like my last post is irrelevant: according to http://sqlite.org/pragma.html#pragma_foreign_keys
..the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite.
If I execute PRAGMA foreign_keys = on; for each connection - cascade delete via ADO .NET is working.

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Fri 16 Nov 2018 21:04
by Shalex
Please open \BlankDB.DAL\CommonClasses.cs and add the following line in the BuildConnectionString() method:

Code: Select all

sb.RunOnceCommand = "PRAGMA foreign_keys = on";
Now records in ExpressEvent are deleted with the record in FlightExpress.

Is that what you want?

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Fri 16 Nov 2018 21:05
by Shalex

Re: Cascade delete is not working correctly in EF6 Code-first

Posted: Mon 19 Nov 2018 15:45
by bairog
Shalex wrote: Fri 16 Nov 2018 21:04 Please open \BlankDB.DAL\CommonClasses.cs and add the following line in the BuildConnectionString() method:

Code: Select all

sb.RunOnceCommand = "PRAGMA foreign_keys = on";
Now records in ExpressEvent are deleted with the record in FlightExpress.

Is that what you want?
It's working now (both for EF 6 Code-First approach and for simple ADO .NET approach).
Thank you.