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

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

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") });
    }
  }
}

Here’s the test code

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

About this issue

Original URL
State: open
Created 4 years ago
Comments: 17 (10 by maintainers)

Most upvoted comments

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.

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<Post>().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, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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(TaskCompletionSource`1 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, IReadOnlyDictionary`2 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:\Stuff\AllTogetherNow\Daily\Daily.cs:line 23
ClientConnectionId:6214f8c1-3ee5-459a-a28e-8f2aea336352
Error Number:596,State:1,Class:21

ajcvickers on Aug 3, 2020

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

ErikEJ on Aug 9, 2020