Does EF Core allow a unique column to contain multiple nulls?

Question

My entity has a property which is allowed to be null. BUT, if it isn't null, then it must be unique. In other words, the column is unique but allows multiple nulls.

I've tried:

config.Property(p => p.ProductId).IsRequired(false);

I remember struggling to get this to work in pre-Core EF.

Is this possible? How do I configure the entity?

score 27 · Accepted Answer · answered Jan 03 '17 at 08:58

27

Yes, you can do that with EF Core, as a Unique index by default is created as a filtered index (WHERE ... IS NOT NULL)

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

https://github.com/aspnet/EntityFramework/pull/2868

answered Jan 03 '17 at 08:58

ErikEJ

40,951
5
75
115

3

I assume this is for SQL Server - do you know whether it is also supported by SQLite? "Filtered index" seems to be an MS thing? – grokky Jan 03 '17 at 11:36
I'm on EF Core 1.0.2 and this doesn't seem to work for me? modelBuilder.Entity("DataModel.Models.Employee", b =>{b.HasIndex("FingerprintId").IsUnique();}); Still getting `SqlException: Cannot insert duplicate key row in object 'dbo.Employees' with unique index 'IX_Employees_FingerprintId'. The duplicate key value is ().` – Lawrence May 03 '17 at 12:42
@grokky Would you mind sharing at which exact part of the code you are writing that part? I'm still quite new to EF in general and I've seen multiple location which have similar syntax to this answer. – Lawrence May 16 '17 at 02:01
@Lawrence That depends on how you've designed your system. Erik's suggestion definitely works. I suggest you open a new question, show your EF config code. Maybe Erik can have a look, he's one of the EF grand masters. – grokky May 16 '17 at 07:57

jmcilhinney · Answer 2 · 2021-03-18T05:19:17.363

I know this is old but, for those who find it now, the default behaviour I'm seeing in EF Core 5.0 is not what's described in the accepted answer. You can specify a filter for an index explicitly and here's an example of one I just created:

modelBuilder.Entity<Reef>(etb =>
                          {
                              // ...

                              etb.HasIndex(r => r.Label)
                                 .HasFilter("Label IS NOT NULL")
                                 .IsUnique();

                              // ...
                          });

EDIT:

I think that I have worked out the complete answer. If you have a property that is a nullable value type and you create a unique index on that then that index will have a filter by default. If the property is a reference type though, you have to specify that it is nullable as an extra step, either using the fluent API or an attribute. I haven't tested with an attribute but, using the fluent API, the resulting index will not have a filter by default. Consider the following entity:

public class Thing
{
    public int ThingId { get; set; }
    public string Text { get; set; }
    public int? StuffId { get; set; }

    public Stuff Stuff { get; set; }
}

With the following in the DbContext:

modelBuilder.Entity<Thing>(etb =>
                           {
                               etb.Property(t => t.Text)
                                  .IsRequired(false);

                               etb.HasIndex(t => t.Text)
                                  .IsUnique();

                               etb.HasIndex(t => t.StuffId)
                                  .IsUnique();
                          });

The unique index generated on the StuffId column will have a filter by default while the one on Text will not. A filter must be specified explicitly for the Text column index:

modelBuilder.Entity<Thing>(etb =>
                           {
                               etb.Property(t => t.Text)
                                  .IsRequired(false);

                               etb.HasIndex(t => t.Text)
                                  .HasFilter("Text IS NOT NULL")
                                  .IsUnique();

                               etb.HasIndex(t => t.StuffId)
                                  .IsUnique();
                          });

For the record, the accepted answer is correct (but not complete for EF 5). EF creates a filtered index when the index is unique. — Gert Arnold, Mar 16 '21 at 08:19
@GertArnold, I think I've worked out where the discrepancy is. I have a foreign key of type `long?` and the unique index generated on the corresponding column did indeed have a filter. The `Label` property shown in my answer above is type `string` and so had to be made nullable by using `etb.Property(r => r.Label).IsRequired(false)`. The unique index generated on that column did not have a filter by default so it had to be provided explicitly. — jmcilhinney, Mar 18 '21 at 05:05

Does EF Core allow a unique column to contain multiple nulls?

2 Answers2

Linked