Create a Table in different File-group with EF core code-first

Question

I want to create a table in the "Secondary" file group which has been created previously in the SQL Server 2016. I want to create this table with EF core code-first.

Answer 1

Finally, I found the solution and I want to share it.

1. Before creating a Table or adding your Tables’ DbSet in your ApplicationDbContext, create an empty migration with this code in PMC.

PM> add-migration CreateSecondaryFileGroup

2. Write the SQL query for creating your filegroup and its Physical file in the “Up” method.

protected override void Up(MigrationBuilder migrationBuilder)
{
     //create FILEGROUP 
     migrationBuilder.Sql(
         " EXEC('ALTER DATABASE [YourDatabaseName] ADD FILEGROUP [Secondary]')",
         suppressTransaction: true);

     //Create Physical file
     migrationBuilder.Sql(
         "ALTER DATABASE [YourDatabaseName] " +
                "ADD FILE " +
                "( " +
                    "NAME = 'Secondary_Data', " +
                    "FILENAME = '[yourFileDirectory]' " +
                    //for example:
                    //"FILENAME = 'D:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\DATA\\Abby_Data2.mdf' " +
                ") " +
                "TO FILEGROUP Secondary",
                suppressTransaction:true
            );
}

3. Then in PMC run this code to update your Database and create the filegroup.

PM> update-database

4. Now add your tables’ DbSet in the ApplicationDbContext file, (my table name is Category)

public DbSet<Category> Category { get; set; }

5. Now create a new migration file in order to create the table in your database in your specific filegroup (for me the filegroup name is Secondary) with this code in PMC

PM> add-migration CreateCategoryTableInSecondaryFileGroup

6. Now in the “Up” method of migration file you should add SQL query to “create cluster Index of the table again” with SQL method of migrationBuilder.

One solution to create or move a table to another filegroup is to create Clustered Index in the specific filegroup. The clustered index is the primary key of the “Category” table whose name is “PK_Category”

for more information

protected override void Up(MigrationBuilder migrationBuilder)
{
      migrationBuilder.CreateTable(
      name: "Category",
      columns: table => new
      {
           Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
           Name = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
           DisplayOrder = table.Column<int>(type: "int", nullable: false)
       },
       constraints: table =>
       {
             table.PrimaryKey("PK_Category", x => x.Id);
       });
       ///// this below code is the key point
       ///// you should write the below code

       migrationBuilder.Sql(
          "CREATE UNIQUE CLUSTERED INDEX PK_Category ON Category (Id)"+  
          " WITH(DROP_EXISTING = ON) "+
                " ON Secondary"
               , suppressTransaction:true);
}

7. Finally, run this code to update the database in PMC.

PM> update-database