Concurrency

• Entity Framework supports Optimistic Concurrency by default.
  • In the optimistic concurrency, EF saves the entity to the database, in the hope that the same data has not changed since the entity was loaded.
  • If it finds that the data has changed, then an exception is thrown and you must resolve the conflict before attempting to save it again.
• To handle optimistic concurrency with an entity, you need to have a RowVersion column in the table.
  • RowVersion is a datatype in the SQL Server that automatically generates unique binary number, whenever the insert or update operation is performed in a table.
• EF includes a RowVersion column in the where clause, whenever you do an update operation.
  • If the RowVersion value is different than in the where clause then it will throw DbUpdateConcurrencyExection.
• EF includes a property in the where clause, during the update operation, if the property is marked with the TimeStamp attribute.
  • The property type is byte[] because timestamp is binary in C#.

[Timestamp]
public byte[] RowVersion { get; set; }

• If you prefer to use the fluent API, you can use the IsConcurrencyToken method to specify the tracking property.

modelBuilder.Entity<Department>()
    .Property(p => p.RowVersion).IsConcurrencyToken();

Transactions

• Entity Framework automatically wraps all the queries made by the DbContext.SaveChanges() method in a single explicit database transaction.
  • It will use whatever default transaction isolation level the database engine has been configured to use (READ COMMITTED for SQL Server).
• You can use Database.BeginTransaction and Database.UseTransaction to provide more control over transactions.

using (var db = new MyDbContext(ConnectionString))
{
	using (var transaction = db.BeginTransaction(IsolationLevel.RepeatableRead))
	// Or: using (var transaction = db.Database.BeginTransaction())
	{
		try
		{
			// ...
			db.SaveChanges();
			transaction.Commit();
		}
		catch (Exception ex)
		{
			transaction.Rollback();
		}
	}
}

• Database.UseTransaction allows the DbContext to use a transaction which was started outside of the Entity Framework.
• An side-effect of manually controlling the transaction is that you are now forcing the connection and transaction to remain open for the duration of the transaction scope.
• There's no built-in way to override the default isolation level used for AutoCommit and automatic explicit transactions.
• EF relies on for read queries and the explicit transaction it automatically starts when SaveChanges() is called use whatever default isolation level the database engine has been configured with. There's no built-in way to override this isolation level.
  • If you'd like to use another isolation level, you must start and manage the database transaction yourself.
  • The database connection open by DbContext will enroll in an ambient TransactionScope.
  • Alternatively, you can also use the TransactionScope class to control the transaction scope and isolation level.
  • The database connection that Entity Framework opens will enroll in the ambient TransactionScope.
• In practice, and unless you actually need a distributed transaction, you should avoid using TransactionScope.
  • TransactionScope and distributed transactions in general, are not necessary for most applications and tend to introduce more problems than they solve.

using (var conn = new SqlConnection("..."))
{
	conn.Open();
	using (var sqlTransaction = conn.BeginTransaction(IsolationLevel.Snapshot))
	{
		try
		{
			var sqlCommand = new SqlCommand();
			sqlCommand.Connection = conn;
			sqlCommand.Transaction = sqlTransaction;
			
			// ...

			using (var db = new MyDbContext(conn, contextOwnsConnection: false))
			{
				db.Database.UseTransaction(sqlTransaction);
				// ...
				db.SaveChanges();
			}

			sqlTransaction.Commit();
		}
		catch (Exception)
		{
			sqlTransaction.Rollback();
		}
	}
}

Connection Resiliency and Retry Logic

• Connection Resiliency refers to the ability for EF to automatically retry any commands that fail due to these connection breaks.