Question: How do I get EF 4.1 to specify the ON DELETE CASCADE option on the foreign key constraint using the fluent api for base tables? I know how to do it with relationships to other tables, but how do I have it generate this for TPT (Table Per Type) tables?
Description:
Let me point out, I am not referring to foreign key relationships. With my DbContext I always use Mapping objects for my entities, only because in most cases I prefer to be explicit as opposed to accepting the convention approach. That being said, all the configuration for my TPT tables are being handled in the EntityTypeConfiguration<SomeEntityClass> classes.
When I define a TPT relationship by creating a new class which derives from another, the ON DELETE CASCADE does not get generated in the SQL constraint, which is the problem.
Have a look at the following code...
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}
public class OtherPerson : Person
{
public string SomeOtherProperty { get; set; }
}
public class PersonMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Person>
{
public PersonMap()
{
this.HasKey(t => t.PersonId); // Primary Key
this.Property(t => t.PersonId)
.HasColumnName("PersonId") // Explicitly set column name
.IsRequired() // Field is required / NOT NULL
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); // Specify as Identity (Not necessary, but I'm explicit)
this.Property(t => t.Name)
.HasColumnName("Name") // Explicitly set column name
.IsRequired() // Field is required / NOT NULL
.HasMaxLength(50); // Max Length
this.ToTable("People"); // Map to table name People
}
}
public class OtherPersonMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<OtherPerson>
{
public OtherPersonMap()
{
this.Property(t => t.SomeOtherProperty)
.HasColumnName("SomeOtherProperty") // Explicitly set column name
.IsRequired() // Field is required / NOT NULL
.HasMaxLength(10); // Max Length
this.ToTable("OtherPeople"); /* Map to table name OtherPeople
* This also causes TPT to create a shared primary key from the base table
* and double serving as a foreign key to base table.
*/
}
The above code is really simple. I have 2 types and they are correctly create in the database. If I create a new OtherPerson and save to the database it correctly creates 2 records, 1st a record in the People table and another in the OtherPeople table with a shared primary key which is also the foreign key from OtherPeople to People.
Now, the DbContext or EF rather, correctly deletes both records if I delete the OtherPerson within my code. However, should I delete the record directly from the database, an orphan record is left behind in the People table.
So, how do I get the ON DELETE CASCADE to be specified for the foreign key constraints generated for base tables using the fluent api?
Sorry the question is so long but I just wanted to describe the best I could my problem. Thanks in advance.
