Linq to Sql Horrors

Linq to Sql is a kind of ORM for .Net by Microsoft. Working with Linq to Sql can be a nightmare.

• The basics:

In order to send or retrieve objects to or from the database, you need a DataContext instance. This is an instance of a class that inherits DataContext. It is created by Visual Studio with drag and drop. One context is generated for every database you access to. This generated context is inside a .cs file which contains also the classes that map database tables. The mapping is contained in a .dbml file (xml) generated in the same folder. You should not change any of this generated files. If a table changes in the database you must regenerate with the IDE.

Select example:
If the name of the database is ProductionDB, the DataContext will be ProductionDBDataContext.

var context = new ProductionDBDataContext(connectionString);
var userEntity = (from user in context.Users
	where user.name == "carlos"
        select user).First();

Insert example:

var context = new ProductionDBDataContext(connectionString);
var user = new User();
user.name = "manolo"; 
context.Users.InsertOnSubmit(user);
context.SubmitChanges();

• The main issue:

The same context instance have to be used for all the operations performed on a relational object instance. This would throw an exception:

var context = new ProductionDBDataContext(connectionString);
var userEntity = (from user in context.Users
	where user.name == "carlos"
        select user).First();
// Imagine this is part of another method in another place
// but with the same userEntity instance that was passed through:
userEntity.age = "30";
var otherContextInstance = new ProductionDBDataContext(connectionString);
otherContextInstance.Users.InsertOnSubmit(user);
otherContextInstance.SubmitChanges();

So you can’t just pass relational objects from one method or layer to anoter, you have to always cope with the “same context instance problem”.

•  Solutions

1. Pass the context instance along with the relational objects, in all the places where you need to perform database access:   someMethod(user, context);
   Bad choice: This leads to spaguetti code quickly. Why  does my business layer have to know about a database context?
2. Ok so we can make a Singleton for the context instance and make sure it is the same for all the application:
   public static ProductionDBDataContext AppContext = new ProductionDBDataContext(connString);
   Bad choice: When more than one request hit the server at the same time you get concurrency problems. The context will be trying to insert in one thread while trying to update or select in other. Weird race conditions happens and exceptions are thrown.
3. Create a context for every thread. Every thread will have its own context Singleton. We found this solution thanks to Rick Strahl and his great post. However Rick’s code can be simplified this way:

public class ContextFactory
{
	[ThreadStatic]
	private static ProductionDBDataContext context;
	private string connString = "settings to access the database ...";

	public static DataContext Context(){
		if (context == null)
			context = new ProductionDBDataContext(connString);
		return context;
	}
}

The ThreadStatic attribute ensures that every thread will access a different instance. So the “context” is not shared amont threads. This simplifies Ricks’s code. But our problems didn’t stop here unfortunatelly 🙁

You might expect the thread to die when the response is sent back to the client side, but it doesn’t. Sometimes IIS reuses the thread for other requests, and the context is not a new, fresh instance. Moreover, the context caches some queries and that leads to several issues:

1. The optimistic concurrency control nightmare. The default behavior is for “Linq to Sql” to check every db column on update, to see if there are more than one simultaneous attemp to persist the entity. If so, it raises the ChangeConflictException. This can be avoided by editing the dbml and marking every column with “Never” in the UpdateCheck property. Guess that…. when you regenerate the dbml file, this is overwritten and you loose your configuration. More information on conflicts here.
   One way to deal with conflicts is to catch the exception and force the update:

   var someEntity = ... // query here
   try
   {
       context.SubmitChanges(ConflictMode.ContinueOnConflict);
   }
   catch (ChangeConflictException e)
   {
      context.Refresh(RefreshMode.KeepCurrentValues, someEntity);
   }

   However, this does not avoid the cache invalidation problem.

2. The cache invalidation problemis the last one we have found and solved so far. The solution is to drop the cache as the new request enters the server. To refresh the cache just create a new instance of the context:

   public class ContextFactory
   {
   	[ThreadStatic]
   	private static ProductionDBDataContext context;
   	private string connString = "settings to access to database ...";
   
   	public static DataContext Context(){
   		if (context == null)
   			context = new ProductionDBDataContext(connString);
   		return context;
   	}
   	public static void FlushContext(){
   		context = new ProductionDBDataContext(connString);
   	}
   
   }

   Then, in the first layer of the server side (where the request is received) we call FlushContext to make sure it is new for this request. (For us is our JsonRpcController, a custom make controller hierarchy).

For now, we can rest assured that our DataContext is:

• Unique for every thread so it is thread-safe.
• Unique for every request so there are no cache problems among different requests.
• If two different parts of the application need a context’s instance in the same request, our factory gives a single reference (Singleton), making sure that only the first access initializes it (because we check whether it is null before instantiating).