Entity Framework Core 3.1 code first unique constraint not working
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.
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]);
Sanjay Kumar
Updated on June 04, 2022Comments
-
Sanjay Kumar over 1 year
Well question is pretty clear. Here is my
OnModelCreating
method in thedbContext
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 almost 4 yearsthank you for your answer but I have already tried that and it is not working.
-
vernou almost 4 yearsWith
RequiredAttribut
your columnsState
andCode
are nullable? -
A_kat almost 4 years@Orwel No they are not this was actually an old migration. My mistake for not reapplying the migration.