Entity Framework code first unique column

Question

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

User · Answer

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.

User · Answer

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 lt User gt          public UserMap                             Property x   gt  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

User · Answer

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

User · Answer

Solution for EF4 3  Unique UserName  Add data annotation over column as     Index IsUnique   true     MaxLength 255      for code-first implementations  public string UserName get set     Unique ID    I have added decoration  Key  over my column and done   Same solution as described here  https   msdn microsoft com en-gb data jj591583 aspx  IE    Key  public int UserId get set     Alternative answers  using data annotation   Key  DatabaseGenerated DatabaseGeneratedOption Identity    Column  UserId      using mapping    mb Entity lt User gt                 HasKey i   gt  i UserId           mb User lt User gt               Property i   gt  i UserId             HasDatabaseGeneratedOption DatabaseGeneratedOption Identity             HasColumnName  UserId

User · Answer

In EF 6 2 using FluentAPI  you can use HasIndex    modelBuilder Entity lt User gt    HasIndex u   gt  u UserName  IsUnique

User · Answer

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

[entity-framework] Entity Framework code first unique column

Examples related to entity-framework

Examples related to data-annotations

Examples related to entity-framework-4.3