Entity Framework - how to set column as unique related to another column

Question

I am using Entity Framework and I have the following model class:

public class Test 
{
    public string Name { get; set; }
    public int SiteId { get; set; }    
    public bool IsPrimary { get; set; } 
}

How can I set the column IsPrimary to be unique in accordance with the SiteId?

For example I can have multiple records with the IsPrimary set to false for the same SiteId, but only one set to true.

Are you looking to enforce this in the database or in the application? — devNull, Mar 30 '20 at 14:47
Assuming the database, you could look into using a check constraint: https://stackoverflow.com/q/13232777/5803406 — devNull, Mar 30 '20 at 14:49

score 1 · Accepted Answer · answered Mar 31 '20 at 09:48

finally found the solution! i can have multiple rows for the same siteId when isPrimary is false but only one when the isPrimary is true.

modelBuilder.Entity<MyTable>()
            .HasIndex(b => new { b.IsPrimary, b.SiteId })
            .IsUnique()
            .HasFilter("IsPrimary = 1");

score 0 · Answer 2 · answered Mar 31 '20 at 10:02

0

You can try with the following codes:

[Index("Index_SiteId_IsPrimary", 1, IsUnique = true)]
public int SiteId  { get; set; }

[Index("Index_SiteId_IsPrimary", 2, IsUnique = true)]
public bool IsPrimary { get; set; }

answered Mar 31 '20 at 10:02

sebu

2,824
1
30
45

Entity Framework - how to set column as unique related to another column

2 Answers2