Entity Framework code first unique column

Question

In Entity Framework 6.1+ you can use this attribute on your model:

[Index(IsUnique=true)]

You can find it in this namespace:

using System.ComponentModel.DataAnnotations.Schema;

If your model field is a string, make sure it is not set to nvarchar(MAX) in SQL Server or you will see this error with Entity Framework Code First:

Column 'x' in table 'dbo.y' is of a type that is invalid for use as a key column in an index.

The reason is because of this:

SQL Server retains the 900-byte limit for the maximum total size of all index key columns."

(from: http://msdn.microsoft.com/en-us/library/ms191241.aspx )

You can solve this by setting a maximum string length on your model:

[StringLength(450)]

Your model will look like this now in EF CF 6.1+:

public class User
{
   public int UserId{get;set;}
   [StringLength(450)]
   [Index(IsUnique=true)]
   public string UserName{get;set;}
}

Update:

if you use Fluent:

  public class UserMap : EntityTypeConfiguration<User>
  {
    public UserMap()
    {
      // ....
      Property(x => x.Name).IsRequired().HasMaxLength(450).HasColumnAnnotation("Index", new IndexAnnotation(new[] { new IndexAttribute("Index") { IsUnique = true } }));
    }
  }

and use in your modelBuilder:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  // ...
  modelBuilder.Configurations.Add(new UserMap());
  // ...
}

Update 2

for EntityFrameworkCore see also this topic: https://github.com/aspnet/EntityFrameworkCore/issues/1698

Update 3

for EF6.2 see: https://github.com/aspnet/EntityFramework6/issues/274

Update 4

ASP.NET Core Mvc 2.2 with EF Core:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Unique { get; set; }

EF doesn't support unique columns except keys. If you are using EF Migrations you can force EF to create unique index on UserName column (in migration code, not by any annotation) but the uniqueness will be enforced only in the database. If you try to save duplicate value you will have to catch exception (constraint violation) fired by the database.

From your code it becomes apparent that you use POCO. Having another key is unnecessary: you can add an index as suggested by juFo.
If you use Fluent API instead of attributing UserName property your column annotation should look like this:

this.Property(p => p.UserName)
    .HasColumnAnnotation("Index", new IndexAnnotation(new[] { 
        new IndexAttribute("Index") { IsUnique = true } 
    }
));

This will create the following SQL script:

CREATE UNIQUE NONCLUSTERED INDEX [Index] ON [dbo].[Users]
(
    [UserName] ASC
)
WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

If you attempt to insert multiple Users having the same UserName you'll get a DbUpdateException with the following message:

Cannot insert duplicate key row in object 'dbo.Users' with unique index 'Index'. 
The duplicate key value is (...).
The statement has been terminated.

Again, column annotations are not available in Entity Framework prior to version 6.1.

In EF 6.2 using FluentAPI, you can use HasIndex()

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

Note that in Entity Framework 6.1 (currently in beta) will support the IndexAttribute to annotate the index properties which will automatically result in a (unique) index in your Code First Migrations.