1

Are there any restrictions with having Entity Framework access In Memory OLTP Tables? Or is this not possible?

4

I created a small test application to see if there were any issues accessing IMOLTP tables from Entity Framework. My test application uses the latest stable version of Entity Framework (EF6).

With the default settings, I got this error initially trying to insert a row into a memory-optimized table:

SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

This is because Entity Framework creates an explicit transaction automatically when using the "SaveChanges()" method (the most common approach for inserting rows):

In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction.

The simplest way to work around this in the case of EF is to turn on the database-level option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which essentially adds the needed WITH (SNAPSHOT) hint automatically:

ALTER DATABASE [YourDatabaseName] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

Other than that, there seems to be no issues with accessing the in-memory tables. This isn't all that surprising, since under the covers Entity Framework is using the .NET Framework Data Provider for SQL Server (SqlClient), which has no documented limitations when it comes to in-memory tables.

  • Create an explicit transaction with the isolation level you want. EF will use that. This is best practice anyway because otherwise you can get a random isolation level inherited from the pooled connection for EF select queries. – usr Sep 30 '18 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy