EF Core 3.0 : Why does it automatically generate a unique index?

[ZhangGaoxing]

I have a class (I removed some irrelevant properties)

public class Comment
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long CommentID { get; set; }

    public long? ReplyCommentID { get; set; }

    public long? ParentCommentID { get; set; }

    [ForeignKey("ParentCommentID")]
    public virtual Comment ParentComment { get; set; }

    [ForeignKey("ReplyCommentID")]
    public virtual Comment ReplyComment { get; set; }
}

In my DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Comment>()
        .ToTable("Comments")
        .HasQueryFilter(x => x.IsDeleted == false);

    var cascadeFKs = modelBuilder.Model.GetEntityTypes()
        .SelectMany(t => t.GetForeignKeys())
        .Where(fk => !fk.IsOwnership && fk.DeleteBehavior == DeleteBehavior.Cascade);
    foreach (var fk in cascadeFKs)
        fk.DeleteBehavior = DeleteBehavior.Restrict;
}

Steps to reproduce

In PMC run this:

PM> dotnet ef migrations add init

After run it, the tool generates the following:

migrationBuilder.CreateIndex(
    name: "IX_Comments_ParentCommentID",
    table: "Comments",
    column: "ParentCommentID",
    unique: true,
    filter: "[ParentCommentID] IS NOT NULL");

migrationBuilder.CreateIndex(
    name: "IX_Comments_ReplyCommentID",
    table: "Comments",
    column: "ReplyCommentID");

Interestingly, only ParentCommentID generates a unique index, while ReplyCommentID does not. I don't want to specify IsUnique(false) in DbContext. So, what exactly is this situation? 😄

Further technical details

EF Core version: 3.0.0-preview3.19153.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 1903
IDE: Visual Studio 2019 16.0.2

[divega]

Note from triage: we believe that in this case we should throw because it is non-deterministic whether the FK should be unique.

[AndriySvyryd]

But we'll also try just configuring it as non-unique to avoid a breaking change.

[ajcvickers]

@divega @AndriySvyryd Why is it not just two one-to-many relationships? (In other words, what is it that makes these different from a normal reference-only navigation setup, which we configure as one-to-many?)

[AndriySvyryd]

@ajcvickers They are detected as one one-to-one relationship and then split into two once we process the data annotations.

[ajcvickers]

@AndriySvyryd Why 1:1?

[AndriySvyryd]

@ajcvickers Because both navigations are not collections

[ajcvickers]

@AndriySvyryd But isn't this two different relationships? (Since there are two FKs, and each navigation is associated with a different FK. So the two navigations cannot be inverses of the same relationship. Which means we have one unidirectional reference navigation property for each relationship...which means one-to-many because this will work for one-to-many or one-to-one (if used as one-to-one) even if it isn't fully constrained to one-to-one.)

I'm clearly missing something critical here, but I don't know what question to ask to figure it out!

[AndriySvyryd]

@ajcvickers Yes, that's what should happen, but before the FKs are associated with the navigations they are configured as a single one-to-one relationship and later it becomes a unidirectional one-to-one relationship.

[ajcvickers]

Talked to @AndriySvyryd in person and agreed that this should be two 1:* relationships and so the correct fix (i.e. not just to avoid a breaking change) is to make the FK not unique. (Note that this may be hard to implement due to the design of the model building pipeline.)

Throwing as an ambiguous case would not be the correct fix here; we would only do this if making the correct fix is prohibitively expensive, and we can't come up with anything else.