Entity Framework Core add unique constraint code-first

Question

I can't find way to add a unique constraint to my field with using attribute:

public class User
{
    [Required]
    public int Id { get; set; }

    [Required]
    // [Index("IX_FirstAndSecond", 2, IsUnique = true)] not supported by core
    public string Email { get; set; }

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

I'm using these packages:

 "Microsoft.EntityFrameworkCore": "1.0.1",
 "Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
 "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.1",
 "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",

Answer 1

Yeah, so any of this worked for me. I'm in .NET 6 and I don't know which version of EF, but my solution came from official documentation.

[Index(nameof(Email), IsUnique = true)]
public class User
{
    [Required]
    public int Id { get; set; }

    [Required]
    // [Index("IX_FirstAndSecond", 2, IsUnique = true)] not supported by core
    public string Email { get; set; }

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

Basically to use DataAnnotatios you need to declare the indexes in the class and not in the property.

Answer 2

Elaborating on @Kuldeep Singh's answer:

When using older versions of .net his answer worked. When you want to create a unique index on multiple columns:

Property(p => p.Prop1).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique", 1) { IsUnique = true }));
Property(p => p.Prop2).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Unique", 2) { IsUnique = true }));

Note: the name 'IX_Unique' has to be the same for both columns. And The order specified in the IndexAttribute constructor is crucial for multi-column indexes, as it determines the column order in the index.

Answer 3

Since Entity Framework Core (EF Core) 5.0 we can configure Unique Indexes via Data Annotations.

It is little different to e.g. EF6, because we cannot set it on the property itself but instead on the class.

using Microsoft.EntityFrameworkCore;           // for [Index] attribute
using System.ComponentModel.DataAnnotations;   // for [Key] and [Required] attributes

namespace FunWithDataAnnotations
{
    [Index(nameof(EmailAddress), IsUnique = true)]
    public class User
    {
        [Key]
        public Guid Id { get; set; }

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

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

For further information on Indexes and Data Annotations see: https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations

Answer 4

The OP is asking about whether it is possible to add an Attribute to an Entity class for a Unique Key. The short answer is that it IS possible, but not an out-of-the-box feature from the EF Core Team. If you'd like to use an Attribute to add Unique Keys to your Entity Framework Core entity classes, you can do what I've posted here

public class Company
{
    [Required]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid CompanyId { get; set; }

    [Required]
    [UniqueKey(groupId: "1", order: 0)]
    [StringLength(100, MinimumLength = 1)]
    public string CompanyName { get; set; }

    [Required]
    [UniqueKey(groupId: "1", order: 1)]
    [StringLength(100, MinimumLength = 1)]
    public string CompanyLocation { get; set; }
}

Answer 5

Ef core support unique configuration.

protected override void OnModelCreating(ModelBuilder builder)
{
  builder.Entity<Account>()
    .HasIndex(account => account.Email)
      .IsUnique();
}

Ef core support multiple unique keys

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<Account>()
      .HasKey(account => new { account.Id, account.Email, account.RoleId });
}

Don't forget run ef core command to make migration and update the database

>> dotnet ef migrations add MigrationName -c YourDbContextName
>> dotnet ef database update -c YourDbContextName

Answer 6

To use it in EF core via model configuration

public class ApplicationCompanyConfiguration : IEntityTypeConfiguration<Company>
{
    public void Configure(EntityTypeBuilder<Company> builder)
    {
        builder.ToTable("Company"); 
        builder.HasIndex(p => p.Name).IsUnique();
    }
}

Answer 7

None of these methods worked for me in .NET Core 2.2 but I was able to adapt some code I had for defining a different primary key to work for this purpose.

In the instance below I want to ensure the OutletRef field is unique:

public class ApplicationDbContext : IdentityDbContext
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Outlet>()
                .HasIndex(o => new { o.OutletRef });
        }
    }

This adds the required unique index in the database. What it doesn't do though is provide the ability to specify a custom error message.

Answer 8

For someone who is trying all these solution but not working try this one, it worked for me

protected override void OnModelCreating(ModelBuilder builder)
{

    builder.Entity<User>().Property(t => t.Email).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_EmailIndex") { IsUnique = true }));

}

Answer 9

We can add Unique key index by using fluent api. Below code worked for me

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<User>().Property(p => p.Email).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_EmailIndex") { IsUnique = true }));

    }

Answer 10

On EF core you cannot create Indexes using data annotations.But you can do it using the Fluent API.

Like this inside your {Db}Context.cs:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<User>()
        .HasIndex(u => u.Email)
        .IsUnique();
}

...or if you're using the overload with the buildAction:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<User>(entity => {
        entity.HasIndex(e => e.Email).IsUnique();
    });
}

You can read more about it here : Indexes

Answer 11

Solution for EF Core

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Passport { get; set; }
}

public class ApplicationContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public ApplicationContext()
    {
        Database.EnsureCreated();
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=efbasicsappdb;Trusted_Connection=True;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasAlternateKey(u => u.Passport);
        //or: modelBuilder.Entity<User>().HasAlternateKey(u => new { u.Passport, u.Name})
    }
}

DB table will look like this:

CREATE TABLE [dbo].[Users] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Name]     NVARCHAR (MAX) NULL,
    [Passport] NVARCHAR (450) NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [AK_Users_Passport] UNIQUE NONCLUSTERED ([Passport] ASC)
);

Ref to EF Core docs

Answer 12

Also if you want to create Unique constrains on multiple columns you can simply do this (following @Sampath's link)

class MyContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .HasIndex(p => new { p.FirstName, p.LastName })
            .IsUnique(true);
    }
}

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}