Entity Framework Core - prevent multiple null values on unique indexes [duplicate]

Question

In relation to this question: Does EF Core allow a unique column to contain multiple nulls?

I want every value to be unique, even null.

config.Entity<Product>()
    .HasIndex(b => b.ProductId)
    .IsUnique();

The equivalent in SQL works

[ProductId] int null unique foreign key references Product([Id])

Can I modify this code to prevent multiple nulls on a column?

Answer 1

By default, the Fluent API for EF Core adds

filter: "[ProductId] IS NOT NULL"

to the index created in the migration.

In order to ensure that even NULL is unique, we have to modify our index, like so:

config.Entity<Product>()
    .HasIndex(b => b.ProductId)
    .IsUnique()
    .HasFilter(null);

This removes the filter, and allows NULL to be unique.