Entity Framework Code First Fluent Api: Adding Indexes to columns

Question

I'm running EF 4.2 CF and want to create indexes on certain columns in my POCO objects.

As an example lets say we have this employee class:

public class Employee
{
  public int EmployeeID { get; set; }
  public string EmployeeCode { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public DateTime HireDate { get; set; }
}

We often do searches for employees by their EmployeeCode and since there are a lot of employees it would be nice to have that indexed for performance reasons.

Can we do this with fluent api somehow? or perhaps data annotations?

I know it is possible to execute sql commands something like this:

context.Database.ExecuteSqlCommand("CREATE INDEX IX_NAME ON ...");

I would very much like to avoid raw SQL like that.

i know this does not exist but looking for something along those lines:

class EmployeeConfiguration : EntityTypeConfiguration<Employee>
    {
        internal EmployeeConfiguration()
        {
            this.HasIndex(e => e.EmployeeCode)
                .HasIndex(e => e.FirstName)
                .HasIndex(e => e.LastName);
        }
    }

or maybe using System.ComponentModel.DataAnnotations the POCO could look like this (again i know this does not exist):

public class Employee
{
  public int EmployeeID { get; set; }
  [Indexed]
  public string EmployeeCode { get; set; }
  [Indexed]
  public string FirstName { get; set; }
  [Indexed]
  public string LastName { get; set; }
  public DateTime HireDate { get; set; }
}

Anyone have any ideas on how to do this, or if there are any plans to implement a way to do this, the code first way?

UPDATE: As mentioned in the answer by Robba, this feature is implemented in EF version 6.1

Sirois

see solution here: https://mcmap.net/q/157696/-entity-framework-code-first-unique-column – Perpetrate

Answer 1

After Migrations was introduced in EF 4.3 you can now add indexes when modifying or creating a table. Here is an excerpt from the EF 4.3 Code-Based Migrations Walkthrough from the ADO.NET team blog

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

    public partial class AddPostClass : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Posts",
                c => new
                    {
                        PostId = c.Int(nullable: false, identity: true),
                        Title = c.String(maxLength: 200),
                        Content = c.String(),
                        BlogId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.PostId)
                .ForeignKey("Blogs", t => t.BlogId, cascadeDelete: true)
                .Index(t => t.BlogId)
                .Index(p => p.Title, unique: true);

            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
        }

        public override void Down()
        {
            DropIndex("Posts", new[] { "BlogId" });
            DropForeignKey("Posts", "BlogId", "Blogs");
            DropColumn("Blogs", "Rating");
            DropTable("Posts");
        }
    }
}

This is a nice strongly typed way to add the indexes, which was what i was looking for when i first posted the question.

Sirois

Point I was making was that the original question referred to 4.2 :) – Pallette

This solution isn't really good enough. The indexes added in the migration will be lost if the database is created from scratch. Really you want a data annotation for an index. – Occlusion

@Occlusion I agree completely, either that or a way to set it up via EntityTypeConfigurations – Sirois

@Occlusion If the database is created from scratch, all the migrations will be run and the indexes will be recreated, so it should be no problem. – Vapory

It's only a problem if the migration is re-scaffolded, since the migration tools dont know about the added index. Recreating the database from scratch should actually be fine, if you have the indexes in generated migration. – Sirois

@Vapory In my experience that doesn't happen. It doesn't run any migrations; it simply creates the database to correspond with the latest version of the data model, and inserts a single entry into the migrations history table for an initial create. – Occlusion

Hmm, then you could have some incorrect configuration. Have you set an initializer for the database using System.Data.Entity.Database.SetInitializer? I'm using MigrateDatabaseToLatestVersion which will run all migrations, then seeding. – Vapory

Answer 2

You could create an attribute called indexed (as you suggested), which is then picked up in a custom initializer.

I created the following attribute:

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
public class IndexAttribute : Attribute
{
    public IndexAttribute(bool isUnique = false, bool isClustered = false, SortOrder sortOrder = SortOrder.Ascending)
    {
        IsUnique = isUnique;
        IsClustered = isClustered;
        SortOrder = sortOrder == SortOrder.Unspecified ? SortOrder.Ascending : sortOrder;

    }

    public bool IsUnique { get; private set; }
    public bool IsClustered { get; private set; }
    public SortOrder SortOrder { get; private set; }
    //public string Where { get; private set; }
}

I then created a custom initializer which got a list of the table names created for the entities in my context. I have two base classes which all my entities inherit, so I did the following to get the table names:

 var baseEF = typeof (BaseEFEntity);
        var baseLink = typeof (BaseLinkTable);
        var types =
            AppDomain.CurrentDomain.GetAssemblies().ToList().SelectMany(s => s.GetTypes()).Where(
                baseEF.IsAssignableFrom).Union(AppDomain.CurrentDomain.GetAssemblies().ToList().SelectMany(
                    s => s.GetTypes()).Where(
                        baseLink.IsAssignableFrom));

        var sqlScript = context.ObjectContext.CreateDatabaseScript();

        foreach (var type in types)
        {
            var table = (TableAttribute) type.GetCustomAttributes(typeof (TableAttribute), true).FirstOrDefault();
            var tableName = (table != null ? table.Name : null) ?? Pluralizer.Pluralize(type.Name);

I then found all the properties on each entity that have this attribute and then execute a SQL command to generate the index on each property. Sweet!

//Check that a table exists
            if (sqlScript.ToLower().Contains(string.Format(CREATETABLELOOKUP, tableName.ToLower())))
            {

                //indexes

                var indexAttrib = typeof (IndexAttribute);
                properties = type.GetProperties().Where(prop => Attribute.IsDefined(prop, indexAttrib));
                foreach (var property in properties)
                {
                    var attributes = property.GetCustomAttributes(indexAttrib, true).ToList();

                    foreach (IndexAttribute index in attributes)
                    {
                        var indexName = string.Format(INDEXNAMEFORMAT, tableName, property.Name,
                                                      attributes.Count > 1
                                                          ? UNDERSCORE + (attributes.IndexOf(index) + 1)
                                                          : string.Empty);
                        try
                        {
                            context.ObjectContext.ExecuteStoreCommand(
                                string.Format(INDEX_STRING, indexName,
                                              tableName,
                                              property.Name,
                                              index.IsUnique ? UNIQUE : string.Empty,
                                              index.IsClustered ? CLUSTERED : NONCLUSTERED,
                                              index.SortOrder == SortOrder.Ascending ? ASC : DESC));
                        }
                        catch (Exception)
                        {
                        }
                    }
                }

I even went on to add class based indexes (which could have multiple columns) , unique constraints and default constraints all in the same way. Whats also really nice is that if you put these attributes on an inherited class the index or constraint gets applied to all the classes (tables) that inherit it.

BTW the pluralizer helper contains the following:

public static class Pluralizer
{
    private static object _pluralizer;
    private static MethodInfo _pluralizationMethod;

    public static string Pluralize(string word)
    {
        CreatePluralizer();
        return (string) _pluralizationMethod.Invoke(_pluralizer, new object[] {word});
    }

    public static void CreatePluralizer()
    {
        if (_pluralizer == null)
        {
            var aseembly = typeof (DbContext).Assembly;
            var type =
                aseembly.GetType(
                    "System.Data.Entity.ModelConfiguration.Design.PluralizationServices.EnglishPluralizationService");
            _pluralizer = Activator.CreateInstance(type, true);
            _pluralizationMethod = _pluralizer.GetType().GetMethod("Pluralize");
        }
    }
}

Pallette

Very interesting, this is exactly the kind of thing i was thinking about, using data annotations or fluent api to define indexes in your model aswell. I'm going to see if i can create some tests for this approach soon, nice work. – Sirois

Answer 3

To build on frozen's response, you can hand code it into a migration yourself.

First, go to the Package Manager Console and create a new migration with add-migration, then give it a name. A blank migration will appear. Stick this in:

    public override void Up()
    {
        CreateIndex("TableName", "ColumnName");
    }

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

Note that if you're using a string field it needs to be capped to a length of 450 chars as well.

Saito

This is exactly what I wanted, since I needed to create an index inside a migration. Thanks! – Friedafriedberg

The DropIndex method used here is incorrect. The second parameter is either string[] columns or string name. The Down method will try to drop an index named "ColumnName". You need to specify the index name in the Up or pass in an array of column names in the Down. See msdn.microsoft.com/en-us/library/hh829733(v=vs.103).aspx – Marasco

Sql Server enforces a maximum key size of 900 bytes. msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx. Your 450 'characters' could be different depending on the encoding. – Artificiality

Answer 4

I've also looked into this recently and found no other way, so I settled with creating indexes when seeding the database:

public class MyDBInitializer : DropCreateDatabaseIfModelChanges<MyContext>
{
    private MyContext _Context;

    protected override void Seed(MyContext context)
    {
        base.Seed(context);
        _Context = context;

        // We create database indexes
        CreateIndex("FieldName", typeof(ClassName));

        context.SaveChanges();
    }

    private void CreateIndex(string field, Type table)
    {
        _Context.Database.ExecuteSqlCommand(String.Format("CREATE INDEX IX_{0} ON {1} ({0})", field, table.Name));
    }    
}   

Udometer

I am passing a Type for the table in CreateIndex() to have compile-time safety on the table names. Sadly this isn't so easily done for field names so I haven't implemented it. – Udometer

Thanks - this worked for me. Extended the CreateIndex method a little to support multiple column and unique indexes - see answer below: – Mev

Answer 5

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.

Envious

It does now: EF 6.1: Creating indexes with IndexAttribute – Colene

Answer 6

For anyone using Entity Framework 6.1+, you can do the following with fluent api:

modelBuilder 
    .Entity<Department>() 
    .Property(t => t.Name) 
    .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute()));

