EF Core: Juggling between cascading cycles and conflicting property with simple name

Question

I am trying to a create a many-to-many relationship by using a linking table Booking and this got me stuck juggling between two errors and I'm very confused.

Introducing FOREIGN KEY constraint 'FK_Bookings_WorkProfiles_WorkProfileId' on table 'Bookings' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

Which I tried to solve going into Context class modelBuilder.

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
                .HasOne(e => e.UserProfile)
                .WithMany()
                .OnDelete(DeleteBehavior.Restrict);
}

But this creates the following error:

The foreign key property 'Booking.UserProfileId1' was created in shadow state because a conflicting property with the simple name 'UserProfileId' exists in the entity type, but is either not mapped, is already used for another relationship, or is incompatible with the associated primary key type. See https://aka.ms/efcore-relationships for information on mapping relationships in EF Core.

I am very confused. I tried fixing this error by removing the foreign key etc. and I get back to first error again.

What I would like: deleting UserProfile or WorkProfile to not cascade to delete booking so I don't get the the first error.

public class Booking : BaseDateClass
{
    public int BookingId { get; set; }
    [ForeignKey("AdvertTreatmentId")]
    public AdvertTreatment AdvertTreatment { get; set; }
    [ForeignKey("UserProfileId")]
    public UserProfile UserProfile { get; set; }
    [ForeignKey("WorkProfileId")]
    public WorkProfile WorkProfile { get; set; }
}

public class UserProfile : BaseDateClass
{
    public int UserProfileId { get; set; }
    public List Bookings { get; set; }
}

public class WorkProfile : BaseDateClass
{
    public int WorkProfileId { get; set; }
    [ForeignKey("WorkAccountId")]
    public WorkAccount WorkAccount { get; set; }
    public List? Adverts { get; set; }
    public List WorkProfileLanguages { get; set; }
    public List Bookings { get; set; }
}

Edit: this is an Entity Framework Core 6.0.5 project

  
    
    
    
    
      runtime; build; native; contentfiles; analyzers; buildtransitive
      all

user_5202563 · Answer

The second problem is caused by a typical mistake with fluent relationship configuration.

You are required to start the relationship configuration with Has{One|Many} followed by With{One|Many}, but when doing so, many people don't realize that not passing the navigation property to these fluent calls does not mean "use the conventional default", but instead it means that the relationship has no navigation property at the corresponding end.

However, when you do have navigation property, this leaves it unmapped, which in turn causes EF to associate a different separate relationship with conventional FK name. Which in turn leads to errors like the one in question, and in general is not what you want.

Thus, you should always pass the correct argument to Has / With method.

In your case, the cause of the issue is the HasMany() call here

    modelBuilder.Entity()
                .HasOne(e => e.UserProfile)
                .WithMany() // <--
                .OnDelete(DeleteBehavior.Restrict);

which leaves UserProfile.Bookings collection navigation property unmapped.

So change it to

.WithMany(e => e.Bookings)

and the problem will be solved.

user_2936204 · Answer

I think your problem that your are defining the FK in attributes and then FluentApi is trying to add another. using by-convention value which will be the same as the item specified via attributes.

Try removing:

[ForeignKey("UserProfileId")]

from your class and update your Fluent config to:

    modelBuilder.Entity()
    .HasOne(e => e.UserProfile)
    .WithMany()
    .HasForiegnKey("UserProfileId")
     .OnDelete(DeleteBehavior.Restrict);

UPDATE

I guess that your other relationships are causing some issues.

Using the following model without any Fluent Config:

public class Booking
{
    public int BookingId { get; set; }
    [ForeignKey("UserProfileId")]
    public UserProfile UserProfile { get; set; }
    [ForeignKey("WorkProfileId")]
    public WorkProfile WorkProfile { get; set; }
}

public class UserProfile
{
    public int Id { get; set; }
    public List Bookings { get; set; }
}

public class WorkProfile
{
    public int WorkProfileId { get; set; }
    public List Bookings { get; set; }
}

I get a migration created without problem and am able to update to an Sql Server database.

Migration created is:

public partial class initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "UserProfile",
            columns: table => new
            {
                Id = table.Column(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_UserProfile", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "WorkProfile",
            columns: table => new
            {
                WorkProfileId = table.Column(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_WorkProfile", x => x.WorkProfileId);
            });

        migrationBuilder.CreateTable(
            name: "Bookings",
            columns: table => new
            {
                BookingId = table.Column(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                UserProfileId = table.Column(type: "int", nullable: false),
                WorkProfileId = table.Column(type: "int", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Bookings", x => x.BookingId);
                table.ForeignKey(
                    name: "FK_Bookings_UserProfile_UserProfileId",
                    column: x => x.UserProfileId,
                    principalTable: "UserProfile",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
                table.ForeignKey(
                    name: "FK_Bookings_WorkProfile_WorkProfileId",
                    column: x => x.WorkProfileId,
                    principalTable: "WorkProfile",
                    principalColumn: "WorkProfileId",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Bookings_UserProfileId",
            table: "Bookings",
            column: "UserProfileId");

        migrationBuilder.CreateIndex(
            name: "IX_Bookings_WorkProfileId",
            table: "Bookings",
            column: "WorkProfileId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Bookings");

        migrationBuilder.DropTable(
            name: "UserProfile");

        migrationBuilder.DropTable(
            name: "WorkProfile");
    }
}

Alternative

public class Booking
{
    public int BookingId { get; set; }
    //[ForeignKey("UserProfileId")]  // REMOVED
    public UserProfile UserProfile { get; set; }
    [ForeignKey("WorkProfileId")]
    public WorkProfile WorkProfile { get; set; }
}

public class UserProfile
{
    public int Id { get; set; }
    public List Bookings { get; set; }
}

public class WorkProfile
{
    public int WorkProfileId { get; set; }
    public List Bookings { get; set; }
}

public class Context : DbContext
{
    public Context(DbContextOptions options)
    : base(options) { }

    public DbSet Bookings { get; set; }

    public DbSet UserProfiles { get; set; }    

    public DbSet WorkProfiles { get; set; }

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity()
            .HasOne(b => b.UserProfile)
            .WithMany()
            .HasForeignKey("UserProfileId")
            .OnDelete(DeleteBehavior.Restrict);
    }
}

also works fine.

Just noticed the signature of your OnModelCreating!!

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)

Change that to match the signature in my latest post.

EF Core: Juggling between cascading cycles and conflicting property with simple name

2 Answers