仅在 EF Core 中为“true"创建唯一约束

问题描述

我有一个用于跟踪记录附件的类.每个 Record 可以有多个 RecordAttachment，但要求每个 Record 只能有一个 RecordAttachment 标记为 IsPrimary.

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; }
}

我不能只使用 .HasIndex(e => new { e.RecordId, e.IsPrimary }).IsUnique(true) 因为可以有多个 false每条记录的值.

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

基本上我需要对 RecordId 和 IsPrimary == true 的唯一约束，尽管这不起作用:

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)

查看这样的答案:位的唯一约束Column Allowing Only 1 True (1) Value 看来这可以直接用 SQL 创建约束，但它不会反映在我的模型中.

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.

推荐答案

您可以使用 HasFilter 流畅的 API.

You can specify index filter using the HasFilter fluent API.

不幸的是，它与数据库无关，因此您必须使用目标数据库 SQL 语法和实际表列名称.

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

对于 Sql Server，它将是这样的:

For Sql Server it would be something like this:

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

或

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

有关详细信息，请参阅关系数据库建模 - 索引 文档主题.

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

这篇关于仅在 EF Core 中为“true"创建唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！