Entity Framework Code First Fluent Api: Adding Ind

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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