Cascade delete is not working correctly in EF6 Code-first

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 112
Joined: Mon 29 Apr 2013 09:05

Cascade delete is not working correctly in EF6 Code-first

Post by bairog » Thu 25 Oct 2018 05:58

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.
Last edited by bairog on Thu 15 Nov 2018 08:33, edited 3 times in total.

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

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

Post by Shalex » Fri 26 Oct 2018 18:52

bairog wrote:
Thu 25 Oct 2018 05:58
I'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?

bairog
Posts: 112
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Sat 27 Oct 2018 06:04

Hmm, I just succesfully decompressed it (WinRAR 5.21)
ok - here is .rar at dropbox

bairog
Posts: 112
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Tue 06 Nov 2018 08:51

So, did you obtain my source code?
Did you reproduced the problem?

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

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

Post by Shalex » Thu 08 Nov 2018 20:02

We have successfully downloaded your source code and are investigating the project. We will notify you about the result.

bairog
Posts: 112
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Thu 15 Nov 2018 06:05

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

bairog
Posts: 112
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Thu 15 Nov 2018 08:32

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.

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

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

Post by Shalex » 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?


bairog
Posts: 112
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Mon 19 Nov 2018 15:45

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.

Post Reply