Why must I access the related fields of a user before I can delete them in ASP.NET MVC EF app?

Question

There is a ApplicationUser. They can have multiple TfsAccounts and one TfsToken. A TfsAccount can have multiple TrackedTasks.

public class ApplicationUser : IdentityUser
{
    public virtual ICollection<TfsAccount> TfsAccounts { get; set; }
    public virtual TfsToken TfsToken { get; set; }
}

public class TfsAccount
{
    [ForeignKey("ApplicationUserId")]
    public virtual ApplicationUser ApplicationUser { get; set; }

    public string ApplicationUserId { get; set; }

    public virtual ICollection<TrackedTask> TrackedTasks { get; set; }
}

public class TrackedTask
{
    [ForeignKey("TfsAccountId")]
    public virtual TfsAccount TfsAccount { get; set; }

    public int TfsAccountId { get; set; }
}

Now, I have a method to cancel a subscription for a user and delete them:

[HttpGet]
public async Task<ActionResult> CancelSubscription()
{
    var currentUser = UserManager.FindById(User.Identity.GetUserId());

    var tfsAccounts = currentUser.TfsAccounts;  <---REMOVE THESE
    var tfsToken = currentUser.TfsToken;        <---TWO LINES AND IT BREAKS

    var result = await UserManager.DeleteAsync(currentUser);  <---ON THIS LINE

    AuthenticationManager.SignOut();
    return RedirectToAction("CancellationConfirmed");
}

Here is the error I get in the browser:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.TfsAccounts_dbo.AspNetUsers_ApplicationUserId". The conflict occurred in database "TfsTeamStatus", table "dbo.TfsAccounts", column 'ApplicationUserId'.
The statement has been terminated.

Why do I have to access the related fields on the user before I can delete the user? This works but feels super hacky.

Answer 1

That's super hacky. Correct. You need to set on delete cascade option to set for that.

If you are using EF Designer, then you can do that using EF Designer as per these steps

1. Set Cascade on relation in EF designer. This instruct context that all loaded related entities must be deleted prior to deletion of the parent entity. If this doesn't happen EF will throw exception because internal state will detect that loaded childs are not related to any existing parent entity even the relation is required. I'm not sure if this happens before execution of delete statement of the parent entity or after but there is no difference. EF doesn't reload related entities after executing modifications so it simply doesn't know about cascade deletes triggered in the database.
2. Set ON CASCADE DELETE on relation in database. This will instruct SQL to delete all related records which were not loaded to context in the time of deleting the parent.

If you are doing code first approach, you can do that using Fluent API as per [Entity Framework (EF) Code First Cascade Delete for One-to-Zero-or-One relationship

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<User>()
      .HasOptional(a => a.UserDetail)
      .WithOptionalDependent()
      .WillCascadeOnDelete(true);
}

Now, coming to your point, I am not sure why it works with the two lines when you access TfsAccount and TfsToken, but I guess EF might be setting Modified flag when you accessed it. So while doing SaveChanges it might be deleting those entities. - It's just a guess.