Efcore: Throw a better exception message when attempting to use LocalDB with memory-optimized tables

[SetTrend]

Initializing a SQL Server Express LocalDB database using Entity Framework Core 3.1 Database.EnsureCreated() throws internal SQL Server exceptions.

Here's the DbContext class code

```c#
using Microsoft.EntityFrameworkCore;

using Repository.Entities;
using Repository.Entities.Lookups;

namespace EF_DB
{
public class Context : DbContext
{
private readonly string _connectionString;

public DbSet<Gender> Genders { get; set; }
public DbSet<User> Users { get; set; }



public Context(string connectionString) : base()
{
  _connectionString = connectionString;

  Database.EnsureDeleted();   // ------ this throws in case #1 ------
  Database.EnsureCreated();   // ------ this throws in case #2 ------

  Database.ExecuteSqlRaw("ALTER TABLE Users ADD CONSTRAINT DF_CreatedAt DEFAULT GETDATE() FOR CreatedAt");
}



protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer(_connectionString);

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<Gender>().IsMemoryOptimized().HasData(new[] { new Gender(1, "Herr"), new Gender(2, "Frau"), new Gender(3, "Divers") });
}

}
}

</details>

### Here's the test code
<details>

```c#
using System.Diagnostics;
using System.IO;
using System.Reflection;

using EF_DB;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using Repository.Entities;

namespace EF_Tests
{
  [TestClass]
  public class CreateDbTests
  {
    private static readonly string _testDbLocation = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "EF-DB.mdf");



    [TestMethod]
    public void CreateDb()
    {
      using (Context db = new Context(@"Server=(LocalDB)\MSSQLLocalDB;Integrated Security=true;Database=EF-DB;AttachDbFileName=" + _testDbLocation))
      {
        foreach (User user in db.Users) Debug.Print(user.FullName);
      }
    }
  }
}

Case 1

When I first called above code by running the test method, I got the following error message after executing Database.EnsureDeleted():

Unable to call into the C compiler. GetLastError = 2.

(Being a Microsoft.Data.SqlClient.SqlError exception.)

Following this analysis I granted myself full access to the C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Xtp directory.

But to no avail. Re-running my test resulted in:

Case 2

After granting myself full access rights to the C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Xtp, the exception message changed to:

Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.

(Both being Microsoft.Data.SqlClient.SqlError exceptions, listed in the $exceptions.Errors collection)

The exception was and still is raised when calling Database.EnsureCreated() in above code.

Code to reproduce

For you to be able to reproduce, here's the source Solution:

EFCore-ForeignDataAnnotations.zip

[bricelam]

LocalDB might be in a bad state. You could try resetting it. ⚠ Warning, this will delete databases.

SqlLocalDB stop
SqlLocalDB delete
DEL "%USERPROFILE%\*.ldf" "%USERPROFILE%\*.mdf"
SqlLocalDB start

[bricelam]

Also, what version of LocalDB are you using? If it's newer than 2016, You might be hitting this issue.

[SetTrend]

Thanks for trying to help.

After deleting and restarting LocalDB, the error is still there, I'm afraid.

BTW: There was a folder called {database name}_MOD in my %USERPROFILE% folder which I had to delete manually after deleting LocalDB. My test program threw an error that this folder already existed. So I deleted the folder and the original error came up back again.

I'm using SQL Server 2019.

Entity Framework 6.4 doesn't show this error, so I believe it's EF Core related.

Actually I've converted my Entity Framework 6.4 test solution to EF Core, so I'm pretty sure the user code is the same in both projects.

[ajcvickers]

@SetTrend We have not been able to reproduce this and it certainly looks like an issue with the SQL Server installation. I don't know why this is only manifesting with EF Core since EF6 and EF Core don't do anything fundamentally different with LocalDb. Without being able to reproduce what you are seeing I'm not sure there is anything else we can do here.

[SetTrend]

I added my Visual Studio solution above for being able to reproduce. Have you been able to run the single MS test function therein properly on your side?

[ErikEJ]

@SetTrend The file EF-DB.mdf is not included in your repro solution! So the test fails.

[SetTrend]

@ericstj: it's by intention.

The database context constructor constains these lines:

```c#
Database.EnsureDeleted();
Database.EnsureCreated();

According to [Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/managing-schemas/ensure-created), they are supposed to re-create the database.

In EF 6 I used the following construct to do the same:

```c#
internal class Initializer : DropCreateDatabaseIfModelChanges<Context>
{ ... }

[ErikEJ]

I think you need to change your connection string to use "Initial Catalog" instead of AttachDbFilename then.

[SetTrend]

You are guessing.

Shouldn't we better refrain from that?

Have you been able to reproduce the issue using the solution I provided?

[ajcvickers]

Note for triage: looks like LocalDb crashes when attempting to create a memory-optimized table. Minimal repro and stack below--removing the call to IsMemoryOptimized resolves the issue.

Note that LocalDb also leaves the database in an invalid state after crashing.

```C#
public static class Program
{
public static void Main()
{
using (var context = new SomeDbContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
}
}

public class SomeDbContext : DbContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().IsMemoryOptimized();
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

}

public class Post
{
public int Id { get; set; }
}

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command. The results, if any, should be discarded.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()
at Program.Main() in C:StuffAllTogetherNowDailyDaily.cs:line 23
ClientConnectionId:6214f8c1-3ee5-459a-a28e-8f2aea336352
Error Number:596,State:1,Class:21
```

[SetTrend]

Excellent research and observation! 👍

Do you believe this is the right place to keep this issue open? Or would you suggest to open an issue at some other repo to take over to solve the bug?

[ErikEJ]

@SetTrend This is due to auto_close being default for LocalDB - see https://dba.stackexchange.com/questions/258048/error-creating-memory-optimized-filegroup-in-ssdt - in other words, use SQL Server Express or higher with memory optimized tables

[SetTrend]

OK, now I have a workaround to work with.

Yet, is this behaviour supposed to be by design on LocalDB? I'm far from wanting to nag, still, LocalDB was the preferred way of developing for Entity Framework some time ago. However, this faulty behaviour, particular this cryptic error, doesn't seem to fit for utilizing LocalDB for development, does it?

[bricelam]

Docker is a lightweight and convenient way to get something more than LocalDB during development.

docker run -d -p 1433:1433 -e SA_PASSWORD=Password12! -e ACCEPT_EULA=Y mcr.microsoft.com/mssql/server

Connection string: Server=(local)\mssqlserver;Database=Test;UID=sa;Password=Password12!

[ajcvickers]

Note from triage: putting this on the backlog to consider detecting this in the migration to turn the bad crash into a better exception.

@SetTrend LocalDb is convenient for SQL Server on Windows, but has limitations. SQL Server Developer Edition is often a better option, either running in docker or just installed locally. It also runs on Linux, which is important for many people.

[SetTrend]

Sounds very good to me.

From my VS Enterprise subscription I have SQL Server Enterprise and Express installed locally in my development VMs. LocalDB came along with it. I remember that LocalDB had been endorsed for EF development for long time since.

To give a bit of reasoning: As a consultant I must have motivations behind my recommendations when a corporation team reaches out on me like "we want to migrate our existing project from EF to EF Core". They may have existing workflows, build pipelines or similar, so they try to avoid big, expensive, or "unnecessary" as they'd probably coin it, rework.

So, may I quote that LocalDB development is deprecated for working with EF Core?

[ErikEJ]

LocalDB works fine for many scenarios, not many use memory optimized tables...