Efcore: Cannot create database when using MemoryOptimizedTables

[andrelmp]

I have a entity configured to use MemoryOptimizedTabled as follow:
```c#
builder.Entity()
.ToTable("Users")
.ForSqlServerIsMemoryOptimized(true);

When the application starts i run the following command to apply migrations to database:
```c#
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

[andrelmp]

According to those articles Link 1 and Link 2 it's a bad practice use AUTO_CLOSE ON.
Maybe set AUTO_CLOSE OFF by default will be a good choice

[ajcvickers]

@divega Can you do some investigation into the AUTO_CLOSE question. Maybe we should be using a differentr default in Migrations.

[divega]

@ajcvickers @bricelam I am missing context here. What are we using AUTO_CLOSE for?

[bricelam]

@AndriySvyryd ?

[ajcvickers]

@divega Maybe the statement, "but when the Entity Framework creates this option it is true by default" is in error or only happens sometimes?

[ErikEJ]

Localdb defaults to auto-close, wonder if that is the real issue (dev/std edition of course have autoclose off)

[ajcvickers]

@ErikEJ That makes sense. In that case, I don't think Migrations should specify anything that would override the default. 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?

[andrelmp]

@ajcvickers i think this is the best choice

[ErikEJ]

+1 - this happens since localdb 2016 sp1 now supports mem-optimized and many other Ent edition festures

[AndriySvyryd]

@andrelmp So are you using localdb?

[divega]

Localdb defaults to auto-close,

Yep, I just found about that. If you do this on LocaldDB:

CREATE DATABASE Hey;
SELECT name, is_auto_close_on FROM master.sys.databases 

The latter returns 'Hey', 1.

But it is not the default in other editions.

[andrelmp]

@ajcvickers no, is a instance db on Express and Dev editions

[divega]

@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]

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

[andrelmp]

@divega it's true, i tested on enterprise edition and AUTO_CLOSE is OFF by default

[divega]

I did a quick search in the code, and I don't think we are setting the option explicitly. So I think we can close this issue unless I have misunderstood something.

FWIW, I also found that at https://github.com/aspnet/EntityFramework/blob/dev/src/EFCore.SqlServer/Storage/Internal/SqlServerDatabaseCreator.cs#L193 we have retry logic for transient failures that commonly occur while "auto-closing".

[ajcvickers]

Closing as I think there is agreement that EF shouldn't do anything special here.

[divega]

FWIW, this seems to be fixed in SQL Server 2017, however creation of LocalDB databases with SQL Server 2017 is now affected by a different issue: https://github.com/aspnet/EntityFrameworkCore/issues/10288.