Unique Constraint in Entity Framework Code First

Question

Question

Is it possible to define a unique constraint on a property using either the fluent syntax or an attribute? If not, what are the workarounds?

I have a user class with a primary key, but I would like to make sure the email address is also unique. Is this possible without editing the database directly?

Solution (based on Matt's answer)

public class MyContext : DbContext {
    public DbSet<User> Users { get; set; }

    public override int SaveChanges() {
        foreach (var item in ChangeTracker.Entries<IModel>())
            item.Entity.Modified = DateTime.Now;

        return base.SaveChanges();
    }

    public class Initializer : IDatabaseInitializer<MyContext> {
        public void InitializeDatabase(MyContext context) {
            if (context.Database.Exists() && !context.Database.CompatibleWithModel(false))
                context.Database.Delete();

            if (!context.Database.Exists()) {
                context.Database.Create();
                context.Database.ExecuteSqlCommand("alter table Users add constraint UniqueUserEmail unique (Email)");
            }
        }
    }
}

Answer 1

As far as I can tell, there's no way to do this with Entity Framework at the moment. However, this isn't just a problem with unique constraints... you may want to create indexes, check constraints, and possibly triggers and other constructs too. Here's a simple pattern you can use with your code-first setup, though admittedly it's not database agnostic:

public class MyRepository : DbContext {
    public DbSet<Whatever> Whatevers { get; set; }

    public class Initializer : IDatabaseInitializer<MyRepository> {
        public void InitializeDatabase(MyRepository context) {
            if (!context.Database.Exists() || !context.Database.ModelMatchesDatabase()) {
                context.Database.DeleteIfExists();
                context.Database.Create();

                context.ObjectContext.ExecuteStoreCommand("CREATE UNIQUE CONSTRAINT...");
                context.ObjectContext.ExecuteStoreCommand("CREATE INDEX...");
                context.ObjectContext.ExecuteStoreCommand("ETC...");
            }
        }
    }
}

Another option is if your domain model is the only method of inserting/updating data in your database, you could implement the uniqueness requirement yourself and leave the database out of it. This is a more portable solution and forces you to be clear about your business rules in your code, but leaves your database open to invalid data getting back-doored.

Answer 2

Starting with EF 6.1 it is now possible:

[Index(IsUnique = true)]
public string EmailAddress { get; set; }

This will get you a unique index instead of unique constraint, strictly speaking. For most practical purposes they are the same.

Answer 3

Not really related to this but it might help in some cases.

If you're looking to create a unique composite index on let's say 2 columns that will act as a constraint for your table, then as of version 4.3 you can use the new migrations mechanism to achieve it:

• http://msdn.microsoft.com/en-us/library/hh770484(v=vs.103).aspx
• http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-code-based-migrations-walkthrough.aspx

Basically you need to insert a call like this in one of your migration scripts:

CreateIndex("TableName", new string[2] { "Column1", "Column2" }, true, "IX_UniqueColumn1AndColumn2");

Something like that:

namespace Sample.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class TableName_SetUniqueCompositeIndex : DbMigration
    {
        public override void Up()
        {
            CreateIndex("TableName", new[] { "Column1", "Column2" }, true, "IX_UniqueColumn1AndColumn2");
        }

        public override void Down()
        {
            DropIndex("TableName", new[] { "Column1", "Column2" });
        }
    }
}

Answer 4

I do a complete hack to get SQL executed when the database is being created. I create my own DatabaseInitializer and inherit from one of the provided initializers.

public class MyDatabaseInitializer : RecreateDatabaseIfModelChanges<MyDbContext>
{
    protected override void Seed(MyDbContext context)
    {
        base.Seed(context);
        context.Database.Connection.StateChange += new StateChangeEventHandler(Connection_StateChange);
    }

    void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        DbConnection cnn = sender as DbConnection;

        if (e.CurrentState == ConnectionState.Open)
        {
            // execute SQL to create indexes and such
        }

