Entity Framework Core - prevent multiple null values on unique indexes

Question

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?

Your product id appears to be a primary key, which SQL does not allow to be null. — Greg, Jan 09 '18 at 22:00

score 31 · Accepted Answer · answered Jan 09 '18 at 22:33

31

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.

answered Jan 09 '18 at 22:33

Matthew Layton

What query does the above generate to please MSSQL? MSSQL expects a primary key to be unique? – Greg Jan 09 '18 at 23:06
@Greg `ProductId` is not a primary key, it's a foreign key referencing a primary key in the same table (a circular reference). – Matthew Layton Jan 09 '18 at 23:08
Ah, but why would you want to add a circular reference like that? Genuinely curious, I've never thought to use null as a unique constraint. – Greg Jan 09 '18 at 23:11
1

@Greg I was curious as to whether it is possible to create a linked list using SQL. The first element cannot point to anything, because there's nothing to point to when it's inserted. The next element can't point to null, because the first element points to null, therefore the next element must point to the first one, etc. – Matthew Layton Jan 09 '18 at 23:13
You taught me something new, very cool. Neat idea. – Greg Jan 10 '18 at 06:47
doesn't work in MySql – maskalek Mar 19 '20 at 15:58
tried it on multiple index, but unfortunately doesn`t work for me:( – Влад Макух Jun 17 '21 at 12:02
I need to skip null values, this way worked for me by specifying filter :) – Homayoun Behzadian Jun 25 '21 at 08:20

1 Answers1