EF 4.1 Cascading in two directions between entities with two relationships

Question

I have the following two entities:

public class Tournament {
    public int TournamentID { get; set; }
    public String Name { get; set; }
    public int? OfficialID { get; set; }

    public virtual Official HeadOfficial { get; set; }

    public virtual ICollection<Official> Officials { get; set; }
}

public class Official {
    public int OfficialID { get; set; }
    public String Surname { get; set; }
    public String FirstName { get; set; }
    public int TournamentID { get; set; }

    public virtual Tournament Tournament { get; set; }
}

A tournament can have 0..N officials and an official HAS to have a tournament linked to it. The second relationship is that a tournament may have a headofficial.

To make the EF properly interpret this when making the database I have the following:

    protected override void  OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Tournament>()
            .HasOptional(t => t.HeadOfficial)
            .WithMany()
            .HasForeignKey(t => t.OfficialID);

        modelBuilder.Entity<Tournament>()
            .HasMany(t => t.Officials)
            .WithRequired(o => o.Tournament)
            .HasForeignKey(o => o.TournamentID);        
    }

If I now delete a tournament, all the officials that were linked to that tournament are deleted, which is what I want. However, when I delete an official that has been made head official in one of the tournaments, the delete doesn't go through citing that the delete statement conflicted with the reference constraint in Tournaments.OfficialID.

Answer 1

If I now delete a tournament, all the officials that were linked to that tournament are deleted, which is what I want. However, when I delete an official that has been made head official in one of the tournaments, the delete doesn't go through citing that the delete statement conflicted with the reference constraint in Tournaments.OfficialID.

I think these are quite different situations. The first case works because there is a cascading delete setup in the database (EF did create this rule in the database because the relationship is required, that's default behaviour).

Your second relationship is optional, so there is no cascading delete by default. More important: I don't think that you want cascading delete because it would mean that deleting an official would also delete the tournaments which have this official as head.

I can only imagine that you want to set the HeadOfficial reference to null if the official gets deleted. But there is no automatic way (like for cascading delete). You must program such a behaviour, for example:

using (var ctx = new MyContext())
{
    var officialToDelete = ctx.Officials.Single(o => o.OfficialID == 5);
    var tournaments = ctx.Tournaments.Where(t => t.OfficialID == 5).ToList();

    foreach (var tournament in tournaments)
        tournament.OfficialID = null;

    ctx.Officials.Remove(officialToDelete);

    ctx.SaveChanges();
}

I believe that the foreach loop is not necessary because EF will fixup the relationships (= set the foreign key to null) when you call Remove. (Basically EF itself does this loop internally.) But it is important that the tournaments are loaded into the context because EF cares about this relationship fixup (only for attached (=loaded) entities), not the database (in contrast to the cascading delete).