Read more in the documentation.

Methuselah

Answer 7

Well i found a solution online and adapted it to fit my needs here it is:

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
public class IndexAttribute : Attribute
{
    public IndexAttribute(string name, bool unique = false)
    {
        this.Name = name;
        this.IsUnique = unique;
    }

    public string Name { get; private set; }

    public bool IsUnique { get; private set; }
}

public class IndexInitializer<T> : IDatabaseInitializer<T> where T : DbContext
{
    private const string CreateIndexQueryTemplate = "CREATE {unique} INDEX {indexName} ON {tableName} ({columnName});";

    public void InitializeDatabase(T context)
    {
        const BindingFlags PublicInstance = BindingFlags.Public | BindingFlags.Instance;
        Dictionary<IndexAttribute, List<string>> indexes = new Dictionary<IndexAttribute, List<string>>();
        string query = string.Empty;

        foreach (var dataSetProperty in typeof(T).GetProperties(PublicInstance).Where(p => p.PropertyType.Name == typeof(DbSet<>).Name))
        {
            var entityType = dataSetProperty.PropertyType.GetGenericArguments().Single();
            TableAttribute[] tableAttributes = (TableAttribute[])entityType.GetCustomAttributes(typeof(TableAttribute), false);

            indexes.Clear();
            string tableName = tableAttributes.Length != 0 ? tableAttributes[0].Name : dataSetProperty.Name;

            foreach (PropertyInfo property in entityType.GetProperties(PublicInstance))
            {
                IndexAttribute[] indexAttributes = (IndexAttribute[])property.GetCustomAttributes(typeof(IndexAttribute), false);
                NotMappedAttribute[] notMappedAttributes = (NotMappedAttribute[])property.GetCustomAttributes(typeof(NotMappedAttribute), false);
                if (indexAttributes.Length > 0 && notMappedAttributes.Length == 0)
                {
                    ColumnAttribute[] columnAttributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), false);

                    foreach (IndexAttribute indexAttribute in indexAttributes)
                    {
                        if (!indexes.ContainsKey(indexAttribute))
                        {
                            indexes.Add(indexAttribute, new List<string>());
                        }

                        if (property.PropertyType.IsValueType || property.PropertyType == typeof(string))
                        {
                            string columnName = columnAttributes.Length != 0 ? columnAttributes[0].Name : property.Name;
                            indexes[indexAttribute].Add(columnName);
                        }
                        else
                        {
                            indexes[indexAttribute].Add(property.PropertyType.Name + "_" + GetKeyName(property.PropertyType));
                        }
                    }
                }
            }

            foreach (IndexAttribute indexAttribute in indexes.Keys)
            {
                query += CreateIndexQueryTemplate.Replace("{indexName}", indexAttribute.Name)
                            .Replace("{tableName}", tableName)
                            .Replace("{columnName}", string.Join(", ", indexes[indexAttribute].ToArray()))
                            .Replace("{unique}", indexAttribute.IsUnique ? "UNIQUE" : string.Empty);
            }
        }

        if (context.Database.CreateIfNotExists())
        {
            context.Database.ExecuteSqlCommand(query);
        }
    }

    private string GetKeyName(Type type)
    {
        PropertyInfo[] propertyInfos = type.GetProperties(BindingFlags.FlattenHierarchy | BindingFlags.Instance | BindingFlags.Public);
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            if (propertyInfo.GetCustomAttribute(typeof(KeyAttribute), true) != null)
                return propertyInfo.Name;
        }
        throw new Exception("No property was found with the attribute Key");
    }
}

Then overload OnModelCreating in your dbcontext

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer(new IndexInitializer<MyContext>());
        base.OnModelCreating(modelBuilder);
    }

Apply the index attribute to your Entity type, with this solution you can have multiple fields in the same index just use the same name and unique.

Entanglement

very interesting solution, come across any way to extend the add-migrations functionality and the fluent api for typeconfigurations, to allow for creation of indexes, when using migrations aswell? – Sirois

I have never used migrations so i have no idea but this should be adaptable to be used in a migration, but you have to check if the index exists and drop it before creating it – Entanglement

I've found the same solution at [link]blogs.southworks.net/dschenkelman/2012/08/18/…, but it isn't usable for me. I use table per hierarchy approach and code go through properties only on base class. – Calendar

@RudolfDvoracek change the following line const BindingFlags PublicInstance = BindingFlags.Public | BindingFlags.Instance; to class "const BindingFlags PublicInstance = BindingFlags.Public | BindingFlags.Instance | BindingFlags.FlattenHierarchy;" this should make it loop the properties in the base class. – Entanglement

@Petoj I've tried your recommendation, but I haven't been successful. Application didn't stepped into dealing with derived type and checked only properties on base type. – Calendar

@RudolfDvoracek im sorry but i don't understand what you are writing and im not 100% sure i understand your problem but changing the PublicInstance should make the code loop the properties on the base class... – Entanglement

@Petoj. I have base class DeviceContact with property Id, which is a primary key. Derrived class SMTPMail have an additional Property named SMTPAddress. Although I changed value of PublicInstance constant, iterating through property goes only through ID property, but not over SMTPAddress, which is already decorated. – Calendar

This process does not work on Table per hierarchy model, although I added BindingFlags.FlattenHierarchy option as @Petoj adviced. I also couldn't find how to get singularized table name. dataSetProperty.Name returns "Cities" instead of "City". I've tried setting modelBuilder.Conventions.Remove<PluralizingTableNameConvention>() in my initializer, but it didn't help. – Calendar

Interesting technique, it doesn't pick up attributes defined on a MetaDataType though. Where was the original article? – Magnify

Watch out!!! this technique works BUT it will prevent your DB from performing an automatic update (in case you change the schema). The solution is to explicitly call IndexInitializer.InitializeDatabase from your Seed method, AND change the last if statement in InitializeDatabase so it will always run (it is redundant since it will only be called from Seed). – Wilbert

Answer 8

