Migration existing disk-based table to memory optimized table

Question

We are developing a web application using ASP.NET MVC Core and using EntityFramework Core for data acess. .net core version is 2.2. Now, We are trying to use MemoryOptimizedTables in SQL server 2016 for our existing tables.

I added following line in OnModelCreating method modelBuilder.Entity<MailLog>().ForSqlServerIsMemoryOptimized(); then I executed Add-Migration it resulted with below migration file.

public partial class DataModel_v0711 : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropPrimaryKey(
                name: "PK_Core_MailLogs",
                table: "Core_MailLogs");

            migrationBuilder.AlterDatabase()
                .Annotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterTable(
                name: "Core_MailLogs")
                .Annotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AddPrimaryKey(
                name: "PK_Core_MailLogs",
                table: "Core_MailLogs",
                column: "Id")
                .Annotation("SqlServer:Clustered", false);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropPrimaryKey(
                name: "PK_Core_MailLogs",
                table: "Core_MailLogs");

            migrationBuilder.AlterDatabase()
                .OldAnnotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterTable(
                name: "Core_MailLogs")
                .OldAnnotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AddPrimaryKey(
                name: "PK_Core_MailLogs",
                table: "Core_MailLogs",
                column: "Id");
        }
    }

And I executed Update-Database Command. I tried with empty database. I try to take migration script with following command.

dotnet ef migrations script  --idempotent -o c:\test\migrate2_1.sql --project EntityFrameworkCore.csproj --startup-project Web.Mvc.csproj

Each time I got the following error.

System.InvalidOperationException: To set memory-optimized on a table on or off the table needs to be dropped and recreated.
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateScript(String fromMigration, String toMigration, Boolean idempotent)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.ScriptMigration(String fromMigration, String toMigration, Boolean idempotent, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScriptMigration.c__DisplayClass0_1.b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.c__DisplayClass3_0`1.b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
To set memory-optimized on a table on or off the table needs to be dropped and recreated.

Thanks.

Answer 1

Looks like Entity framework is not smart enough to generate such script to move data. Please try:
1. Create brand new empty database using EF.
2. Use Visual Studio Schema compare or other tool like RedGate to generate change script. You can also create one by hand.
3. Manually update the database (or use manual migrations)

Answer 2

Check out my solution on

https://www.red-gate.com/simple-talk/sql/t-sql-programming/converting-database-memory-oltp/

The solution is free! Based on your disk based database, InMemory database will be created. There is no need for manual update.