Entity Framework 6 was and still remains a ‘workhorse’ for data access incorporate. NET-based applications primarily because of their stability, low barrier of entry, and wide renown. Therefore, I hope this article will still be useful.
Contents
- Database First without EDMX
- Working with detached graphs
- SQL modification
- Data caching beyond the boundaries of the DbContext life-cycle
- Retry during errors from SQL Server
- Overriding DbContext, isolating from the real DB
- Quick insert
Database First Without EDMX
I really don’t want to go into the old debate on Code First vs. Database First. Instead, I’d better write a few words on how to make your life easier, if you prefer Database First. Many developers who prefer this approach complain about inconveniences when working with the tedious EDMX file. This file can turn team development into real hell: it significantly slows down the merge of parallel changes because of the permanent ‘mixing’ of its internal structure. As for the models with several hundreds of instances (a typical legacy monolith), you can face a strong speed slowdown of any action when working with the standard EDMX designer.
The solution seems to be quite obvious – you need to abandon EDMS and prefer an alternate means of the POCO generation and metadata storage. Well, it looks like a simple task, and EF has the Generate Code First From Database feature that is viable in Visual Studio (inVS2015 for sure). But in real life, rolling database changes onto the received model is very inconvenient with this tool. Furthermore, everyone working with EF for a long time remembers the Entity Framework Power Tools extension, that solves similar problems, but unfortunately, this project seems to be almost dead (you cannot install it on VS2015 without hacking), and a part of its developers now works in the EF team.
When everything looked so bad, I found EntityFramework Reverse POCO Generator. It is a T4 template for the POCO generation on the basis of the existing DB with a large number of settings and the open source code. It supports all basic EDMX features and includes a range of additional tips: generation of FakeDbContext/FakeDbSet for unit testing, attribute coverage for models (e.g. DataContract/DataMember), and others. Also, T4 provides full control over code generation. To sum up: it works consistently, the team enjoys it, and migration of existing projects goes smoothly.
Working with Detached Graphs
Usually, attaching a new object or an object that was previously generated in other context is a simple task. Problems begin when it comes to graphs, that is entities with links: EF ‘out of the box’ does not track changes in the content of navigation properties of an entity reattached to the context. To track changes, the corresponding entry must exist (an object with service information, including info about the state of entity – Added, Modified, Deleted, etc) for each object entity during the life-cycle of the context. You can fill entries for adding graph in the following 2 ways:
- You can store the state within the entities and track changes on your own. Thus, our detached graph will contain all information required for connection.
- You can do nothing beforehand, and when you attach a graph to the context, you need to pull up the source graph from DB and set the entity states basing on the comparison of two graphs.
An example of solution #1 can be found in the Pluralsight course from Julie Lerman, a renown EF expert. You will need to take a large number of steps for its implementation. All entities must implement the IstateObject interface:
public interface IStateObject
{
ObjectState State { get; set; }
}
One way or another, we need to ensure the relevancy of the State values after the manual addition of each graph entity to the context
context.Foos.Attach(foo);
in order to pass through all entries by editing their states:
IStateObject entity = entry.Entity;
entry.State = ConvertState(entity.State);
In this case, we won’t need additional DB calls, but solution turns out to be too jumbo, fragile, and potentially not working for the many-to-many relations. Besides, it lumbers models (by the way, the requirement of interface implementation can be extended with modification of the T4 templates from the previous section of this article).
Let’s consider solution #2 briefly:
context.UpdateGraph(root, map => map.OwnedCollection(r => r.Childs));
This call will add the root entity to the context. At that, it will update the navigation property with the Childs objects collection by means of a single SELECT to DB. It is now possible owing to the GraphDiff library. The author of the library has made all the dirty work and fixed basic bugs.
SQL Modification
Generation of the seemingly simple SELECT… FROM Table WITH (UPDLOCK) statement is not supported by EF. Instead, it has interceptors allowing to modify the generated SQL in any suitable way. For example, with help of regular expressions. Let’s add UPDLOCK to each generated SELECT within the life-cycle of the context (of course, granularity is not a necessary context, it depends on your implementation).
using (var ctx = new MyDbContext().With(SqlLockMode.UpdLock)) {}
For this, let’s declare the With method within the context and register the interceptor:
public interface ILockContext
{
SqlLockMode LockMode { get; set; }
MyDbContext With(SqlLockMode lockMode);
}
public class MyDbConfig : DbConfiguration
{
public MyDbConfig()
{
AddInterceptor(new LockInterceptor());
}
}
[DbConfigurationType(typeof(MyDbConfig))]
public partial class MyDbContext : ILockContext
{
public SqlLockMode LockMode { get; set; }
public MyDbContext With(SqlLockMode lockMode)
{
LockMode = lockMode;
return this;
}
private static void MyDbContextStaticPartial() { }
}
LockInterceptor
public class LockInterceptor : DbCommandInterceptor
{
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
AddLockStatement(command, interceptionContext);
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
AddLockStatement(command, interceptionContext);
}
private void AddLockStatement(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
var lockMode = GetLock(interceptionContext);
switch (lockMode)
{
case SqlLockMode.UpdLock: command.CommandText = SqlModifier.AddUpdLock(command.CommandText);
break;
}
}
private SqlLockMode GetLock(DbCommandInterceptionContext interceptionContext)
{
if (interceptionContext == null) return SqlLockMode.None;
ILockContext lockContext = interceptionContext.DbContexts.First() as ILockContext;
if (lockContext == null) return SqlLockMode.None;
return lockContext.LockMode;
}
}
Regular expression may look in the following way:
public static class SqlModifier
{
private static readonly Regex _regex = new Regex(@"(?SELECT\s.*FROM\s.*AS \[Extent\d+])",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
public static string AddUpdLock(string text)
{
return _regex.Replace(text, "${tableAlias} WITH (UPDLOCK)");
}
}
Testing of our regular expression:
public class SqlModifier_Tests
{
[TestCase("SELECT [Extent1].[Name] AS [Name] FROM [dbo].[Customer] AS [Extent1]")]
[TestCase("SELECT * FROM [dbo].[Customer] AS [Extent999]")]
public void AddUpdLock_ValidEfSelectStatement_AddLockAfterTableAlias(string text)
{
string expected = text + " WITH (UPDLOCK)";
string actual = SqlModifier.AddUpdLock(text);
Assert.AreEqual(expected, actual);
}
[TestCase("SELECT [Extent1].[Extent1] AS [Extent1]")]
[TestCase("SELECT * FROM Order")]
[TestCase(" AS [Extent111]")]
public void AddUpdLock_InvalidEfSelectStatement_NoChange(string text)
{
string actual = SqlModifier.AddUpdLock(text);
Assert.AreEqual(text, actual);
}
}
Data Caching Beyond the Boundaries of the DbContext Life-Cycle
EF caches such things, as:
- Query Plan
- Metadata
- Compiled Queries
Data caching is possible within the boundaries of the context life-cycle (recall the Find method). Moreover, it can hardly be called a full-featured cache. So, how can we organize a managed cash in the process memory that would be universal for all contexts? We will use EntityFramework.Plus, or EntityFramework.Cache, its “poor” alternative:
public void SelectWithCache()
{
using (var ctx = new MyDbContext())
{
ctx.Customers.FromCache().ToList();
}
}
[Test]
public void SelectWithCache_Test()
{
TimeSpan expiration = TimeSpan.FromSeconds(5);
var options = new CacheItemPolicy() { SlidingExpiration = expiration };
QueryCacheManager.DefaultCacheItemPolicy = options; SelectWithCache(); //query to DB
SelectWithCache(); //from cache
Thread.Sleep(expiration);
SelectWithCache(); //query to DB
}
It is enough to run SQL profiler to ensure that the second call ofSelectWithCache() does not affect DB. Lazy calls will also be cached. Moreover, you can integrate EF with a distributed cache. For instance, through the self-written cache manager on the basis of Sytem.Runtime.Caching.ObjectCache that is connected to EntityFramework.Plus. NCache supports integration with EF ‘out of the box’ (I cannot go into detail – I haven’t tested this cache).
Retry during errors from SQL Server
public class SchoolConfiguration : DbConfiguration
{
public SchoolConfiguration()
{
SetExecutionStrategy("System.Data.SqlClient", () =>
new SqlAzureExecutionStrategy(maxRetryCount: 3, maxDelay: TimeSpan.FromSeconds(10)));
}
}
SqlAzureExecutionStrategy – this strategy is supported in EF6 (it is disabled by default). During its usage, getting of the specific error code in a response from SQL Server leads to the resending of SQL statement to the server.Error Codes for SqlAzureExecutionStrategy.
// SQL Error Code: 40197
// The service has encountered an error processing your request. Please try again.
case 40197:
// SQL Error Code: 40501
// The service is currently busy. Retry the request after 10 seconds.
case 40501:
// SQL Error Code: 10053
// A transport-level error has occurred when receiving results from the server.
// An established connection was aborted by the software in your host machine.
case 10053:
// SQL Error Code: 10054
// A transport-level error has occurred when sending the request to the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 10054:
// SQL Error Code: 10060
// A network-related or instance-specific error occurred while establishing a connection to SQL Server.
// The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server
// is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed
// because the connected party did not properly respond after a period of time, or established connection failed
// because connected host has failed to respond.)"}
case 10060:
// SQL Error Code: 40613
// Database XXXX on server YYYY is not currently available. Please retry the connection later. If the problem persists, contact customer
// support, and provide them the session tracing ID of ZZZZZ.
case 40613:
// SQL Error Code: 40143
// The service has encountered an error processing your request. Please try again.
case 40143:
// SQL Error Code: 233
// The client was unable to establish a connection because of an error during connection initialization process before login.
// Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy
// to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 233:
// SQL Error Code: 64
// A connection was successfully established with the server, but then an error occurred during the login process.
// (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
case 64:
// DBNETLIB Error Code: 20
// The instance of SQL Server you attempted to connect to does not support encryption.
case (int)ProcessNetLibErrorCode.EncryptionNotSupported:
return true;
Interesting nuances:
- You can write your own strategy on the basis of the SqlAzureExecutionStrategy source code by redefining the error codes leading to retry.
- Usage of the retry strategies, inducing SqlAzureExecutionStrategy, imposes a series of restrictions. The most serious of them is incompatibility with user transactions. For the explicit declaration of the transaction, we need to disable it through the call to System.Runtime.Remoting.Messaging.CallContext.
- The strategy may be covered with integration tests (again, thanks to Julie Lerman, who kindly replied to this question).
Overriding DbContext, Isolating From the Real DB
For testing purposes, let’s override DbContext for the invoking code transparently, and fill the fake DbSet with test data. I will provide several examples of solutions to this problem: Method #1 (long): we need to create stubs for IMyDbContext and DbSet manually, and to describe the required behavior explicitly. It may look in the following way (using the Moq library):
public IMyDbContext Create()
{
var mockRepository = new MockRepository(MockBehavior.Default);
var mockContext = mockRepository.Create<IMyDbContext>();
mockContext.Setup(x => x.SaveChanges()).Returns(int.MaxValue);
var mockDbSet = MockDbSet<Customer>(customers);
mockContext.Setup(m => m.Customers).Returns(mockDbSet.Object);
return mockContext.Object;
}
private Mock<DbSet<T>> MockDbSet<T>(List<T> data = null)
where T : class
{
if (data == null) data = new List<T>();
var queryable = data.AsQueryable();
var mock = new Mock<DbSet<T>>();
mock.As<IQueryable<T>>().Setup(m => m.Provider)
.Returns(queryable.Provider);
mock.As<IQueryable<T>>().Setup(m => m.Expression)
.Returns(queryable.Expression);
mock.As<IQueryable<T>>().Setup(m => m.ElementType)
.Returns(queryable.ElementType);
mock.As<IQueryable<T>>().Setup(m => m.GetEnumerator())
.Returns(queryable.GetEnumerator());
return mock;
}
There is a basic article in MSDN related to this subject: Entity Framework Testing with a Mocking Framework (EF6 onwards). There was a time when I was so excited with this method, that I created the whole demo project on GitHub (with the usage of EF6 DbFirst, SQL Server, Moq, Ninject). By the way, the above-mentioned Entity Framework in the Enterprise course features the entire chapter dedicated to testing. Method #2 (short): usage of the already mentioned Reverse POCO Generator that creates stubs for your DbContext’s and all DbSet’s by default (there will be a normal in-memory collection inside FakeDbSet).
Quick Insert
To insert thousands of new records in SQL DB simultaneously, it is effective to use the BULK operations instead of the standard row-by-row INSERT. Here are ready-to-use solutions on the basis of SqlBulkCopy:
That’s it. Share your tips and tricks in the comments below.
Tags: .net framework, ado.net, entity framework Last modified: October 06, 2021
Excellent post, May I translate to Spanish your article on my blog?
Hi Erick,
Yes you can translate the current article to Spanish. Please add to the Spanish version the link to the current resource (or article) as a primary source. Also please specify the original author – Ilya Chumakov. Thank you.