        cnn.StateChange -= Connection_StateChange;
    }
}

That's the only place I could find to wedge in my SQL statements.

This is from CTP4. I don't know how it works in CTP5.

Answer 5

Just trying to find out if there was a way to do this, only way I found so far was enforcing it myself, I created an attribute to be added to each class where you supply the name of the fields you need to be unique:

    [System.AttributeUsage(System.AttributeTargets.Class, AllowMultiple=false,Inherited=true)]
public class UniqueAttribute:System.Attribute
{
    private string[] _atts;
    public string[] KeyFields
    {
        get
        {
            return _atts;
        }
    }
    public UniqueAttribute(string keyFields)
    {
        this._atts = keyFields.Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
    }
}

Then in my class I'll add it:

[CustomAttributes.Unique("Name")]
public class Item: BasePOCO
{
    public string Name{get;set;}
    [StringLength(250)]
    public string Description { get; set; }
    [Required]
    public String Category { get; set; }
    [Required]
    public string UOM { get; set; }
    [Required]
}

Finally, I'll add a method in my repository, in the Add method or when Saving Changes like this:

private void ValidateDuplicatedKeys(T entity)
{
    var atts = typeof(T).GetCustomAttributes(typeof(UniqueAttribute), true);
    if (atts == null || atts.Count() < 1)
    {
        return;
    }
    foreach (var att in atts)
    {
        UniqueAttribute uniqueAtt = (UniqueAttribute)att;
        var newkeyValues = from pi in entity.GetType().GetProperties()
                            join k in uniqueAtt.KeyFields on pi.Name equals k
                            select new { KeyField = k, Value = pi.GetValue(entity, null).ToString() };
        foreach (var item in _objectSet)
        {
            var keyValues = from pi in item.GetType().GetProperties()
                            join k in uniqueAtt.KeyFields on pi.Name equals k
                            select new { KeyField = k, Value = pi.GetValue(item, null).ToString() };
            var exists = keyValues.SequenceEqual(newkeyValues);
            if (exists)
            {
                throw new System.Exception("Duplicated Entry found");
            }
        }
    }
}

Not too nice as we need to rely on reflection but this so far is the approach that works for me! =D

Answer 6

Also in 6.1 you can use the fluent syntax version of @mihkelmuur's answer like so:

Property(s => s.EmailAddress).HasColumnAnnotation(IndexAnnotation.AnnotationName,
new IndexAnnotation(
    new IndexAttribute("IX_UniqueEmail") { IsUnique = true }));

The fluent method isnt perfect IMO but at least its possible now.

More deets on Arthur Vickers blog http://blog.oneunicorn.com/2014/02/15/ef-6-1-creating-indexes-with-indexattribute/

Answer 7

An easy way in visual basic using EF5 Code First Migrations

Public Class Sample

    Public Property SampleId As Integer

    <Required>
    <MinLength(1),MaxLength(200)>

    Public Property Code() As String

End Class

The attribute MaxLength is very important for unique index of string type

Run cmd: update-database -verbose

after run cmd: add-migration 1

in the generated file

Public Partial Class _1
    Inherits DbMigration

    Public Overrides Sub Up()
        CreateIndex("dbo.Sample", "Code", unique:=True, name:="IX_Sample_Code")
    End Sub

    Public Overrides Sub Down()
        'DropIndex if you need it
    End Sub

End Class

Answer 8

Similar to Tobias Schittkowski's answer but C# and has the capability to have multiple fields in the constrtaints.

To use this, just place a [Unique] on any field you wish to be unique. For strings, you will have to do something like (note the MaxLength attribute):

[Unique]
[MaxLength(450)] // nvarchar(450) is max allowed to be in a key
public string Name { get; set; }

because the default string field is nvarchar(max) and that will not be allowed in a key.

For multiple fields in the constraint you can do:

