.NET 6 (Core) EF - Conditional Unique constraint?

Question

I have a model DocIdent that defines Identification Documents for a person. It has an FK to DocIdentTypes, and a value. (for example: type: "passport", value "XXXXX"; type "social security number", value YYYY; etc).

public class DocIdent {
    public int Id { get; set; }
   
    [ForeignKey("DocIdentType")]
    public int DocIdentTypeId { get; set; }       //FK

    public string Value { get; set; }

    //nav. props
    public virtual DocIdentType DocIdentType { get; set; }
}

And I have a unique constraint (composite) defined in OnModelCreating(), like this:

        modelBuilder.Entity<DocIdent>()
            .HasIndex(entity => new { entity.DocIdentTypeId, entity.Value }).IsUnique();

So that it will only allow one combination of DocIdentType and Value.

The problem is: there is a DocIdentType "undocumented" (lets say its Id is 5). And obviously, for this DocIdentType, the value will always be null. (or, to avoid having to make the field nullable, lets say the value in these cases will always be "Not applicable").

So I need to modify the constraint so that, if and only if the DocIdentType Id is 5, then it must allow to have duplicated values.

I hope this explanation makes sense...

How can this be done? (or is it not possible?)

Answer 1

You can use filters on the index, like so :

 modelBuilder.Entity<DocIdent>()
            .HasIndex(entity => new { entity.DocIdentTypeId, entity.Value })
            .IsUnique()
            .HasFilter("[DocIdentTypeId] != 5")

Answer 2

You could check this document:

UNIQUE constraints allow for the value NULL.

..........

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table

If you configured unique constraints in the database, it would just perform as the document mentioned, won't ignore particular value (the database itself doesn't allow you to try what you want).

The arguments (true / false) in .IsUnique() only determine if unique is added to it: