How to create a Clustered Index with Entity Framework Core

Question

From EF6.1, we have a way of specifying a clustered index on a property

public class Person 
{
  [Index(IsClustered = true, IsUnique = true)]
  public long UserName { get; set; }
}

But this Index attribute does not seem to be in EF Core right now? In EF Core how do you achieve this?

Answer 1

From the current EF Core documentation - Indexes section:

Data Annotations

Indexes can not be created using data annotations.

But for sure you can specify that via Fluent API (note the extension methods having ForSqlServer prefix which seem to denote SqlServer specific features):

modelBuilder.Entity<Person>()
    .HasIndex(e => e.UserName)
    .IsUnique()
    .ForSqlServerIsClustered();

Update: for EF Core 3.0+ the method is called just IsClustered:

modelBuilder.Entity<Person>()
    .HasIndex(e => e.UserName)
    .IsUnique()
    .IsClustered();

Update: Starting with EF Core 5.0, there is Index data annotation now mentioned in the Indexes documentation link, but it can't be used to specify database specific attributes like clustered (SqlServer specific), so the original answer still applies.

Answer 2

For EF Core 3.0+ You can now use IsClustered:

modelBuilder.Entity<Person>()
.HasIndex(e => e.UserName)
.IsUnique()
.IsClustered();

.ForSqlServerIsClustered() is now marked as obsolete.

Also be aware that if you have a Primary Key on the table you may also need to explicitly remove the clustering on it before you add the clustering on your Username:

modelBuilder.Entity<Person>()
.HasKey(e => e.PersonId)
.IsClustered(false);

modelBuilder.Entity<Person>()
.HasIndex(e => e.UserName)
.IsUnique()
.IsClustered();

Answer 3

In the absence of built-in support, you can use a custom attribute of your own to annotate model properties and apply in OnModelCreating():

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var prop in entity.GetProperties())
        {
            var attr = prop.PropertyInfo.GetCustomAttribute<IndexAttribute>();
            if (attr != null)
            {
                var index = entity.AddIndex(prop);
                index.IsUnique = attr.IsUnique;
                index.SqlServer().IsClustered = attr.IsClustered;
            }
        }
    }
}

With a simple marker attribute class:

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
public class IndexAttribute : Attribute
{
    public bool IsUnique { get; set; } 
    public bool IsClustered { get; set; } 
}

Then in your model class, just add the attribute to create a secondary index:

public class User
{
    public int UserId { get; set; }
    [Index(IsUnique = true, IsClustered = true)]
    public string Nickname { get; set; }
}

Answer 4

Or this works too, say if you wanted to cluster by age...

            modelBuilder
            .Entity<Person>()
            .Property(t => t.Age)
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute() { IsClustered = true}));

Answer 5

I liked the answer of @StephenD. But I would do some improvements. In your answer you need to add '?' on the PropertyInfo. In case there are no custom attributes that will result in null.

var attr = prop.PropertyInfo?.GetCustomAttribute<IndexAttribute>();

I searched the solution for more than one 'Index' attributes on the property. For each entity, group all indexes by names and get all the properties that corresponds to the appropriate index (in my case System.ComponentModel.DataAnnotations.Schema.IndexAttribute). After that add all indexes to the entity.

public class User
{
    [Index("IX_UserIdAndNickname", 1, IsUnique = false)]
    public int UserId { get; set; }

    [Index("IX_UserIdAndNickname", 2, IsUnique = false)]
    [Index(IsUnique = true)]
    public string Nickname { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        var indexeNameToAttribute = new Dictionary<string, System.ComponentModel.DataAnnotations.Schema.IndexAttribute>();
        var indexNameToProperties = new Dictionary<string, List<IMutableProperty>>();

        foreach (var prop in entity.GetProperties())
        {
            var attributes = prop.PropertyInfo?.GetCustomAttributes<System.ComponentModel.DataAnnotations.Schema.IndexAttribute>();
            if (attributes != null && attributes.Count() > 0)
            {
                foreach (var attr in attributes)
                {
                    // Index can be without name, so we add our convention
                    var indexName = attr.Name ?? $"IX_{prop.Name}";
                    if(!indexeNameToAttribute.ContainsKey(indexName))
                    {
                       indexeNameToAttribute.Add(indexName, attr);
                    }

                    if (!indexNameToProperties.ContainsKey(indexName))
                    {
                       indexNameToProperties.Add(indexName, new List<IMutableProperty>());
                    }

                    indexNameToProperties[indexName].Add(prop);
                 }
              }
           }

           foreach (var prop in indexNameToProperties)
           {
               var addedIndex = entity.AddIndex(prop.Value, prop.Key);
               var indexAttribute = indexeNameToAttribute[prop.Key];
               addedIndex.IsUnique = indexAttribute.IsUnique;
           }
        }
    }
}