[Unique(Name="UniqueValuePairConstraint", Position=1)]
public int Value1 { get; set; }
[Unique(Name="UniqueValuePairConstraint", Position=2)]
public int Value2 { get; set; }

First, the UniqueAttribute:

/// <summary>
/// The unique attribute. Use to mark a field as unique. The
/// <see cref="DatabaseInitializer"/> looks for this attribute to 
/// create unique constraints in tables.
/// </summary>
internal class UniqueAttribute : Attribute
{
    /// <summary>
    /// Gets or sets the name of the unique constraint. A name will be 
    /// created for unnamed unique constraints. You must name your
    /// constraint if you want multiple fields in the constraint. If your 
    /// constraint has only one field, then this property can be ignored.
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the position of the field in the constraint, lower 
    /// numbers come first. The order is undefined for two fields with 
    /// the same position. The default position is 0.
    /// </summary>
    public int Position { get; set; }
}

Then, include a useful extension to get the database table name from a type:

public static class Extensions
{
    /// <summary>
    /// Get a table name for a class using a DbContext.
    /// </summary>
    /// <param name="context">
    /// The context.
    /// </param>
    /// <param name="type">
    /// The class to look up the table name for.
    /// </param>
    /// <returns>
    /// The table name; null on failure;
    /// </returns>
    /// <remarks>
    /// <para>
    /// Like:
    /// <code>
    ///   DbContext context = ...;
    ///   string table = context.GetTableName&lt;Foo&gt;();
    /// </code>
    /// </para>
    /// <para>
    /// This code uses ObjectQuery.ToTraceString to generate an SQL 
    /// select statement for an entity, and then extract the table
    /// name from that statement.
    /// </para>
    /// </remarks>
    public static string GetTableName(this DbContext context, Type type)
    {
        return ((IObjectContextAdapter)context)
               .ObjectContext.GetTableName(type);
    }

    /// <summary>
    /// Get a table name for a class using an ObjectContext.
    /// </summary>
    /// <param name="context">
    /// The context.
    /// </param>
    /// <param name="type">
    /// The class to look up the table name for.
    /// </param>
    /// <returns>
    /// The table name; null on failure;
    /// </returns>
    /// <remarks>
    /// <para>
    /// Like:
    /// <code>
    ///   ObjectContext context = ...;
    ///   string table = context.GetTableName&lt;Foo&gt;();
    /// </code>
    /// </para>
    /// <para>
    /// This code uses ObjectQuery.ToTraceString to generate an SQL 
    /// select statement for an entity, and then extract the table
    /// name from that statement.
    /// </para>
    /// </remarks>
    public static string GetTableName(this ObjectContext context, Type type)
    {
        var genericTypes = new[] { type };
        var takesNoParameters = new Type[0];
        var noParams = new object[0];
        object objectSet = context.GetType()
                            .GetMethod("CreateObjectSet", takesNoParameters)
                            .MakeGenericMethod(genericTypes)
                            .Invoke(context, noParams);
        var sql = (string)objectSet.GetType()
                  .GetMethod("ToTraceString", takesNoParameters)
                  .Invoke(objectSet, noParams);
        Match match = 
            Regex.Match(sql, @"FROM\s+(.*)\s+AS", RegexOptions.IgnoreCase);
        return match.Success ? match.Groups[1].Value : null;
    }
}

Then, the database initializer:

