Unique Indexes convention in EF6

Question

How to create custom index and key conventions for different type of indexes. I need different naming for following key or index types:

• PK_TableName Primary keys
• FK_SourceTable_Column_TargetTable for foreign keys
• IX_TableName_Column1_Column2 Non-unique indexes
• UX_TableName_Column1_Column2 Unique indexes

By defaults, Entity Framework uses following namings:

• PK_schemaname.TableName for primary keys
• FK_schemaname.SourceTable_schemaname.TargetTable_Column1 for foreign keys
• IX_Column1 for non-unique indexes
• ColumnName for unique indexes

I've found out that I can implement IStoreModelConvention<T>, but I haven't found particular type to use as type parameter. Moreover, there're can be Custom Code-First Conventions, but my research is ended with no results. How I can get mentioned naming rules when I use Entity Framework Code First? It can be anything: package, sample, or just direction for following researches.

Answer 1

Mission impossible for PK and FK. The problems is that there is no special EdmModel property/attribute/annotation for naming the store constraint - in the model they are basically represented as list of columns (properties) and the naming convention is hardcoded inside the migration builder classes. Please note that some examples mentioned in the comments are showing how to rename the FK columns (properties), not the FK constraint itself.

Luckily for indexes, although not simple, but it's possible, thanks to the IndexAttribute and IndexAnnotation. This is because the annotation (with attribute) is associated with column (entity property), and then consolidated by an internal class called ConsolidatedIndex.

So in order to achieve the goal, you have to create IStoreModelConvention<EntityType>, prepare a consolidated index info from properties similar to how ConsolidatedIndex class does it, determine the new name based on your rules for the unnamed indexes or indexes with default name generated for FK constrains by the ForeignKeyIndexConvention, and update the corresponding IndexAnnotation of the properties.

With that being said, here is the code for applying your index name convention:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.Annotations;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;

public class IndexNameConvention : IStoreModelConvention<EntityType>
{
    public void Apply(EntityType item, DbModel model)
    {
        // Build index info, consolidating indexes with the same name
        var indexInfo = new List<IndexInfo>();
        foreach (var p in item.Properties)
        {
            foreach (var mp in p.MetadataProperties)
            {
                var a = mp.Value as IndexAnnotation;
                if (a == null) continue;
                foreach (var index in a.Indexes)
                {
                    var info = index.Name != null ? indexInfo.FirstOrDefault(e => e.Name == index.Name) : null;
                    if (info == null)
                    {
                        info = new IndexInfo { Name = index.Name };
                        indexInfo.Add(info);
                    }
                    else
                    {
                        var other = info.Entries[0].Index;
                        if (index.IsUnique != other.IsUnique || index.IsClustered != other.IsClustered)
                            throw new Exception("Invalid index configuration.");
                    }
                    info.Entries.Add(new IndexEntry { Column = p, Annotation = mp, Index = index });
                }
            }
        }
        if (indexInfo.Count == 0) return;
        // Generate new name where needed
        var entitySet = model.StoreModel.Container.EntitySets.First(es => es.ElementType == item);
        foreach (var info in indexInfo)
        {
            var columns = info.Entries.OrderBy(e => e.Index.Order).Select(e => e.Column.Name);
            if (info.Name == null || info.Name == IndexOperation.BuildDefaultName(columns))
            {
                bool unique = info.Entries[0].Index.IsUnique;
                var name = string.Format("{0}_{1}_{2}", unique ? "UX" : "IX", entitySet.Table, string.Join("_", columns));
                if (name.Length > 128) name = name.Substring(0, 128);
                if (info.Name == name) continue;
                foreach (var entry in info.Entries)
                {
                    var index = new IndexAttribute(name);
                    if (entry.Index.Order >= 0)
                        index.Order = entry.Index.Order;
                    if (entry.Index.IsUniqueConfigured)
                        index.IsUnique = entry.Index.IsUnique;
                    if (entry.Index.IsClusteredConfigured)
                        index.IsClustered = entry.Index.IsClustered;
                    entry.Index = index;
                    entry.Modified = true;
                }
            }
        }
        // Apply the changes
        foreach (var g in indexInfo.SelectMany(e => e.Entries).GroupBy(e => e.Annotation))
        {
            if (g.Any(e => e.Modified))
                g.Key.Value = new IndexAnnotation(g.Select(e => e.Index));
        }
    }

    class IndexInfo
    {
        public string Name;
        public List<IndexEntry> Entries = new List<IndexEntry>();
    }

    class IndexEntry
    {
        public EdmProperty Column;
        public MetadataProperty Annotation;
        public IndexAttribute Index;
        public bool Modified;
    }
}

All you need is to add it to the DbModelBuilder.Conventions in your OnModelCreating:

modelBuilder.Conventions.Add<IndexNameConvention>();