英文:
.NET 6 (Core) EF - Conditional Unique constraint?
问题
我有一个名为DocIdent的模型，用于定义个人的身份证明文件。它具有对DocIdentTypes的外键（FK），以及一个值。 （例如：类型：“护照”，值“XXXXX”；类型“社会安全号码”，值“YYYY”等）。
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; }
}
并且我在OnModelCreating()中定义了一个唯一约束（复合约束），如下所示：
modelBuilder.Entity<DocIdent>()
.HasIndex(entity => new { entity.DocIdentTypeId, entity.Value }).IsUnique();
因此，它只允许一种DocIdentType和Value的组合。
问题是：有一个DocIdentType叫做“undocumented”（假设其Id为5）。很显然，对于这种DocIdentType，值始终为null（或者，为了避免使字段可为空，可以说在这些情况下值始终为“不适用”）。
因此，我需要修改约束，以便仅当DocIdentType Id为5时，它才能允许重复的值。
希望这个解释有意义...
如何实现这一点？（或者这是否不可能？）
英文:
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?)
答案1
得分: 1
你可以在索引上使用筛选器，如下所示：
modelBuilder.Entity<DocIdent>()
.HasIndex(entity => new { entity.DocIdentTypeId, entity.Value })
.IsUnique()
.HasFilter("[DocIdentTypeId] != 5")
英文:
You can use filters on the index, like so :
modelBuilder.Entity<DocIdent>()
.HasIndex(entity => new { entity.DocIdentTypeId, entity.Value })
.IsUnique()
.HasFilter("[DocIdentTypeId] != 5")
答案2
得分: 1
你可以查看这个文档：
UNIQUE约束允许包含值为NULL。
..........
数据库引擎会自动创建一个UNIQUE索引来强制执行UNIQUE约束的唯一性要求。因此，如果尝试插入重复的行，数据库引擎会返回一个错误消息，指出UNIQUE约束已被违反，并不会将该行添加到表中。
如果你在数据库中配置了唯一约束，它将按照文档中提到的方式执行，不会忽略特定的值（数据库本身不允许你尝试你想要的）。
.IsUnique()中的参数（true / false）仅确定是否向其添加唯一性：
英文:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论