/// <summary>
///     The database initializer.
/// </summary>
public class DatabaseInitializer : IDatabaseInitializer<PedContext>
{
    /// <summary>
    /// Initialize the database.
    /// </summary>
    /// <param name="context">
    /// The context.
    /// </param>
    public void InitializeDatabase(FooContext context)
    {
        // if the database has changed, recreate it.
        if (context.Database.Exists()
            && !context.Database.CompatibleWithModel(false))
        {
            context.Database.Delete();
        }

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

            // Look for database tables in the context. Tables are of
            // type DbSet<>.
            foreach (PropertyInfo contextPropertyInfo in 
                     context.GetType().GetProperties())
            {
                var contextPropertyType = contextPropertyInfo.PropertyType;
                if (contextPropertyType.IsGenericType
                    && contextPropertyType.Name.Equals("DbSet`1"))
                {
                    Type tableType = 
                        contextPropertyType.GetGenericArguments()[0];
                    var tableName = context.GetTableName(tableType);
                    foreach (var uc in UniqueConstraints(tableType, tableName))
                    {
                        context.Database.ExecuteSqlCommand(uc);
                    }
                }
            }

            // this is a good place to seed the database
            context.SaveChanges();
        }
    }

    /// <summary>
    /// Get a list of TSQL commands to create unique constraints on the given 
    /// table. Looks through the table for fields with the UniqueAttribute
    /// and uses those and the table name to build the TSQL strings.
    /// </summary>
    /// <param name="tableClass">
    /// The class that expresses the database table.
    /// </param>
    /// <param name="tableName">
    /// The table name in the database.
    /// </param>
    /// <returns>
    /// The list of TSQL statements for altering the table to include unique 
    /// constraints.
    /// </returns>
    private static IEnumerable<string> UniqueConstraints(
        Type tableClass, string tableName)
    {
        // the key is the name of the constraint and the value is a list 
        // of (position,field) pairs kept in order of position - the entry
        // with the lowest position is first.
        var uniqueConstraints = 
            new Dictionary<string, List<Tuple<int, string>>>();
        foreach (PropertyInfo entityPropertyInfo in tableClass.GetProperties())
        {
            var unique = entityPropertyInfo.GetCustomAttributes(true)
                         .OfType<UniqueAttribute>().FirstOrDefault();
            if (unique != null)
            {
                string fieldName = entityPropertyInfo.Name;

                // use the name field in the UniqueAttribute or create a
                // name if none is given
                string constraintName = unique.Name
                                        ?? string.Format(
                                            "constraint_{0}_unique_{1}",
                                            tableName
                                               .Replace("[", string.Empty)
                                               .Replace("]", string.Empty)
                                               .Replace(".", "_"),
                                            fieldName);

                List<Tuple<int, string>> constraintEntry;
                if (!uniqueConstraints.TryGetValue(
                        constraintName, out constraintEntry))
                {
                    uniqueConstraints.Add(
                        constraintName, 
                        new List<Tuple<int, string>> 
                        {
                            new Tuple<int, string>(
                                unique.Position, fieldName) 
                        });
                }
                else
                {
                    // keep the list of fields in order of position
                    for (int i = 0; ; ++i)
                    {
                        if (i == constraintEntry.Count)
                        {
                            constraintEntry.Add(
                                new Tuple<int, string>(
                                    unique.Position, fieldName));
                            break;
                        }

                        if (unique.Position < constraintEntry[i].Item1)
                        {
                            constraintEntry.Insert(
                                i, 
                                new Tuple<int, string>(
                                    unique.Position, fieldName));
                            break;
                        }
                    }
                }
            }
        }

        return
            uniqueConstraints.Select(
                uc =>
                string.Format(
                    "ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE ({2})",
                    tableName,
                    uc.Key,
                    string.Join(",", uc.Value.Select(v => v.Item2))));
    }
}

Answer 9

I solved the problem by reflection (sorry, folks, VB.Net...)

First, define an attribute UniqueAttribute:

<AttributeUsage(AttributeTargets.Property, AllowMultiple:=False, Inherited:=True)> _
Public Class UniqueAttribute
    Inherits Attribute

End Class

Then, enhance your model like

<Table("Person")> _
Public Class Person

    <Unique()> _
    Public Property Username() As String

End Class

Finally, create a custom DatabaseInitializer (In my version, I recreate the DB on DB changes only if in debug mode...). In this DatabaseInitializer, the indices are automatically created based on the Unique-Attributes:

