MySQL Bugs: #50698: Entity Framework - Cascade On Delete Ommitted from CSDL Output

Bug #50698	Entity Framework - Cascade On Delete Ommitted from CSDL Output
Submitted:	28 Jan 2010 20:14	Modified:	19 Feb 2010 20:11
Reporter:	Nathan Taylor	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S2 (Serious)
Version:	6.1.2	OS:	Any
Assigned to:	Tony Bedford	CPU Architecture:	Any
Tags:	bug, cascade, entity framework, foreign key, on delete, ssdl

Description:
There appears to be a bug in the .NET connector for MySQL which, when used with Entity Framework, causes important and necessary information to be omitted from the generated database context. If foreign keys relationships are defined with on delete or on update actions specified, the necessary <OnDelete /> / <OnUpdate /> declarations are not included in the CSDL (or SSDL) section of the generated Entity Framework markup.

Example:

Table1 has a 1-To-Many relatioship with Table2 and cascade on delete is set in the Table2 foreign key referencing Table1's ID column.

The markup generated by the Entity Framework tool (used with the MySQL connector) looks something like:

<Association Name="FK_Table2_Table1">
  <End Role="Table1" Type="Entities.Table1" Multiplicity="1" />
  <End Role="Table2" Type="Entities.Table2" Multiplicity="*" />
</Association>

Adhering to Table2 foreign key constraint in the database, the above markup will produce an error when trying to delete a Table1 entity as it will not issue the cascade on delete instruction to the *local object context*:

A relationship is being added or deleted from an AssociationSet 'FK_Table2_Table1'. With cardinality constraints, a corresponding 'Table2' must also be added or deleted.

This problem can be mitigated by manually updating the CSDL definition to the form of:

<Association Name="FK_Table2_Table1">
  <End Role="Table1" Type="Entities.Table1" Multiplicity="1">
	<OnDelete Action="Cascade" />
  </End>
  <End Role="Table2" Type="Entities.Table2" Multiplicity="*" />
</Association>

Although this stops the error from occurring, this really is not an acceptable solution as it must be performed individually for each qualifying table relationship every time the CSDL content is refreshed- not very scalable.

How to repeat:
1. Create a table relationship with on update and on delete actions defined in the foreign key.
2. Generate a new Entity Framework context including the table relationship.

Suggested fix:
Ensure the required <OnDelete /> and <OnCascade /> definitions are included in the generated CSDL markup.

My model

Attachment: TestModel.edmx (application/octet-stream, text), 14.73 KiB.

Hi Nathan and thanks for your report.

Using latest c/NET sources I see some improvement in this area (check the attached model):
        <Association Name="fkey">
          <End Role="bug50698_1" Type="TestModel.Store.bug50698_1" Multiplicity="1">
            <OnDelete Action="Cascade" />
          </End>

My test tables:
CREATE TABLE bug50698_1 (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE bug50698_2 (
`foreign_id` int(10) unsigned NOT NULL,
CONSTRAINT `fkey` FOREIGN KEY (`foreign_id`) REFERENCES `bug50698_1` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

mysql> insert into bug50698_1 values (null), (null), (null);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into bug50698_2 values (1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into bug50698_2 values (2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into bug50698_2 values (3);
Query OK, 1 row affected (0.01 sec)

mysql> update bug50698_1 set id = 5 where id = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from  bug50698_1;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+
3 rows in set (0.00 sec)

mysql> select * from  bug50698_2;
+------------+
| foreign_id |
+------------+
|          1 |
|          3 |
|          5 |
+------------+
3 rows in set (0.00 sec)

mysql> delete from bug50698_1 where id = 5;
Query OK, 1 row affected (0.05 sec)

mysql> select * from  bug50698_1;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

mysql> select * from  bug50698_2;
+------------+
| foreign_id |
+------------+
|          1 |
|          3 |
+------------+
2 rows in set (0.00 sec)

So, all I'm missing in c/NET 6.2.2 is the "OnUpdate Action". Please retest with c/NET 6.2.2 and inform me of your findings.

I just did some tests with version 6.2.2 and found that it is including the correct <OnDelete /> declarations in the output. Now they just need to add the <OnUpdate /> support. 

Very nice.

Nathan, we've already discussed this problem yesterday. I can not guarantee when it will be fixed but at least we now know exactly what's wrong.

Thanks again for nice catch.

My pleasure, I am happy to help and I appreciate your feedback!

I was just informed by a program manager for Microsoft that entity framework does not contain the concept of ON UPDATE CASCADE.  I also setup a test with VS 2010 and Sql Server and verified that it also does not generate any form of <OnUpdate> element.

It does create a referentialconstraint element but I checked to see if that implements on update cascade by write a simple linq app that attempted to change the key of a parent child relationship.  It threw an exception saying that I was not allowed to change the key.

It appears as of right now that on update cascade is not implemented by EF.

Thanks Reggie. I will assign Tony now to clearly document your findings in manual.