Updating Memory Optimized Table

Question

I have a memory-optimized table in SQL Server 2017 called "User".
I'm going to update a row as:

using (var context = new MyDbEntities())
{
    var user = context.Users.Single(p => p.UserId == 1);
    user.Name = "John";
    context.SaveChanges();
}

At SaveChanges() statement, this exception appears:

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).

I've tried changing some options on my db, like:

alter database MyDb set ALLOW_SNAPSHOT_ISOLATION ON

Also using transaction with Snapshot isolation in my code, but nothing achieved.

Answer 1

Entity Framework wraps the SaveChanges() call in a transaction for you, with isolation level of the transaction by default (for SQL Server) READ COMMITTED.

You can wrap your Entity Framework code in a transaction yourself, for example:

using (var conn = new SqlConnection("...")) 
{ 
    conn.Open(); 
    using (var sqlTxn = conn.BeginTransaction(System.Data.IsolationLevel.Snapshot)) 
    { 
        using (var context =  new MyDBEntities(conn, contextOwnsConnection: false)) 
        { 
            context.Database.UseTransaction(sqlTxn);
            // ...
        }

    }
}

See the documentation: https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx