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.

score 15 · Accepted Answer · answered Dec 06 '16 at 20:30

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"
        }
    }
}

In EF Core 3+ it just `.IsClustered(false)` – d_f Jan 04 '21 at 12:12 — d_f, Jan 04 '21 at 12:12

score 5 · Answer 2 · answered Sep 29 '21 at 06:44

5

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

answered Sep 29 '21 at 06:44

ddegasperi

662
9
12

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**. – mamen Mar 20 '23 at 13:34
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. – ddegasperi Mar 24 '23 at 13:23
That is true, but I think an Index is always non-clustered unless it is specified otherwise by declaring `.IsClustered(true)` or `.ForSqlServerIsClustered(true)`. – mamen Mar 27 '23 at 07:11
1

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)` – ddegasperi Mar 27 '23 at 07:55

Create a non-clustered index in Entity Framework Core

2 Answers2

Linked