Cascade delete is not working correctly in EF6 Code-first

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

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.

[Shalex]

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]

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

[bairog]

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

[Shalex]

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

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

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

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

[Shalex]

Refer to https://www.devart.com/dotconnect/sqlit ... mbers.html.

[bairog]

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.