Bug #64779 Cascading delete using CreateDatabase in Entity Framework
Submitted: 27 Mar 2012 16:52 Modified: 24 Aug 2012 23:52
Reporter: Laurent Sibilla Email Updates:
Status: Closed Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.5.4 OS:Any
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any
Tags: entity framework

[27 Mar 2012 16:52] Laurent Sibilla
When using EntityFramework with MySQL .Net connector, the association property OnDelete is not taken into account in the CreateDatabaseScript function of the ObjectContext.

It's actually taken into account in the DDL generation wizard.

Here is the Exception dump:
System.Data.UpdateException was unhandled
  Message=An error occurred while updating the entries. See the inner exception for details.
       at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
       at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
       at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
       at System.Data.Objects.ObjectContext.SaveChanges()
       at ConsoleApplication1.Program.Main(String[] args) in c:\users\laurent privé\documents\visual studio 2010\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 31
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: MySql.Data.MySqlClient.MySqlException
       Message=Cannot delete or update a parent row: a foreign key constraint fails (`budget_test`.`entity2set`, CONSTRAINT `Entity1Entity2` FOREIGN KEY (`Entity1_Id`) REFERENCES `entity1set` (`Id`))
            at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
            at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
            at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
            at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
            at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
            at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
            at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
            at MySql.Data.Entity.EFMySqlCommand.ExecuteNonQuery()
            at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
            at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)

How to repeat:
1) Create a new project. Add references to System.Data.Entity.
2) Add a ADO.Net Entity Data Model "Model1.edmx"
3) Create two object associated with each other. For each of them create a string property called "Name".
4) Set property of the association such as :
    End1 Multiplicity = 1 of Entity1
    End1 OnDelete = Cascade
    End2 Multiplicity = * of Entity2
    End2 OnDelete = Cascade
5) Right click -> Generate Database from Model... And create a new MySQL Database
6) Copy this Main function, 
        static void Main(string[] args)
            using (Model1Container m = new Model1Container())
                if (m.DatabaseExists())

            using (Model1Container m = new Model1Container())
                Entity1 e1 = new Entity1() { Name = "test" };
                e1.Entity2.Add(new Entity2() { Name = "test" });
            using (Model1Container m = new Model1Container())

Suggested fix:
Here are the piece of SQL code generated with the wizard :
-- Creating foreign key on `Budget_Id` in table 'Accounts'

ALTER TABLE [dbo].[Entity2Set]
ADD CONSTRAINT [FK_Entity1Entity2]
    FOREIGN KEY ([Entity1_Id])
    REFERENCES [dbo].[Entity1Set]

And here is the one generated by CreateDatabaseScript function :

ALTER TABLE `Entity2Set` ADD KEY (`Entity1_Id`);
ALTER TABLE `Entity2Set` ADD CONSTRAINT Entity1Entity2
	 FOREIGN KEY (Entity1_Id)
	REFERENCES Entity1Set (Id);

I suggest to add this at the end of the GetAssociationCreateScript:

        sql.Append(String.Format("\tREFERENCES {0} (", _pluralizedNames[parentType.Name]));
        foreach (EdmProperty p in a.ReferentialConstraints[0].FromProperties)
          EdmMember member;
          if (!parentType.KeyMembers.TryGetValue(p.Name, false, out member))
                "ALTER TABLE `{0}` ADD KEY (`{1}`);", _pluralizedNames[parentType.Name], p.Name));
          sql.AppendFormat("{0}{1}", delimiter, p.Name);
          delimiter = ", ";
+        sql.AppendLine(String.Format("\tON DELETE {0} ON UPDATE {1}",
+            a.AssociationEndMembers[0].DeleteBehavior == OperationAction.Cascade ? "CASCADE" : "NO ACTION",
+            "NO ACTION"));

      return keySql.ToString();

Note that I haven't been able to build the solution.
[27 Mar 2012 17:13] Laurent Sibilla
Issue solution example

Attachment: ConsoleApplication1.zip (application/zip, text), 46.44 KiB.

[27 Mar 2012 17:14] Laurent Sibilla
I have added a solution that show the problem.
[24 Aug 2012 23:52] John Russell
Added to changelog for 6.4.6, 6.5.5, 6.6.0: 

When using Entity Framework with Connector/Net, the association
property OnDelete was not taken into account in the
CreateDatabaseScript function of the ObjectContext, leading to an
error message System.Data.UpdateException was unhandled. The SQL
generated by the CreateDatabaseScript function was missing ON DELETE
and ON UPDATE clauses. These clauses were filled in correctly by the
DDL generation wizard.