Code first Unique constraint on multiple columns

Question

Say I want a Microsoft SQL Server entity as follows:
CREATE TABLE [dbo].[Entity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[COL1] nvarchar NOT NULL,
[COL2] nvarchar NULL,
[COL3] nvarchar NULL,
CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED

Then I want to use fluent API to create a constraint based on COL1, COL2 and COL3 such that rows like
"One",Null,Null (for COL1,COL2 and COL3) can only exist once (and of course COL1 cannot be null).
The constraint must be within the DB scheme, not just in EF core.
I have tried with both .HasAlternateKey() and .HasIndex().IsUnique(), but I can still add mentioned rows data.

Answer 1

Hi EuroEager2008-7858,
>>I have tried with both .HasAlternateKey() and .HasIndex().IsUnique(), but I can still add mentioned rows data.
In general, you can use HasIndex() and to add indexes for migration through fluent API. So in order to find the cause more accurately, please provide the code you tried.
Code likes below:

    modelBuilder.Entity<Blog>()  
        .HasIndex(b => b.Url)  
 .IsUnique();  

And I found some code example using fluent API to create a constraint you can refer to.
Setting unique Constraint with fluent API?
Entity Framework Core add unique constraint code-first
Unique Key constraints for multiple columns in Entity Framework
Hope these are helpful to you.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi EuroEager2008,
Please try to change your code below

modelBuilder.Entity<Entity>()  
                .ToTable("Entity")  // Is this really neccessary to singularize the entity name?  
                .HasIndex(entity => new { entity.COL1, entity.COL2 }, "UniqueCOL1_COL2").IsUnique(true);  

to

modelBuilder.Entity<Entity>()  
   .HasIndex(entity => new { entity.COL1, entity.COL2 }).IsUnique();  

or

modelBuilder.Entity<Entity>().HasAlternateKey(entity => new [] {entity.COL1, entity.COL2}).HasName("UniqueCOL1_COL2");  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.