Create unique constraint on column

Question

I have this model

public class Record { public int Id {get;set;} public int Client {get;set;} public bool IsActive {get;set;} }

I want to create a constraint for the IsActive column.

Only 1 record can be active per client.

How can i achieve this on the model?

You can achieve this by creating unique index on Client and IsActive columns — vasily.sib
– vasily.sib, Commented Jun 24, 2019 at 7:57
Oh I get it. Basically it means if there is a client with true on isActive it cannot insert this again. But isn't this going to do the same for false? because the previous ones are all false. After client record is done updating it is set to false so client can start on a new record next time he logs in — Jackal
– Jackal, Commented Jun 24, 2019 at 7:58
you need to look at filtered index. EF can't provide you with an easy way here, but you can look at this question — vasily.sib
– vasily.sib, Commented Jun 24, 2019 at 8:06
something like CREATE UNIQUE NONCLUSTERED INDEX ... WHERE ([IsActive]=(1)) — vasily.sib
– vasily.sib, Commented Jun 24, 2019 at 8:07

Soheil Alizadeh · Accepted Answer · 2019-06-24 08:37:23Z

You need to create a filtered index for your column the ef core have filter index feature and you can configure it in your dbcontext with overriding OnModelCreating method.

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Record>(entity => { entity.HasIndex(e => e.Client) .HasName("Record_Filtered_Index") .HasFilter("([IsActive]=(1))"); }); }

Collectives™ on Stack Overflow

Create unique constraint on column

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related