Extending Tsuushin's answer above to support multiple columns and unique constraints:

    private void CreateIndex(RBPContext context, string field, string table, bool unique = false)
    {
        context.Database.ExecuteSqlCommand(String.Format("CREATE {0}NONCLUSTERED INDEX IX_{1}_{2} ON {1} ({3})", 
            unique ? "UNIQUE " : "",
            table,
            field.Replace(",","_"),
            field));
    } 

Mev

what would be neat is a way to use a field and table that is not a string, to have compiletime safety, harder said than done i guess, since it has to use dataannotations/fluent api's fieldnames/tablenames. – Sirois

@FRoZeN just change the Type from string to an interface, create an empty interface and inside the method you get the class name of the object and use as table name. – Bowerbird

Answer 9

expanding on Petoj

i modified the CreateIndexQueryTemplate to

private const string CreateIndexQueryTemplate = "IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = '{indexName}') CREATE {unique} INDEX {indexName} ON {tableName} ({columnName});";

and removed the following from OnModelCreating

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

and added the following to Configuration Seeding method

new IndexInitializer<MyContext>().InitializeDatabase(context);

this way the index attributes are run every time you do a update-database.

Goer

Why not change it so it drops it and recreates it that way you can change the fields it includes and it would be updated on each update.. – Entanglement

Answer 10

If you want this feature added to EF then you can vote for it here http://entityframework.codeplex.com/workitem/57

Jeter

Answer 11

jwsadler's extension of Data Annotations was a nice fit for us. We use Annotations to influence the treatment of a class or property and Fluent API for global changes.

Our annotations cover indexes (unique and not unique) plus default values of getdate() and (1). The code sample shows how we applied it to our situation. All of our classes inherit from one base class. This implementation makes a lot of assumptions because we have a pretty simple model. We're using Entity Framework 6.0.1. Lots of comments have been included.

