how to use code first to create columnstore index in EF Core

user_13580484 0
Viewed 411

We want to start a new project and we decided to use Columnstore index along with clustered Index in some of the Tables from beginning, how can we do this with Code First EF Core 3.1?

columnstore ef-core-3.1 ef-code-first
1 Answers

  • you should change primary key to non cluster index:

    entity.HasKey(e => e.Id).IsClustered(false);

  • then add a manual migration to migrations folder I add a file with name 30000000000001_AddMyColumnStoreIndex.cs and add the following code in file

using DDFHandler;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using System.Diagnostics.CodeAnalysis;

namespace MyNameSpace
{
    [DbContext(typeof(MyApplicationContext))]
    [Migration("30000000000001_AddMyColumnStoreIndex")]
    public class V1_AddMyColumnStoreIndex : Migration
    {
        protected override void Up([NotNullAttribute] MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("CREATE CLUSTERED COLUMNSTORE INDEX [cci] ON [sch].[TableName]");
        }
        protected override void Down(MigrationBuilder migrationBuilder)
        {
        }
    }
}

be careful that you should replace [sch].[TableName] with your table name and schema

user_39403150
Related