Unique key with EF code first

Question

I have a following model in my project

public class Category
{   
    public Guid ID { get; set; }
    [Required(ErrorMessage = "Title cannot be empty")]
    public string Title { get; set; }
}

and I'm trying to make Title as unique key, I googled for the solution, but couldn't find any. Can any suggest me how to do it, please?

Frazzled

Answer 1

Unfortunately you can't define it as unique key in code first because EF doesn't support unique keys at all (it is hopefully planned for next major release). What you can do is to create custom database intializer and add unique index manually by calling SQL command:

public class MyInitializer : CreateDatabaseIfNotExists<MyContext>
{
  protected override void Seed(MyContext context)
  {
    context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX IX_Category_Title ON Categories (Title)");
  }
}

And you must set this initializer in the bootstrap of your application.

Database.SetInitializer<MyContext>(new MyInitializer());

Edit

Now (EF 6.1 onwards )you can easily have unique constrains ,

[Index("TitleIndex", IsUnique = true)]
 public string Title { get; set; }

Stamey

I work with MVC 3 and EF 4 and the code don't recognize ExecuteSqlCommand in context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX IX_Category_Title ON Categories (Title)"); is this about version or otherthing? – Cyrilcyrill

@Saeid: This is for DbContext API (EFv4.1). There is no database initializer in EFv4. ObjectContext API offers its own methods to execute SQL directly - ExecuteStoreCommand. – Stamey

Also a great way to add default constraints (e.g. GETDATE(), etc.) – Mercurous

Seed is executed multiple time - would this not error out as the index (or function/stored procedure/or whatever) already exists in the database? – Doubletongued

@codputer: In this case the Seed is executed only once because it doesn't use migrations. In case of migrations you can create index directly in Up method. – Stamey

+1: good answer, it got me past an issue. But I only need to run this once. And it seems that my initialization is happening all the time - i.e., even when a model doesn't change. I'll search SO for a remedy. – Cordie

This has changed since then. – Infidelity

When you put an index on a string by annotating with an attribute such as [Index("TitleIndex", IsUnique = true)] you ALSO need to limit the length of the string. Don't want SQL Server trying to index a VarChar(Max) type. So set the maximum length too. For example: [MaxLength(255)] Otherwise it may throw an exception and tell you that you are not allowed to put an Index on this Property because it is the "wrong type". You are allowed to put an index on a string provided the string is not too long. – Buyse

In net 5, it say only use for class [AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]. How to use it for string? thank – Brandy

Answer 2

First create the custom attribute class:

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class UniqueAttribute : ValidationAttribute
{
   public override Boolean IsValid(Object value)
    {
        // constraint implemented on database
        return true;
    }
}

Then add to your classes:

public class Email
{
    [Key]
    public int EmailID { get; set; }

    public int PersonId { get; set; }

    [Unique]
    [Required]
    [MaxLength(100)]
    public string EmailAddress { get; set; }
    public virtual bool IsDefault { get; set; }
    public virtual Boolean IsApprovedForLogin { get; set; }
    public virtual String ConfirmationToken { get; set; }

    [ForeignKey("PersonId")]
    public virtual Person Person { get; set; }
}

Then add a Initializer on your DbContext:

public class Initializer : IDatabaseInitializer<myEntities>
{
    public void InitializeDatabase(myEntities context)
    {
        if (System.Diagnostics.Debugger.IsAttached && context.Database.Exists() && !context.Database.CompatibleWithModel(false))
        {
            context.Database.Delete();
        }

        if (!context.Database.Exists())
        {
            context.Database.Create();

            var contextObject = context as System.Object;
            var contextType = contextObject.GetType();
            var properties = contextType.GetProperties();
            System.Type t = null;
            string tableName = null;
            string fieldName = null;
            foreach (var pi in properties)
            {
                if (pi.PropertyType.IsGenericType && pi.PropertyType.Name.Contains("DbSet"))
                {
                    t = pi.PropertyType.GetGenericArguments()[0];

                    var mytableName = t.GetCustomAttributes(typeof(TableAttribute), true);
                    if (mytableName.Length > 0)
                    {
                        TableAttribute mytable = mytableName[0] as TableAttribute;
                        tableName = mytable.Name;
                    }
                    else
                    {
                        tableName = pi.Name;
                    }

                    foreach (var piEntity in t.GetProperties())
                    {
                        if (piEntity.GetCustomAttributes(typeof(UniqueAttribute), true).Length > 0)
                        {
                            fieldName = piEntity.Name;
                            context.Database.ExecuteSqlCommand("ALTER TABLE " + tableName + " ADD CONSTRAINT con_Unique_" + tableName + "_" + fieldName + " UNIQUE (" + fieldName + ")");
                        }
                    }
                }
            }
        }
    }
}

And for last add the Initializer at Application_Start inside Global.asax.cs

System.Data.Entity.Database.SetInitializer<MyApp.Models.DomainModels.myEntities>(new MyApp.Models.DomainModels.myEntities.Initializer());

That's it. based on the vb code at https://stackoverflow.com/a/7426773

Entasis

Couple of corrections. 1. tableName should be bracket enclosed during ExecuteSqlCommand 2. if you are using non-pluralized names, use else { tableName = t.Name } – Vanadinite

Answer 3

Here is the VB.Net version - note the implementation of generics that is a little different, at the class level.

Public Class MyInitializer(Of T As DbContext)
    Inherits CreateDatabaseIfNotExists(Of T)
    Protected Overrides Sub Seed(context As T)
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX IX_Category_Title ON Categories (Title)")
    End Sub
End Class

Implore

oh come on - what's wrong with adding a concise VB version, for vb users with the exact same issue? isn't this the point of SO - providing a resource not only for the original poster? Additionally, as noted, the implementation is somewhat different. – Implore

Answer 4

I create this class (which ws enhanced from another Stackoverflow answer -Execute a large SQL script (with GO commands)), which allows me to drop in the SQL scripts into a directory, and have them all executed each time they are required (Seed, or Migration). I'm not going to leave this open after I deploy to production, but during development it makes it easy to apply scripts each time the DB is recreated.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//dll Microsoft.SqlServer.Smo
//dll Microsoft.SqlServer.Management.Sdk.Sfc
//dll Microsoft.SqlServer.ConnectionInfo
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Monitor.Common;

namespace MonitorDB.DataLayer.Migrations
{
  public class ExecuteSQLScripts :Monitor.Common.ExceptionHandling
  {
    public ExecuteSQLScripts()
    {
}

public bool ExecuteScriptsInDirectory(DBContext.SolArcMsgMonitorContext context, string scriptDirectory)
{
  bool Result = false;
  try
  {
    SqlConnection connection = new SqlConnection(context.Database.Connection.ConnectionString);
    Server server = new Server(new ServerConnection(connection));

    DirectoryInfo di = new DirectoryInfo(scriptDirectory);
    FileInfo[] rgFiles = di.GetFiles("*.sql");
    foreach (FileInfo fi in rgFiles)
    {

      FileInfo fileInfo = new FileInfo(fi.FullName);
      string script = fileInfo.OpenText().ReadToEnd();

      server.ConnectionContext.ExecuteNonQuery(script);
    }
    Result = true;
  }
  catch (Exception ex)
  {
    CatchException("ExecuteScriptsInDirectory", ex);
  }
  return Result;
}

} }

Here is what the VS Solution looks like:

Doubletongued

Answer 5

I found this solution which although not creating a unique-key in the SQL level, it uses DataAnnotations validation, check it out:

http://blogs.microsoft.co.il/blogs/shimmy/archive/2012/01/23/validationattribute-that-validates-a-unique-field-against-its-fellow-rows-in-the-database.aspx

Fitted