using System;
using System.Linq;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace YourNameSpace
{
    public enum SqlOption
    {
        Active = 1,
        GetDate = 2,
        Index = 3,
        Unique = 4,
    }

    [AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
    public class SqlAttribute : Attribute
    {
        public SqlAttribute(SqlOption selectedOption = SqlOption.Index)
        {
            this.Option = selectedOption;
        }

        public SqlOption Option {get; set;}
    }

    // See enum above, usage examples: [Sql(SqlOption.Unique)] [Sql(SqlOption.Index)] [Sql(SqlOption.GetDate)]
    public class SqlInitializer<T> : IDatabaseInitializer<T> where T : DbContext
    {
        // Create templates for the DDL we want generate
        const string INDEX_TEMPLATE = "CREATE NONCLUSTERED INDEX IX_{columnName} ON [dbo].[{tableName}] ([{columnName}]);";
        const string UNIQUE_TEMPLATE = "CREATE UNIQUE NONCLUSTERED INDEX UQ_{columnName} ON [dbo].[{tableName}] ([{columnName}]);";
        const string GETDATE_TEMPLATE = "ALTER TABLE [dbo].[{tableName}] ADD DEFAULT (getdate()) FOR [{columnName}];";
        const string ACTIVE_TEMPLATE = "ALTER TABLE [dbo].[{tableName}] ADD DEFAULT (1) FOR [{columnName}];";

        // Called by Database.SetInitializer(new IndexInitializer< MyDBContext>()); in MyDBContext.cs
        public void InitializeDatabase(T context)
        {
            // To be used for the SQL DDL that I generate
            string sql = string.Empty;

            // All of my classes are derived from my base class, Entity
            var baseClass = typeof(Entity);

            // Get a list of classes in my model derived from my base class
            var modelClasses = AppDomain.CurrentDomain.GetAssemblies().ToList().
                SelectMany(s => s.GetTypes()).Where(baseClass.IsAssignableFrom);

            // For debugging only - examine the SQL DDL that Entity Framework is generating
            // Manipulating this is discouraged.
            var generatedDDSQL = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();

            // Define which Annotation Attribute we care about (this class!)
            var annotationAttribute = typeof(SqlAttribute);

            // Generate a list of concrete classes in my model derived from
            // Entity class since we follow Table Per Concrete Class (TPC).
            var concreteClasses = from modelClass in modelClasses
                                  where !modelClass.IsAbstract
                                  select modelClass;

            // Iterate through my model's concrete classes (will be mapped to tables)
            foreach (var concreteClass in concreteClasses)
            {
                // Calculate the table name - could get the table name from list of DbContext's properties
                // to be more correct (but this is sufficient in my case)
                var tableName = concreteClass.Name + "s";

                // Get concrete class's properties that have this annotation
                var propertiesWithAnnotations = concreteClass.GetProperties().Where(prop => Attribute.IsDefined(prop, annotationAttribute));

                foreach (var annotatedProperty in propertiesWithAnnotations)
                {
                    var columnName = annotatedProperty.Name;
                    var annotationProperties = annotatedProperty.GetCustomAttributes(annotationAttribute, true).ToList();

                    foreach (SqlAttribute annotationProperty in annotationProperties)
                    {
                        // Generate the appropriate SQL DLL based on the attribute selected
                        switch (annotationProperty.Option)
                        {
                            case SqlOption.Active: // Default value of true plus an index (for my case)
                                sql += ACTIVE_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                                sql += INDEX_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                                break;
                            case SqlOption.GetDate: // GetDate plus an index (for my case)
                                sql += GETDATE_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                                sql += INDEX_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                                break;
                            case SqlOption.Index: // Default for empty annotations for example [Sql()]
                                sql += INDEX_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                                break;
                            case SqlOption.Unique:
                                sql += UNIQUE_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                                break;
                        } // switch
                    } // foreach annotationProperty
                } // foreach annotatedProperty
            } // foreach concreteClass

            // Would have been better not to go through all the work of generating the SQL
            // if we weren't going to use it, but putting it here makes it easier to follow.
            if (context.Database.CreateIfNotExists())
                context.Database.ExecuteSqlCommand(sql);

        } // InitializeDatabase
    } // SqlInitializer
} // Namespace

Here's our context:

using System;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace YourNameSpace
{
    public class MyDBContext : DbContext
    {
       protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Only including my concrete classes here as we're following Table Per Concrete Class (TPC)
            public virtual DbSet<Attendance> Attendances { get; set; }
            public virtual DbSet<Course> Courses { get; set; }
            public virtual DbSet<Location> Locations { get; set; }
            public virtual DbSet<PaymentMethod> PaymentMethods { get; set; }
            public virtual DbSet<Purchase> Purchases { get; set; }
            public virtual DbSet<Student> Students { get; set; }
            public virtual DbSet<Teacher> Teachers { get; set; }

            // Process the SQL Annotations
            Database.SetInitializer(new SqlInitializer<MyDBContext>());
            base.OnModelCreating(modelBuilder);

            // Change all datetime columns to datetime2
            modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));

            // Turn off cascading deletes
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        }
    }
}

Trevelyan

Answer 12

To build further on all these great responses, we added the following code to enable the Index attribute to be picked up from an associated metadata type. For the full details please see my blog post, but in summary here are the details.

Metadata types are used like this:

    [MetadataType(typeof(UserAccountAnnotations))]
    public partial class UserAccount : IDomainEntity
        {
        [Key]
        public int Id { get; set; } // Unique ID
        sealed class UserAccountAnnotations
            {
            [Index("IX_UserName", unique: true)]
            public string UserName { get; set; }
            }
       }

In this example the metadata type is a nested class, but it doesn't have to be, it can be any type. Property matching is done by name only, so the metadata type just has to have a property of the same name, and any data annotations applied to that should then be applied to the associated entity class. This didn't work in the original solution because it doesn't check for the associated metadata type. We plumbed in the following helper method:

