Description:
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.
Source=System.Data.Entity
StackTrace:
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`))
Source=MySql.Data
ErrorCode=-2147467259
Number=1451
StackTrace:
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)
InnerException:
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())
m.DeleteDatabase();
m.CreateDatabase();
m.SaveChanges();
}
using (Model1Container m = new Model1Container())
{
Entity1 e1 = new Entity1() { Name = "test" };
m.AddToEntity1Set(e1);
e1.Entity2.Add(new Entity2() { Name = "test" });
m.SaveChanges();
}
using (Model1Container m = new Model1Container())
{
m.DeleteObject(m.Entity1Set.FirstOrDefault());
m.SaveChanges();
}
}
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]
([Id])
ON DELETE CASCADE ON UPDATE NO ACTION;
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))
keySql.AppendLine(String.Format(
"ALTER TABLE `{0}` ADD KEY (`{1}`);", _pluralizedNames[parentType.Name], p.Name));
sql.AppendFormat("{0}{1}", delimiter, p.Name);
delimiter = ", ";
}
sql.AppendLine(");");
+ sql.AppendLine(String.Format("\tON DELETE {0} ON UPDATE {1}",
+ a.AssociationEndMembers[0].DeleteBehavior == OperationAction.Cascade ? "CASCADE" : "NO ACTION",
+ "NO ACTION"));
sql.AppendLine();
}
keySql.Append(sql.ToString());
return keySql.ToString();
}
Note that I haven't been able to build the solution.
Description: 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. Source=System.Data.Entity StackTrace: 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`)) Source=MySql.Data ErrorCode=-2147467259 Number=1451 StackTrace: 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) InnerException: 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()) m.DeleteDatabase(); m.CreateDatabase(); m.SaveChanges(); } using (Model1Container m = new Model1Container()) { Entity1 e1 = new Entity1() { Name = "test" }; m.AddToEntity1Set(e1); e1.Entity2.Add(new Entity2() { Name = "test" }); m.SaveChanges(); } using (Model1Container m = new Model1Container()) { m.DeleteObject(m.Entity1Set.FirstOrDefault()); m.SaveChanges(); } } 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] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION; 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)) keySql.AppendLine(String.Format( "ALTER TABLE `{0}` ADD KEY (`{1}`);", _pluralizedNames[parentType.Name], p.Name)); sql.AppendFormat("{0}{1}", delimiter, p.Name); delimiter = ", "; } sql.AppendLine(");"); + sql.AppendLine(String.Format("\tON DELETE {0} ON UPDATE {1}", + a.AssociationEndMembers[0].DeleteBehavior == OperationAction.Cascade ? "CASCADE" : "NO ACTION", + "NO ACTION")); sql.AppendLine(); } keySql.Append(sql.ToString()); return keySql.ToString(); } Note that I haven't been able to build the solution.