Search code examples
entity-framework-corecode-firstunique-constraint

Entity Framework Core 3.1 code first unique constraint not working

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?

Solution

  • 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.