Entity Framework code first unique column

cpoDesign picture cpoDesign · May 16, 2012 · Viewed 113.8k times · Source

I am using Entity Framework 4.3 and using Code Fist.

I have a class

public class User
{
   public int UserId{get;set;}
   public string UserName{get;set;}
}

How do I tell Entity Framework that UserName has to be unique when creating database table? I would prefer to use data anotations instead of configuration file if possible.

Answer

juFo picture juFo · Apr 14, 2014

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