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
Triage: D3 (Medium)

[28 Jan 2010 20:14] Nathan Taylor
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.
[3 Feb 2010 9:52] Tonci Grgin
My model

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

[3 Feb 2010 9:55] Tonci Grgin
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.
[4 Feb 2010 20:51] Nathan Taylor
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.
[4 Feb 2010 21:16] Tonci Grgin
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.
[4 Feb 2010 21:19] Nathan Taylor
My pleasure, I am happy to help and I appreciate your feedback!
[19 Feb 2010 20:11] Reggie Burnett
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.
[22 Feb 2010 7:20] Tonci Grgin
Thanks Reggie. I will assign Tony now to clearly document your findings in manual.