EF Core Unique constraints with "NULL" values

Question

The solution of Does EF Core allow a unique column to contain multiple nulls? works perfect with Microsoft SQL Server but not with PostgreSQL. Is there a solution which works also with PostgreSQL (Npgsql provider) ?

Answer 1

You can simply use a filtered index to specify the WHERE clauses as you wish:

modelBuilder.Entity<Blog>().HasIndex(b => b.SomeInt)
    .HasFilter(@"""SomeInt"" IS NOT NULL")
    .IsUnique();

This creates the index as follows:

CREATE UNIQUE INDEX "IX_Blogs_SomeInt" ON "Blogs" ("SomeInt") WHERE "SomeInt" IS NOT NULL;