Setting unique Constraint with fluent API?

Question

I'm trying to build an EF Entity with Code First, and an EntityTypeConfiguration using fluent API. creating primary keys is easy but not so with a Unique Constraint. I was seeing old posts that suggested executing native SQL commands for this, but that seem to defeat the purpose. is this possible with EF6?

Solution 1

On EF6.2, you can use HasIndex() to add indexes for migration through fluent API.

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

Example

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

On EF6.1 onwards, you can use IndexAnnotation() to add indexes for migration in your fluent API.

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

You must add reference to:

using System.Data.Entity.Infrastructure.Annotations;

Basic Example

Here is a simple usage, adding an index on the User.FirstName property

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

Practical Example:

Here is a more realistic example. It adds a unique index on multiple properties: User.FirstName and User.LastName, with an index name "IX_FirstNameLastName"

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

Solution 2

As an addition to Yorro's answer, it can also be done by using attributes.

Sample for 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; }

If the data type is string, then MaxLength attribute must be added:

[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 domain/storage model separation concern, using Metadatatype attribute/class can be an option: https://msdn.microsoft.com/en-us/library/ff664465%28v=pandp.50%29.aspx?f=255&MSPPError=-2147217396

---

A quick console app example:

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; }
    }
}

Solution 3

Here is an extension method for setting unique indexes more fluently:

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

Will generate 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: Creating Unique Index with Entity Framework 6.1 fluent API

Solution 4

@coni2k 's answer is correct however you must add [StringLength] attribute for it to work 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; }

Solution 5

Unfortunately this is not supported in Entity Framework. It was on the roadmap for EF 6, but it got pushed back: Workitem 299: Unique Constraints (Unique Indexes)

View more solutions

Comments

[Alexander Vasilyev]

This is required to name column annotation as "Index"! I wrote another name and it didn't work! I spent hours before I try rename it to original "Index" as in your post and understood that this important. :( There must be a constant for it in the framework to not hard code the string.

[Bassam Alugili]

useful link for the problem! stackoverflow.com/questions/18889218/…

[Nathan]

@AlexanderVasilyev The constant is defined as IndexAnnotation.AnnotationName

[Alexander Vasilyev]

@Nathan Thank you! That's it! The example in this post must be corrected using this constant.

[Rickard Liljeberg]

Not if you want to keep your domain model completely separate from storage concerns.

[MikeT]

You also need to make sure you have a reference to EntityFramework

[Sam]

Be nice if the Index attribute was separated from Entity Framework so I could include it in my Models project. I understand that it is a storage concern, but the main reason I use it is to put unique constraints on things like UserNames and Role Names.

[Phil]

It's also pretty easy to create your own extension methods so you can write things like Property(x => x.FirstName).Unique("IX_FirstName") or new[] { Property(x => x.FirstName), Property(x => x.LastName) }.Unique("IX_FirstNameLastName")

[Shimmy Weitzhandler]

Can't seem to find it in EF7 - DNX

[Shimmy Weitzhandler]

Can't seem to find it in EF7 - DNX

[Tanuki]

Yeah, and how do you add unique constraints on navigation properties? Lets say if one of the User properties has a type of Role? how do you make a unique constraint off that?

[Cheesus Toast]

This must be out of date. It does not work at all. You just get an exception: Column 'FieldName' in table 'dbo.TableName' is of a type that is invalid for use as a key column in an index.

[Cheesus Toast]

You get an exception if you do not set the string length. I have edited the answer. It think it may normally be 70 characters for the whole name but it is whatever you feel like.

[Cheesus Toast]

The code does not work and my edit to fix it got rejected. Is this some kind of joke?

[Henk Holterman]

@CheesusToast - post your edit as a separate answer then. Point out the differences and the problems it solves.

[wickd]

That's a lot of typing just to an Unique constraint. Jeez

[jakubka]

I believe that IsUnique need to be set to true when creating IndexAttribute in the first example. Like this: new IndexAttribute() { IsUnique = true }. Otherwise it creates just regular (non-unique) index.

[Eitan K]

How would you get this to work with nullable fields?

[bman]

I have tried this and can compile however when I run Update-Database, the index is not added. Anyone knows why and how to solve?

[JMK]

This only works if you also restrict the length of the string, as SQL doesn't allow nvarchar(max) to be used as a key.

[justanotherdev]

@bman, check your migration scripts. I tried this on a string variable not realizing I needed to set a HasMaxLength but after I fixed this, running the migration again did not re-scaffold properly. You should have something like this: AlterColumn("dbo.GiftCards", "CardNumber", c => c.String(nullable: false, maxLength: 50)); CreateIndex("dbo.GiftCards", "CardNumber", unique: true);

[coni2k]

After three and half years, updated the answer... :)

[Henryk Budzinski]

@Yorro, my ConsoleApplication needed using System.ComponentModel.DataAnnotations.Schema;. I can't run my application right now to see if it's right, but if this is OK, better add it below the other using statment for newbies like me ^^'