Create Unique constraint for 'true' only in EF Core

Question

I have a class for tracking attachments to a Record. Each Record can have multiple RecordAttachments, but there is a requirement that there can only be one RecordAttachment per-Record that is marked as IsPrimary.

public class RecordAttachment
{
    public int Id { get; set; }
    public int RecordId { get; set; }
    public string Details { get; set; }
    public bool IsPrimary { get; set; }

    public Record Record { get; set; }
}

I can't just use .HasIndex(e => new { e.RecordId, e.IsPrimary }).IsUnique(true) because there can be multiple false values per Record.

Basically I need a unique constraint on RecordId and IsPrimary == true, although this didn't work:

entity.HasIndex(e => new { e.RecordId, IsPrimary = (e.IsPrimary == true) }).IsUnique(true)

Edit: Looking at answers like this: Unique Constraint for Bit Column Allowing Only 1 True (1) Value it appears this would be possible creating the constraint directly with SQL, but then it wouldn't be reflected in my Model.

Answer 1

You can specify index filter using the HasFilter fluent API.

Unfortunately it's not database agnostic, so you have to use the target database SQL syntax and actual table column names.

For Sql Server it would be something like this:

.HasIndex(e => new { e.RecordId, e.IsPrimary })
.IsUnique()
.HasFilter("[IsPrimary] = 1");

or

    .HasIndex(e => new { e.RecordId, e.IsPrimary })
    .IsUnique()
    .HasFilter($"[{nameof(RecordAttachment.IsPrimary)}] = 1");

For more information, see Relational Database Modeling - Indexes documentation topic.