EF Core无法创建带SET NULL的外键FK_ApplicationInfos_UserProfiles_UserProfileId如何解决？

Hey there, let's break down why you're running into this foreign key issue and how to fix it. The core problem here is a mismatch between your model's field nullability and the delete behavior EF Core is trying to apply.

What's Causing the Error? #

Your ApplicationInfo class has a non-nullable int UserProfileId field, but EF Core is attempting to create a foreign key with a SET NULL reference action. Since non-nullable fields can't be set to NULL, the database throws an error when trying to create this constraint.

Even though you configured OnDelete(DeleteBehavior.Cascade) in OnModelCreating, there are a couple of reasons EF might be ignoring this:

• The UserProfile navigation property in ApplicationInfo isn't marked as required, so EF assumes the relationship is optional (and defaults to SET NULL).
• Your Fluent API configuration doesn't explicitly specify the foreign key field, leading EF to infer it incorrectly.

Step-by-Step Fixes #

1. Correct Model Nullability

First, ensure your ApplicationInfo model enforces that every application must belong to a user profile. Add the [Required] attribute to the UserProfile navigation property (this reinforces the non-nullable UserProfileId):

public class ApplicationInfo {
    [Key]
    public int Id { get; set; }
    // ... other properties ...
    // Keep UserProfileId non-nullable
    public int UserProfileId { get; set; }
    // Mark the navigation property as required
    [Required]
    public UserProfile UserProfile { get; set; }
    public IEnumerable<Review> Reviews { get; set; }
}

2. Clarify Fluent API Configuration

Update your OnModelCreating code to explicitly specify the foreign key field for the ApplicationInfo ↔ UserProfile relationship. This removes any ambiguity for EF:

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    // User ↔ UserProfile one-to-one cascade delete
    modelBuilder.Entity<User>()
        .HasOne(u => u.UserProfile)
        .WithOne(up => up.User)
        .HasForeignKey<UserProfile>(up => up.UserId)
        .OnDelete(DeleteBehavior.Cascade);

    // ApplicationInfo ↔ UserProfile one-to-many cascade delete (explicit foreign key)
    modelBuilder.Entity<ApplicationInfo>()
        .HasOne(ai => ai.UserProfile)
        .WithMany(up => up.ApplicationInfos)
        .HasForeignKey(ai => ai.UserProfileId) // Explicitly define the foreign key
        .OnDelete(DeleteBehavior.Cascade);

    // Review ↔ UserProfile one-to-many cascade delete
    modelBuilder.Entity<Review>()
        .HasOne(r => r.UserProfile)
        .WithMany(up => up.Reviews)
        .OnDelete(DeleteBehavior.Cascade);

    // Review ↔ ApplicationInfo one-to-many cascade delete
    modelBuilder.Entity<Review>()
        .HasOne(r => r.ApplicationInfo)
        .WithMany(ai => ai.Reviews)
        .OnDelete(DeleteBehavior.Cascade);
}

3. Clean Up Old Migrations (Critical!)

If you've already generated migration files before fixing this, those files likely contain the incorrect SET NULL configuration. To resolve this:

• Delete all existing migration files in your project's Migrations folder.
• If you're working in a development environment, delete the database entirely (or drop the problematic foreign key constraint manually if you need to preserve data).
• Recreate the initial migration:

  Add-Migration InitialCreate
  

• Update your database to apply the corrected schema:

  Update-Database
  

Why This Works #

By making the UserProfile navigation property required and explicitly defining the foreign key in Fluent API, EF Core now understands that the relationship is mandatory. It will generate a foreign key constraint with ON DELETE CASCADE instead of SET NULL, which aligns with your intended behavior and works with the non-nullable UserProfileId field.

内容的提问来源于stack exchange，提问作者Marc