efcore: Cannot create database when using MemoryOptimizedTables

I have a entity configured to use MemoryOptimizedTabled as follow:

            builder.Entity<ApplicationUser>()
                   .ToTable("Users")
                   .ForSqlServerIsMemoryOptimized(true);

When the application starts i run the following command to apply migrations to database:

app.ApplicationServices.GetRequiredService<ApplicationDbContext>()
                                      .Database.Migrate();

If database already exists on server without any table or data everything works fine, but if not exists the exception above is thown.

It seems that when I create the database using Sql Manager, the AUTO_CLOSE option is false, but when the Entity Framework creates this option it is true by default

System.Data.SqlClient.SqlException occurred
  HResult=0x80131904
  Message=The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.
  Source=Core .Net SqlClient Data Provider
  StackTrace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at WebApplication4.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory) in c:\users\andrepassos\documents\visual studio 2017\Projects\WebApplication4\Startup.cs:line 87

Further technical details

EF Core version: 1.1.1 Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 x64 Pro IDE: Visual Studio 2017 Database Engine: Sql Server 2016 Developer, Sql Server 2016 Express SP1

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 18 (14 by maintainers)

Most upvoted comments

If a special case (like memory-optimized tables) requires a specific setting, then it seems reasonable to edit the migration to do that. Do people agree?

@ajcvickers I agree if you mean EF Core should not be in the business of setting this option to any particular value. Although we do the database creation, this is one of the options that can be set afterwards:

ALTER DATABASE Hey SET AUTO_CLOSE OFF

divega on Apr 12, 2017

@andrelmp Express likely defaults to AUTO_CLOSE ON same as LocaldDB. But Developer edition should not. According to the documentation it is because LocalDB and Express are “optimized for the desktop”, and they let you copy and backup database files when they are not in use.

since localdb 2016 sp1 now supports mem-optimized and many other Ent edition features

I wonder if customers are expected to set the option AUTO_CLOSE OFF explicitly in order to use those features.

divega on Apr 12, 2017