Cascade delete is not working correctly in EF6 Code-first
Cascade delete is not working correctly in EF6 Code-first
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.
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.
Last edited by bairog on Thu 15 Nov 2018 08:33, edited 3 times in total.
Re: Cascade delete is not working correctly in EF6 Code-first
Hmm, I just succesfully decompressed it (WinRAR 5.21)
ok - here is .rar at dropbox
ok - here is .rar at dropbox
Re: Cascade delete is not working correctly in EF6 Code-first
So, did you obtain my source code?
Did you reproduced the problem?
Did you reproduced the problem?
Re: Cascade delete is not working correctly in EF6 Code-first
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
Hello again.
I've made some investigations. Even if I don't use Entity Framework and simply use ADO .NET:
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...
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();
}
}
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
Looks like my last post is irrelevant: according to http://sqlite.org/pragma.html#pragma_foreign_keys
If I execute PRAGMA foreign_keys = on; for each connection - cascade delete via ADO .NET is working...the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite.
Re: Cascade delete is not working correctly in EF6 Code-first
Please open \BlankDB.DAL\CommonClasses.cs and add the following line in the BuildConnectionString() method:
Now records in ExpressEvent are deleted with the record in FlightExpress.
Is that what you want?
Code: Select all
sb.RunOnceCommand = "PRAGMA foreign_keys = on";
Is that what you want?
Re: Cascade delete is not working correctly in EF6 Code-first
It's working now (both for EF 6 Code-First approach and for simple ADO .NET approach).Shalex wrote: ↑Fri 16 Nov 2018 21:04 Please open \BlankDB.DAL\CommonClasses.cs and add the following line in the BuildConnectionString() method:Now records in ExpressEvent are deleted with the record in FlightExpress.Code: Select all
sb.RunOnceCommand = "PRAGMA foreign_keys = on";
Is that what you want?
Thank you.