Imports System.Data.Entity
Imports System.Reflection
Imports System.Linq
Imports System.ComponentModel.DataAnnotations

Public Class DatabaseInitializer
    Implements IDatabaseInitializer(Of DBContext)

    Public Sub InitializeDatabase(context As DBContext) Implements IDatabaseInitializer(Of DBContext).InitializeDatabase
        Dim t As Type
        Dim tableName As String
        Dim fieldName As String

        If Debugger.IsAttached AndAlso context.Database.Exists AndAlso Not context.Database.CompatibleWithModel(False) Then
            context.Database.Delete()
        End If

        If Not context.Database.Exists Then
            context.Database.Create()

            For Each pi As PropertyInfo In GetType(DBContext).GetProperties
                If pi.PropertyType.IsGenericType AndAlso _
                    pi.PropertyType.Name.Contains("DbSet") Then

                    t = pi.PropertyType.GetGenericArguments(0)

                    tableName = t.GetCustomAttributes(True).OfType(Of TableAttribute).FirstOrDefault.Name
                    For Each piEntity In t.GetProperties
                        If piEntity.GetCustomAttributes(True).OfType(Of Model.UniqueAttribute).Any Then

                            fieldName = piEntity.Name
                            context.Database.ExecuteSqlCommand("ALTER TABLE " & tableName & " ADD CONSTRAINT con_Unique_" & tableName & "_" & fieldName & " UNIQUE (" & fieldName & ")")

                        End If
                    Next
                End If
            Next

        End If

    End Sub

End Class

Perhaps this helps...

Answer 10

If you override the ValidateEntity method in your DbContext class, you can put the logic there as well. The advantage here is that you'll have full access to all of your DbSets. Here's an example:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.Entity.Validation;
using System.Linq;

namespace MvcEfClient.Models
{
    public class Location
    {
        [Key]
        public int LocationId { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }
    }

    public class CommitteeMeetingContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

        protected override DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, IDictionary<object, object> items)
        {
            List<DbValidationError> validationErrors = new List<DbValidationError>();

            // Check for duplicate location names

            if (entityEntry.Entity is Location)
            {
                Location location = entityEntry.Entity as Location;

                // Select the existing location

                var existingLocation = (from l in Locations
                                        where l.Name == location.Name && l.LocationId != location.LocationId
                                        select l).FirstOrDefault();

                // If there is an existing location, throw an error

                if (existingLocation != null)
                {
                    validationErrors.Add(new DbValidationError("Name", "There is already a location with the name '" + location.Name + "'"));
                    return new DbEntityValidationResult(entityEntry, validationErrors);
                }
            }

            return base.ValidateEntity(entityEntry, items);
        }

        public DbSet<Location> Locations { get; set; }
    }
}

Answer 11

If you are using EF5 and still have this question, solution below solved it for me.

I am using code first approach, therefore putting:

this.Sql("CREATE UNIQUE NONCLUSTERED INDEX idx_unique_username ON dbo.Users(Username) WHERE Username IS NOT NULL;");

in the migration script did the job well. It also allows NULL values!

Answer 12

With EF Code First approach, one can implement attribute-based unique constraint support using the following technique.

Create a marker attribute

[AttributeUsage(AttributeTargets.Property)]
public class UniqueAttribute : System.Attribute { }

Mark properties you'd like to be unique on entities, e.g.

[Unique]
public string EmailAddress { get; set; }

Create a database initializer or use an existing one to create the unique constraints

public class DbInitializer : IDatabaseInitializer<DbContext>
{
    public void InitializeDatabase(DbContext db)
    {
        if (db.Database.Exists() && !db.Database.CompatibleWithModel(false))
        {
            db.Database.Delete();
        }

        if (!db.Database.Exists())
        {
            db.Database.Create();
            CreateUniqueIndexes(db);
        }
    }

