Create a non-clustered index in Entity Framework Core

Question

Using Entity Framework Core, I want to have a Guid PK, without suffering page fragmentation in the database.

I have seen this post and this. Although it was possible in EF6, the way it's done seems to have changed.

Is it possible to create a non-clustered primary key in Entity Framework Core and have an additional index?

Q&A Answer below.

Eared

Answer 1

It is possible using EntityFrameworkCore v1.0.1 or greater.

The following code gets the desired result:

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace TestApplication.Models
{

    /// <summary>
    /// The context class. Make your migrations from this point.
    /// </summary>
    public partial class TestApplicationContext : DbContext
    {
        public virtual DbSet<Company> Companies { get; set; }

        public TestApplicationContext(DbContextOptions<TestApplicationContext> options) : base(options)
        {

        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // standard stuff here...
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Company>(entity =>
            {
                entity.Property<Guid>("CompanyId")
                        .ValueGeneratedOnAdd();

                entity.Property<int>("CompanyIndex")
                        .UseSqlServerIdentityColumn()
                        .ValueGeneratedOnAdd();

                entity.Property(e => e.CompanyName)
                    .IsRequired()
                    .HasColumnType("varchar(100)");

                // ... Add props here.

                entity.HasKey(e => e.CompanyId)
                    .ForSqlServerIsClustered(false)
                    .HasName("PK_Company");
                entity.HasIndex(e => e.CompanyIndex)
                    .ForSqlServerIsClustered(true)
                    .HasName("IX_Company");
            });
        }
    }

        /// <summary>
        /// The model - put here for brevity.
        /// </summary>
        public partial class Company
        {
            public Company()
            {
            }

            public Guid CompanyId { get; set; }
            public int CompanyIndex { get; set; }

            public string CompanyName { get; set; }
            // more props here.
        }

    }

Project.json

{
    "version": "1.0.0-*",

    "dependencies": {
        "Microsoft.EntityFrameworkCore.Design": "1.0.1",
        "Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
        "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.1",
        "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview3-final",
        "NETStandard.Library": "1.6.0"
    },
    "tools": {
        "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview3-final",
        "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
    },
    "frameworks": {
        "netstandard1.6": {
            "imports": "dnxcore50"
        }
    }
}

Eared

In EF Core 3+ it just .IsClustered(false) – Guava

Answer 2

For EF Core 5 or greater

  // ...
  modelBuilder.Entity<MyEntity>(entity =>
  {
     // ...
     entity.HasIndex(e => e.MyIndexColumn)
       .IsClustered(false);
  }
  // ...

=> The obsolete method ForSqlServerIsClustered was removed in this version

For EF Core 3.x

  // ...
  modelBuilder.Entity<MyEntity>(entity =>
  {
     // ...
     entity.HasIndex(e => e.MyIndexColumn)
       .IsClustered(true); // new method
     // OR
     entity.HasIndex(e => e.MyIndexColumn)
       .ForSqlServerIsClustered(false); // obsolete method
  }
  // ...

=> Both methods IsClustered and ForSqlServerIsClustered can be used, but later one is already marked as obsolete in favor of the first one.

For EF Core 1.x- EF Core 2.x

  // ...
  modelBuilder.Entity<MyEntity>(entity =>
  {
     // ...
     entity.HasIndex(e => e.MyIndexColumn)
       .ForSqlServerIsClustered(false);
  }
  // ...

Morality

Isn't .IsClustered(false) or .ForSqlServerIsClustered(false) only needed when creating a key? I don't think you need it when simply creating an index. – Cynical

It depends with which sorting you want to store the data on the physical storage, which does not always have to be the primary key. The main reason for a clustered index could be an optimized performance when reading the data. According to SQL Server documentation only one clustered index per table is allowed. – Morality

That is true, but I think an Index is always non-clustered unless it is specified otherwise by declaring .IsClustered(true) or .ForSqlServerIsClustered(true). – Cynical

Exactly, according to the documentation an index is not clustered by default. However, the methods can be used for Index and the Primary Key. The primary key is clustered by default, and must be deactivated accordingly to enable a clustered index .HasKey(e => e.MyPk).IsClustered(false) or .HasKey(e => e.MyPk).ForSqlServerIsClustered(false) – Morality