How to include a nullable column in a unique constraint using Entity Framework code migrations?

Question

My application uses the EntityFrameWorkCore 8.0.1 and I use code migrations. I want to create the following table:

public class Todo 
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
    public int ParentId { get; set; }
    public Todo Parent { get; set; }
    public int? ProjectId { get; set; }
    public Project Project { get; set; }
    public DateTime? Deleted { get; set; }
}

I want the Todo table to contain a unique constraint of the combination of the UserId, ParentId, ProjectId and Deleted columns.

I have the following configuration for the Todo class:

public class TodoConfiguration : IEntityTypeConfiguration<Todo>
{
    public void Configure(EntityTypeBuilder<ActivityBudget> builder)
    {
        builder.HasKey(e => e.Id);

        builder.HasOne(e => e.User)
            .HasForeignKey(e => e.UserId)
            .OnDelete(DeleteBehavior.NoAction)
            .IsRequired();
            
        builder.HasOne(e => e.Parent)
            .HasForeignKey(e => e.ParentId)
            .OnDelete(DeleteBehavior.NoAction)
            .IsRequired();
            
        builder.Property(e => e.ProjectId)
            .IsRequired(false);

        builder.HasOne(e => e.Project)
            .HasForeignKey(e => e.ProjectId)
            .OnDelete(DeleteBehavior.NoAction);
            
        builder.HasAlternateKey(e => new { e.UserId, e.ParentId, e.ProjectId, e.Deleted })
            .HasName("UXC_Todo");

        builder.HasIndex(e => new { e.UserId, e.ParentId, e.ProjectId, e.Deleted })
            .IsUnique();
}

But when I use the Add-Migration InitialCreate command the generated script will contain the following:

ProjectId = table.Column<int>(type: "int", nullable: false)

This is caused by the code on the line with HasAlternateKey. Is it possible to make to ProjectId column nullable And be part of the unique constraint?

Answer 1

HasAlternateKey requires all database fields to be not null. I don't know if there's a reason for it or that it just happens to have been implemented that way. Either way, sadly, it's not documented, but I see it happening when testing it.

As an alternative, you can define a unique index:

builder.HasIndex(e => new { e.UserId, e.ParentId, e.ProjectId, e.Deleted })
    .IsUnique()
    .HasDatabaseName("UX_Todo");

You already do that, but, if the alternate key was OK, it would be redundant, because HasAlternateKey also generates a unique index. But you need HasIndex because it allows for nullable database fields. The problem, however, is that by default, it creates a filtered index. You'll see a statement like:

CREATE UNIQUE INDEX [UX_Todo] ON [ToDo]
     ([UserId], [e.ParentId], [e.ProjectId], [e.Deleted])
         WHERE [ProjectId] IS NOT NULL AND [Deleted] IS NOT NULL

I.e. entries where both ProjectId and Deleted are null won't be checked. To prevent that, a custom filter clause can be added that removes the default:

builder.HasIndex(e => new { e.UserId, e.ParentId, e.ProjectId, e.Deleted })
    .IsUnique()
    .HasDatabaseName("UX_Todo")
    .HasFilter(null);

Now you have a unique index on fields that can be nullable.