Using the fluent API to set a unique constraint?

Problem

I’m attempting to create an EF Entity and an EntityTypeConfiguration using the fluent API. It’s simple to create main keys, but not so with a Unique Constraint. I saw some older blogs that advised using native SQL statements to accomplish this, but that seemed to defeat the purpose. Is EF6 capable of this?

Asked by kob490

Solution #1

You can use HasIndex() in EF6.2 to add indexes for migration via the fluent API.

https://github.com/aspnet/EntityFramework6/issues/274

Example

modelBuilder
    .Entity<User>()
    .HasIndex(u => u.Email)
        .IsUnique();

You can use IndexAnnotation() to add indexes for migration in your fluent API starting with EF6.1.

http://msdn.microsoft.com/en-us/data/jj591617.aspx#PropertyIndex

You should include a mention of:

using System.Data.Entity.Infrastructure.Annotations;

Basic Example

Here’s an example of how to use it to create an index on the User. The property FirstName

modelBuilder 
    .Entity<User>() 
    .Property(t => t.FirstName) 
    .HasColumnAnnotation(IndexAnnotation.AnnotationName, new IndexAnnotation(new IndexAttribute()));

Practical Example:

Here’s an example that’s more realistic. It adds a unique index to several properties, including User. Username and FirstName LastName, with “IX FirstNameLastName” as an index.

modelBuilder 
    .Entity<User>() 
    .Property(t => t.FirstName) 
    .IsRequired()
    .HasMaxLength(60)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(
            new IndexAttribute("IX_FirstNameLastName", 1) { IsUnique = true }));

modelBuilder 
    .Entity<User>() 
    .Property(t => t.LastName) 
    .IsRequired()
    .HasMaxLength(60)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(
            new IndexAttribute("IX_FirstNameLastName", 2) { IsUnique = true }));

Answered by Yorro

Solution #2

It can also be done using characteristics, in addition to Yorro’s answer.

The following is an example of an int type unique key combination:

[Index("IX_UniqueKeyInt", IsUnique = true, Order = 1)]
public int UniqueKeyIntPart1 { get; set; }

[Index("IX_UniqueKeyInt", IsUnique = true, Order = 2)]
public int UniqueKeyIntPart2 { get; set; }

The MaxLength attribute must be applied if the data type is string:

[Index("IX_UniqueKeyString", IsUnique = true, Order = 1)]
[MaxLength(50)]
public string UniqueKeyStringPart1 { get; set; }

[Index("IX_UniqueKeyString", IsUnique = true, Order = 2)]
[MaxLength(50)]
public string UniqueKeyStringPart2 { get; set; }

If there is a worry about domain/storage model separation, the Metadatatype attribute/class might be used: https://msdn.microsoft.com/en-us/library/ff664465 percent 28v=pandp.50 percent 29.aspx?f=255&MSPPError=-2147217396&MSPPError=-2147217396&MSPPError=-2147217396&MSPPError=-2147217396&MSPPError=-2147217396&MSPPError=-21472173

Here’s an example of a console app:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace EFIndexTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new AppDbContext())
            {
                var newUser = new User { UniqueKeyIntPart1 = 1, UniqueKeyIntPart2 = 1, UniqueKeyStringPart1 = "A", UniqueKeyStringPart2 = "A" };
                context.UserSet.Add(newUser);
                context.SaveChanges();
            }
        }
    }

    [MetadataType(typeof(UserMetadata))]
    public class User
    {
        public int Id { get; set; }
        public int UniqueKeyIntPart1 { get; set; }
        public int UniqueKeyIntPart2 { get; set; }
        public string UniqueKeyStringPart1 { get; set; }
        public string UniqueKeyStringPart2 { get; set; }
    }

    public class UserMetadata
    {
        [Index("IX_UniqueKeyInt", IsUnique = true, Order = 1)]
        public int UniqueKeyIntPart1 { get; set; }

        [Index("IX_UniqueKeyInt", IsUnique = true, Order = 2)]
        public int UniqueKeyIntPart2 { get; set; }

        [Index("IX_UniqueKeyString", IsUnique = true, Order = 1)]
        [MaxLength(50)]
        public string UniqueKeyStringPart1 { get; set; }

        [Index("IX_UniqueKeyString", IsUnique = true, Order = 2)]
        [MaxLength(50)]
        public string UniqueKeyStringPart2 { get; set; }
    }

    public class AppDbContext : DbContext
    {
        public virtual DbSet<User> UserSet { get; set; }
    }
}

Answered by coni2k

Solution #3

Here’s a shortcut for quickly creating unique indexes:

public static class MappingExtensions
{
    public static PrimitivePropertyConfiguration IsUnique(this PrimitivePropertyConfiguration configuration)
    {
        return configuration.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute { IsUnique = true }));
    }
}

Usage:

modelBuilder 
    .Entity<Person>() 
    .Property(t => t.Name)
    .IsUnique();

This will result in migration such as:

public partial class Add_unique_index : DbMigration
{
    public override void Up()
    {
        CreateIndex("dbo.Person", "Name", unique: true);
    }

    public override void Down()
    {
        DropIndex("dbo.Person", new[] { "Name" });
    }
}

Src: Using the Entity Framework 6.1 Fluent API to Create a Unique Index

Answered by Bartho Bernsmann

Solution #4

@coni2k’s response is accurate; but, in order for it to work, you must include the [StringLength] attribute; otherwise, you will get an invalid key exception (Example bellow).

[StringLength(65)]
[Index("IX_FirstNameLastName", 1, IsUnique = true)]
public string FirstName { get; set; }

[StringLength(65)]
[Index("IX_FirstNameLastName", 2, IsUnique = true)]
public string LastName { get; set; }

Answered by Arijoon

Solution #5

Unfortunately, Entity Framework does not allow this. It was on the EF 6 roadmap, but it was put back: Unique Constraints (Workitem 299) (Unique Indexes)

Answered by Kenneth

Post is based on https://stackoverflow.com/questions/21573550/setting-unique-constraint-with-fluent-api