EF Core中实现仅IsPrimary为true的RecordAttachment唯一约束的方法问询

Great question! The issue here is that a standard unique index on (RecordId, IsPrimary) won't work because you'll have multiple attachments with IsPrimary = false for the same RecordId—those duplicate (RecordId, false) pairs would violate the unique constraint. Let's go through the most reliable solutions, ordered by how robust they are:

1. Database-Level Filtered Unique Index (Most Reliable) #

This is the gold standard because it enforces the constraint directly at the database level, so no application logic can bypass it. A filtered index only applies the unique constraint to rows where IsPrimary = true.

EF Core Configuration (5.0+) #

You can define this directly in your model configuration using HasFilter:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<RecordAttachment>()
        // Create a unique index on RecordId, but only for rows where IsPrimary is true
        .HasIndex(e => e.RecordId)
        .IsUnique()
        .HasFilter("[IsPrimary] = 1"); // Adjust syntax for your database (e.g., `IsPrimary = true` for PostgreSQL/MySQL)
}

Raw SQL (If You Need to Create It Manually) #

For SQL Server, the index would look like this:

CREATE UNIQUE NONCLUSTERED INDEX IX_RecordAttachment_PrimaryByRecord
ON RecordAttachment (RecordId)
WHERE IsPrimary = 1;

2. Business Logic Validation (Complementary to Database Constraints) #

While database constraints are critical, adding validation in your business logic provides better user feedback before hitting the database. You can also automatically demote existing primary attachments when setting a new one:

public async Task AddOrUpdateAttachmentAsync(RecordAttachment attachment)
{
    if (attachment.IsPrimary)
    {
        // Check if a primary attachment already exists for this record
        var existingPrimary = await _dbContext.RecordAttachments
            .AnyAsync(a => a.RecordId == attachment.RecordId && a.IsPrimary);
        
        if (existingPrimary)
        {
            // Option 1: Throw an error to notify the user
            throw new InvalidOperationException("This record already has a primary attachment.");
            
            // Option 2: Automatically demote the existing primary
            // var oldPrimary = await _dbContext.RecordAttachments
            //     .FirstOrDefaultAsync(a => a.RecordId == attachment.RecordId && a.IsPrimary);
            // if (oldPrimary != null) oldPrimary.IsPrimary = false;
        }
    }

    _dbContext.RecordAttachments.Update(attachment);
    await _dbContext.SaveChangesAsync();
}

⚠️ Note: This alone isn't enough for high-concurrency scenarios—two simultaneous requests could still create duplicate primary attachments. Always pair this with a database constraint.

3. Database Trigger (Fallback for Older Databases) #

If your database doesn't support filtered indexes (e.g., SQL Server versions before 2008), you can use a trigger to enforce the constraint on insert/update:

CREATE TRIGGER TR_RecordAttachment_EnforceSinglePrimary
ON RecordAttachment
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Check if any record now has multiple primary attachments
    IF EXISTS (
        SELECT RecordId
        FROM RecordAttachment
        WHERE IsPrimary = 1
        GROUP BY RecordId
        HAVING COUNT(*) > 1
    )
    BEGIN
        RAISERROR('Only one primary attachment per record is allowed.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

Key Notes #

• Adjust the filtered index syntax to match your database: PostgreSQL/MySQL use IsPrimary = true instead of [IsPrimary] = 1.
• Always prefer database-level constraints over application logic alone—they're the last line of defense against invalid data.

内容的提问来源于stack exchange，提问作者Valuator