Entity Framework Core 3.1 code first unique constraint not working

code-first entity-framework-core unique-constraint

Question

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?

1
0
12/31/2019 11:00:24 AM

Accepted Answer

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.

1
12/31/2019 11:05:30 AM

Popular Answer

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]);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow