I am working on an ASP.NET Core project using code first I want to alter database design. I have a case were I need to add two foreign keys to the same table, even I used DeleteBehavior.Restrict, still the error occurred when I update database.

Models:

 public class Languages
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Projects> PFromLanguages { get; set; }
        public virtual ICollection<Projects> PToLanguages { get; set; }
    }

public class Projects
    {
        public int Id { get; set; }
        public int FromLanguageId { get; set; }
        public int ToLanguageId { get; set; }

        public virtual Languages FromLanguage { get; set; }
        public virtual Languages ToLanguage { get; set; }

    }

Db Context:

  protected override void OnModelCreating(ModelBuilder builder)
  {

            builder.Entity<Projects>()
                .HasOne(p => p.FromLanguage)
                .WithMany(b => b.PFromLanguages)
                .HasForeignKey(b => b.FromLanguageId)
                .OnDelete(DeleteBehavior.Restrict);

            builder.Entity<Projects>()
                .HasOne(p => p.ToLanguage)
                .WithMany(b => b.PToLanguages)
                .HasForeignKey(b => b.ToLanguageId)
                .OnDelete(DeleteBehavior.Restrict);
  }

Migration:

 protected override void Up(MigrationBuilder migrationBuilder)
 {
           migrationBuilder.AddForeignKey(
                name: "FK_Projects_Languages_FromLanguageId",
                table: "Projects",
                column: "FromLanguageId",
                principalTable: "Languages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_Projects_Languages_ToLanguageId",
                table: "Projects",
                column: "ToLanguageId",
                principalTable: "Languages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
  }

Error:

Failed executing DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [Projects] ADD CONSTRAINT [FK_Projects_Languages_FromLanguageId] FOREIGN KEY ([FromLanguageId]) REFERENCES [Languages] ([Id]) ON DELETE NO ACTION;

CodePudding user response：

I am working on an ASP.NET Core project using code first I want to alter database design. I have a case were I need to add two foreign keys to the same table, even I used DeleteBehavior.Restrict, still the error occurred when I update database

This is officially known issue. Because Primary keys are mapped by default convention please have a look here. You can't have single collection referenced by two FKs. Projects is mapped without cascading delete because it doesn't work in these self referencing many-to-many. Therefore, We have two ways to handle this scenario thus the error:

1. Change one or more of the relationships to not cascade delete.

In this scenario, we could make the Projects relationship with Languages optional by giving it a nullable foreign key property: for instance we can do something like:

.WillCascadeOnDelete(false);// Or you can try
.IsRequired(false)

Note: Either of one .WillCascadeOnDelete(false); or .IsRequired(false) should resolve your issue. You can check our official document for more details.

2. Configure the database without one or more of these cascade deletes, then ensure all dependent entities are loaded so that EF Core can perform the cascading behavior.

Considering this appreach we can keep the Projects, and Languages relationship required and configured for cascade delete, but make this configuration only apply to tracked entities, not the database: So we can do somethng like below:

protected override void OnModelCreating(ModelBuilder builder)
  {

            builder.Entity<Projects>()
                .HasOne(p => p.FromLanguage)
                .WithMany(b => b.PFromLanguages)
                .HasForeignKey(b => b.FromLanguageId)
                .OnDelete(DeleteBehavior.Restrict);
                .WillCascadeOnDelete(false);// Or .IsRequired(false)

            builder.Entity<Projects>()
                .HasOne(p => p.ToLanguage)
                .WithMany(b => b.PToLanguages)
                .HasForeignKey(b => b.ToLanguageId)
                .OnDelete(DeleteBehavior.Restrict);
                .WillCascadeOnDelete(false); // Or .IsRequired(false)
  }
            
       

Note: As you may know OnDelete(DeleteBehavior.ClientCascade); or ClientCascade allows the DBContext to delete entities even if there is a cyclic ref or LOCK on it. Please read the official guideline for more details here . In addition, as you haven't share your detele operation code while, executing your project make sure you are also removing language from context like here.

CodePudding user response：

The error occurred because I am trying to modify the relations between tables while there are some records already inserted into the targeted tables which violating new foreign key.

The solution was simply to clear existing records in those tables and then apply the migration..

Thank you all for your kind support.