Entity Framework Core 3.1 code first unique constraint not working

11,265

Solution 1

First since you want the column and state code to be non nullable you need to declare it using the annotation [Required]:

[Required]
public string Code {get;set;}

[Required]
public string State {get;set;}

The above will make the columns non nullable.

Unique combination of columns:

entity
    .HasIndex(p => new { p.State, p.Code })
    .IsUnique();

The above is the only way. In order to make a combination of columns unique you must index both of them. Included properties will not work. I also have removed the HasFilter since the column is now non nullable a filter which gets only non null result has no use.

EDIT:

Each column is unique

Also for each column you follow the same logic. Now you require 2 index instead of 1.

entity
    .HasIndex(p => p.State)
    .IsUnique();

entity
    .HasIndex(p => p.Code)
    .IsUnique();

As you can see on SQL Management Studio the index has been created. enter image description here

But of course my scripted table does not include the index. We do not create constraint we create an index and indexes are not included in the create table script.

Solution 2

With Fluent syntaxe :

public class States
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string State { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer("***");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //Declare non nullable columns
        modelBuilder.Entity<States>().Property(s => s.Code).IsRequired();
        modelBuilder.Entity<States>().Property(s => s.State).IsRequired();
        //Add uniqueness constraint
        modelBuilder.Entity<States>().HasIndex(s => s.Code).IsUnique();
        modelBuilder.Entity<States>().HasIndex(s => s.State).IsUnique();
    }
}

Migration code generated :

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "States",
        columns: table => new
        {
            Id = table.Column<int>(nullable: false)
                .Annotation("SqlServer:Identity", "1, 1"),
            Code = table.Column<string>(nullable: false),
            State = table.Column<string>(nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_States", x => x.Id);
        });

    migrationBuilder.CreateIndex(
        name: "IX_States_Code",
        table: "States",
        column: "Code",
        unique: true);

    migrationBuilder.CreateIndex(
        name: "IX_States_State",
        table: "States",
        column: "State",
        unique: true);
}

And the SQL Server queries :

CREATE TABLE [States] (
    [Id] int NOT NULL IDENTITY,
    [Code] nvarchar(max) NOT NULL,
    [State] nvarchar(450) NOT NULL,
    CONSTRAINT [PK_States] PRIMARY KEY ([Id])
);
CREATE UNIQUE INDEX [IX_States_State] ON [States] ([State]);
CREATE UNIQUE INDEX [IX_States_Code] ON [States] ([Code]);
Share:
11,265
Sanjay Kumar
Author by

Sanjay Kumar

Updated on June 04, 2022

Comments

  • Sanjay Kumar
    Sanjay Kumar over 1 year

    Well question is pretty clear. Here is my OnModelCreating method in the dbContext class:

    modelBuilder.Entity<States>(entity =>
                {
                    entity.ToTable("States");
    
                    modelBuilder.Entity<States>()
                    .HasIndex(p => new { p.State })
                    .HasFilter("[State] IS NOT NULL")
                    .HasFilter("[Code] IS NOT NULL")
                    .IncludeProperties(p => new
                    {
                        p.Code
                    })
                    .IsUnique();
    

    I have tried .IsUnique(true) too but it doesn't work.

    And here is migration code being generated:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
            migrationBuilder.CreateTable(
                name: "States",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    State = table.Column<string>(maxLength: 30, nullable: true),
                    Code = table.Column<string>(maxLength: 3, nullable: true),
                    Description = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_States", x => x.Id);
                });
    
            migrationBuilder.CreateIndex(
                name: "IX_States_State",
                table: "States",
                column: "State",
                unique: true,
                filter: "[Code] IS NOT NULL")
                .Annotation("SqlServer:Include", new[] { "Code" });
    }
    

    and here is my SQL query of the table being generated:

    CREATE TABLE [dbo].[States]
    (
        [Id] [INT] IDENTITY(1,1) NOT NULL,
        [State] [NVARCHAR](30) NULL,
        [Code] [NVARCHAR](3) NULL,
        [Description] [NVARCHAR](MAX) NULL,
    
        CONSTRAINT [PK_States] 
            PRIMARY KEY CLUSTERED ([Id] ASC)
                        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    

    As you can see the columns are not unique and are nullable.

    What am I missing here?

  • Sanjay Kumar
    Sanjay Kumar almost 4 years
    thank you for your answer but I have already tried that and it is not working.
  • vernou
    vernou almost 4 years
    With RequiredAttribut your columns Stateand Code are nullable?
  • A_kat
    A_kat almost 4 years
    @Orwel No they are not this was actually an old migration. My mistake for not reapplying the migration.