3

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

7

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.

3
  • 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
  • @Josh Darnell Hi, Did your test program call a stored procedure to access (write?) the in-memory OLTP table? Anybody tried this using EF5 & ObjectContext (rather than EF6 & DBContext)? From link above, looks like it might have same problem (transaction wrapper around operation) but solving it would not be straightforward/possible: for EF6 it says "ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden". – Zeek Oct 2 '20 at 14:02
  • @Zeek I didn't try calling a stored procedure. However, since the fix I described is on the database side, I would expect it to still work with EF5 and ObjectContext (automatically elevating the default isolation level to snapshot). Are you seeing different behavior, or you haven't tried it yet? – Josh Darnell Oct 2 '20 at 14:54

Your Answer

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