    private static void CreateUniqueIndexes(DbContext db)
    {
        var props = from p in typeof(AppDbContext).GetProperties()
                    where p.PropertyType.IsGenericType
                       && p.PropertyType.GetGenericTypeDefinition()
                       == typeof(DbSet<>)
                    select p;

        foreach (var prop in props)
        {
            var type = prop.PropertyType.GetGenericArguments()[0];
            var fields = from p in type.GetProperties()
                         where p.GetCustomAttributes(typeof(UniqueAttribute),
                                                     true).Any()
                         select p.Name;

            foreach (var field in fields)
            {
                const string sql = "ALTER TABLE dbo.[{0}] ADD CONSTRAINT"
                                 + " [UK_dbo.{0}_{1}] UNIQUE ([{1}])";
                var command = String.Format(sql, type.Name, field);
                db.Database.ExecuteSqlCommand(command);
            }
        }
    }   
}

Set your database context to use this initializer in startup code (e.g. in main() or Application_Start())

Database.SetInitializer(new DbInitializer());

Solution is similar to mheyman's, with a simplification of not supporting composite keys. To be used with EF 5.0+.

Answer 13

Fluent Api solution:

modelBuilder.Entity<User>(entity =>
{
    entity.HasIndex(e => e.UserId)
          .HasName("IX_User")
          .IsUnique();

    entity.HasAlternateKey(u => u.Email);

    entity.HasIndex(e => e.Email)
          .HasName("IX_Email")
          .IsUnique();
});

Answer 14

I faced that problem today and finally i was able to solve it. I don't know if is a right approach but at least I can keep going:

public class Person : IValidatableObject
{
    public virtual int ID { get; set; }
    public virtual string Name { get; set; }


    public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
    {
        var field = new[] { "Name" }; // Must be the same as the property

        PFContext db = new PFContext();

        Person person = validationContext.ObjectInstance as Person;

        var existingPerson = db.Persons.FirstOrDefault(a => a.Name == person.Name);

        if (existingPerson != null)
        {
            yield return new ValidationResult("That name is already in the db", field);
        }
    }
}

Answer 15

Use a unique property validator.

protected override DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, IDictionary<object, object> items) {
   var validation_state = base.ValidateEntity(entityEntry, items);
   if (entityEntry.Entity is User) {
       var entity = (User)entityEntry.Entity;
       var set = Users;

       //check name unique
       if (!(set.Any(any_entity => any_entity.Name == entity.Name))) {} else {
           validation_state.ValidationErrors.Add(new DbValidationError("Name", "The Name field must be unique."));
       }
   }
   return validation_state;
}

ValidateEntity is not called within the same database transaction. Therefore, there may be race conditions with other entities in the database. You have to hack EF somewhat to force a transaction around the SaveChanges (and therefore, ValidateEntity). DBContext cannot open the connection directly, but ObjectContext can.

using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required)) {
   ((IObjectContextAdapter)data_context).ObjectContext.Connection.Open();
   data_context.SaveChanges();
   transaction.Complete();
}

Answer 16

According to http://blogs.msdn.com/b/adonet/archive/2014/02/11/ef-6-1-0-beta-1-available.aspx, EF 6.1 will have an IndexAttribute to help us out.

Answer 17

After reading this question I had my own question in the process of trying to implement an attribute for designating properties as unique keys like Mihkel Müür's, Tobias Schittkowski's and mheyman's answers suggest: Map Entity Framework code properties to database columns (CSpace to SSpace)

I finally arrived at this answer which can map both scalar and navigation properties down to database columns and create a unique index in a specific sequence designated on the attribute. This code assumes you have implemented a UniqueAttribute with a Sequence property, and applied it to EF entity class properties that should represent the entity's unique key (other than the primary key).

Note: This code relies on EF version 6.1 (or later) which exposes EntityContainerMapping not available in prior versions.

Public Sub InitializeDatabase(context As MyDB) Implements IDatabaseInitializer(Of MyDB).InitializeDatabase
    If context.Database.CreateIfNotExists Then
        Dim ws = DirectCast(context, System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext.MetadataWorkspace
        Dim oSpace = ws.GetItemCollection(Core.Metadata.Edm.DataSpace.OSpace)
        Dim entityTypes = oSpace.GetItems(Of EntityType)()
        Dim entityContainer = ws.GetItems(Of EntityContainer)(DataSpace.CSpace).Single()
        Dim entityMapping = ws.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace).Single.EntitySetMappings
        Dim associations = ws.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace).Single.AssociationSetMappings
        For Each setType In entityTypes
           Dim cSpaceEntitySet = entityContainer.EntitySets.SingleOrDefault( _
              Function(t) t.ElementType.Name = setType.Name)
           If cSpaceEntitySet Is Nothing Then Continue For ' Derived entities will be skipped
           Dim sSpaceEntitySet = entityMapping.Single(Function(t) t.EntitySet Is cSpaceEntitySet)
           Dim tableInfo As MappingFragment
           If sSpaceEntitySet.EntityTypeMappings.Count = 1 Then
              tableInfo = sSpaceEntitySet.EntityTypeMappings.Single.Fragments.Single
           Else
              ' Select only the mapping (esp. PropertyMappings) for the base class
              tableInfo = sSpaceEntitySet.EntityTypeMappings.Where(Function(m) m.IsOfEntityTypes.Count _
                 = 1 AndAlso m.IsOfEntityTypes.Single.Name Is setType.Name).Single().Fragments.Single
           End If
           Dim tableName = If(tableInfo.StoreEntitySet.Table, tableInfo.StoreEntitySet.Name)
           Dim schema = tableInfo.StoreEntitySet.Schema
           Dim clrType = Type.GetType(setType.FullName)
           Dim uniqueCols As IList(Of String) = Nothing
           For Each propMap In tableInfo.PropertyMappings.OfType(Of ScalarPropertyMapping)()
              Dim clrProp = clrType.GetProperty(propMap.Property.Name)
              If Attribute.GetCustomAttribute(clrProp, GetType(UniqueAttribute)) IsNot Nothing Then
                 If uniqueCols Is Nothing Then uniqueCols = New List(Of String)
                 uniqueCols.Add(propMap.Column.Name)
              End If
           Next
           For Each navProp In setType.NavigationProperties
              Dim clrProp = clrType.GetProperty(navProp.Name)
              If Attribute.GetCustomAttribute(clrProp, GetType(UniqueAttribute)) IsNot Nothing Then
                 Dim assocMap = associations.SingleOrDefault(Function(a) _
                    a.AssociationSet.ElementType.FullName = navProp.RelationshipType.FullName)
                 Dim sProp = assocMap.Conditions.Single
                 If uniqueCols Is Nothing Then uniqueCols = New List(Of String)
                 uniqueCols.Add(sProp.Column.Name)
              End If
           Next
           If uniqueCols IsNot Nothing Then
              Dim propList = uniqueCols.ToArray()
              context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX IX_" & tableName & "_" & String.Join("_", propList) _
                 & " ON " & schema & "." & tableName & "(" & String.Join(",", propList) & ")")
           End If
        Next
    End If
End Sub

Answer 18

For those using code first configurations you can also use the IndexAttribute object as a ColumnAnnotation and set its IsUnique property to true.

In example:

var indexAttribute = new IndexAttribute("IX_name", 1) {IsUnique = true};

Property(i => i.Name).HasColumnAnnotation("Index",new IndexAnnotation(indexAttribute));

This will create a unique index named IX_name on the Name column.

Answer 19

Sorry for late answer but i found it good to shae it with you

I have posted about this at code project

In general, it depends on the attributes that you put on the classes to generate your unique indexes