/// <summary>
///   Gets the index attributes on the specified property and the same property on any associated metadata type.
/// </summary>
/// <param name="property">The property.</param>
/// <returns>IEnumerable{IndexAttribute}.</returns>
IEnumerable<IndexAttribute> GetIndexAttributes(PropertyInfo property)
    {
    Type entityType = property.DeclaringType;
    var indexAttributes = (IndexAttribute[])property.GetCustomAttributes(typeof(IndexAttribute), false);
    var metadataAttribute =
        entityType.GetCustomAttribute(typeof(MetadataTypeAttribute)) as MetadataTypeAttribute;
    if (metadataAttribute == null)
        return indexAttributes; // No metadata type

    Type associatedMetadataType = metadataAttribute.MetadataClassType;
    PropertyInfo associatedProperty = associatedMetadataType.GetProperty(property.Name);
    if (associatedProperty == null)
        return indexAttributes; // No metadata on the property

    var associatedIndexAttributes =
        (IndexAttribute[])associatedProperty.GetCustomAttributes(typeof(IndexAttribute), false);
    return indexAttributes.Union(associatedIndexAttributes);
    }

Magnify

P.S. we're using EF 5.0 so the above code is untested on EF 4.2 – Magnify

Answer 13

For EF7 you can use the hasIndex() method. We can set clustered and non-clustered index as well. By default primary key will be clustered . We can change that behavior too.

supplierItemEntity.HasKey(supplierItem => supplierItem.SupplierItemId).ForSqlServerIsClustered(false);

supplierItemEntity.HasIndex(s => new { s.ItemId }).ForSqlServerIsClustered(true);

Claritaclarity

Answer 14

I discovered a problem with the answer @highace gave - the down migration uses the wrong override for DropIndex. Here is what I did:

1. To comply with Sql Server's limitation on index columns (900 bytes) I reduced the size of a couple of fields in my model
2. I added the migration using Add-Migration "Add Unique Indexes"
3. I manually added the CreateIndex and DropIndex methods to the migration. I used the override that takes the index name for the single column index. I used the override that takes an array of column names where the index spans more than one column

And here is the code with examples of both overrides of each method:

public partial class AddUniqueIndexes : DbMigration
{
    public override void Up()
    {
        //Sql Server limits indexes to 900 bytes, 
        //so we need to ensure cumulative field sizes do not exceed this 
        //otherwise inserts and updates could be prevented
        //http://www.sqlteam.com/article/included-columns-sql-server-2005
        AlterColumn("dbo.Answers",
            "Text",
            c => c.String(nullable: false, maxLength: 400));
        AlterColumn("dbo.ConstructionTypes",
            "Name",
            c => c.String(nullable: false, maxLength: 300));

        //[IX_Text] is the name that Entity Framework would use by default
        // even if it wasn't specified here
        CreateIndex("dbo.Answers",
            "Text",
            unique: true,
            name: "IX_Text");

        //Default name is [IX_Name_OrganisationID]
        CreateIndex("dbo.ConstructionTypes",
            new string[] { "Name", "OrganisationID" },
            unique: true);
    }

    public override void Down()
    {
        //Drop Indexes before altering fields 
        //(otherwise it will fail because of dependencies)

        //Example of dropping an index based on its name
        DropIndex("dbo.Answers", "IX_Text");

        //Example of dropping an index based on the columns it targets
        DropIndex("dbo.ConstructionTypes", 
            new string[] { "Name", "OrganisationID" }); 

        AlterColumn("dbo.ConstructionTypes",
            "Name",
            c => c.String(nullable: false));

        AlterColumn("dbo.Answers",
            "Text",
            c => c.String(nullable: false, maxLength: 500));
}

Marasco

Answer 15

You can specify index in ModelBuilder

modelBuilder
            .Entity<UserSalary>(builder =>
            {
                builder.HasNoKey();
                builder.HasIndex("UserId").IsUnique(false);
                builder.ToTable("UserSalary");
            });

Golanka

What's new?. Also, why HasNoKey? You make it seem necessary for creating indexes. – Chook

Sorry for that, You can remove HasNoKey in the example above. But that is how you declare indexes to your columns – Golanka