Linq to SQL uses a DataContext to manage it's access to the database as well as tracking changes made to entities retrieved from the database. Linq to SQL has a persistent approach to managing its 'connection' to the database via this data context and it basically assumes that you use a single DataContext to make all of your data related access. This doesn't mean that it makes persistent connections to the database, but means that the DataContext instance maintains state about active result sets, which is especially important if change tracking is on which is the default.

This is somewhat contrary to other ORM tools which tend to have a static manager to which you pass entities or queries that are then returned. In that respect most other ORMs are stateless in their data connectivity and management object where LINQ to SQL clearly takes a connected approach where everything revolves around this single DataContext instance.  DataContext holds all the change management information and it makes it very difficult to transfer that context information to another DataContext. In short it's a very different approach and requires some thinking about how you create and manage the DataContext object.

BTW, the ADO.NET Entity framework too uses a similar connected approach with its ObjectContext object which also manages state persistently and requires that you keep the object around if you want to do things like track changes.

This raises some interesting questions on how to manage the lifetime of the DataContext object. There are a lot of options of how you can deal hanging on (or not) to the DataContext. Here are a few different approaches:

  • Create a new Context for each atomic operation (Application level management)
  • Create a global DataContext and handle all operations against this single DataContext object
  • Create a thread specific DataContext
  • Create a per business object DataContext

What doesn't work

The first thing to understand is if you are coming from another ORM manager you might be tempted to create a new context for each individual operation. Other ORMs typically have a static DataManager that load, save and otherwise manage entity instances. So you might create a context and load an entity and then modify it. Later you then create another instance and try to save this entity. Other ORMs typically have a method to load an entity and another to save one with a parameter for an entity to save. This approach really doesn't work with LINQ to SQL because of the change information is contained in the DataContext. LINQ to SQL really only has one way to commit updates which is by calling SubmitChanges() that takes all the changes stored on DataContext and writes them to the data store. There's no real way to abort changes either other than re-creating a new DataContext (which is another very odd design choice) and effectively abandoning the existing data context.

What this means is that if you plan on using LINQ to SQL like a traditional ORM - forget it. It just doesn't work well. You need to deal with the DataContext and a connected approach.

So let's talk about a few different approaches.

Create a new Context for each atomic operation

Basically with this approach you'd create a new DataContext for each atomic operation which would include potentially multiple queries and updates against a database. This would also have to include transactional operations such as running Transactions across multiple operations. The idea is that you perform your task or tasks that atomically belong together on a single instance of the DataContext and you then get rid of it (or don't reuse it at least) when you're done.

This approach works in many situations, but it can be problematic if you need to pass data objects or the data context itself around to other components in your application. For example, if you use business objects there's no real clean way to get a context passed between business objects.

In addition LINQ to SQL is very different in the way it deals with 'disconnected entities' - entities that have been detached. It's very difficult to do the truly disconnected entity approach that most other ORMs use where you use a central manager to create entities and then pass those entities back to the manager to update. LINQ to SQL's change manager marks objects in such a way that these objects can be reattached to another DataContext, so you have to use the same instance or use a complex approach (such as first serializing and deserializing an entity) to 'clear' the previous context and then Attach it. Attach is pretty ugly with lots of quirks that don't work so this should be reserved for only those truly disconnected (ie. Web Services etc.) situations. If at all possible you should try to stay connected to DataContext to get consistent behavior.

When I say 'atomic' here I'm talking about sticking with a connected instance of the Context and using that instance for one or more operations that are in the same application context - a single update or related set of updates that make up an operation. In Web applications this can often be the context of a single page or a single operation (like Save or Delete) in that page.

Create a global DataContext and handle all operations against this single DataContext object

It might seem very tempting to use a global DataContext that is used for all data access and indeed in some  types of applications like a Desktop application that might actually work. You can have a global context that is used for all data operations, and that single global data context can then manage all data access and update task.

At first this might seem a perfect situation. You get the benefit of just one instance and you globally have access to this single DataContext. But there are problems with this too. In some situations you might need to perform multiple data operations possibly on the same data that can't be handled in the context of a single DataContext. For example, say you update multiple sets of tables - for example say Invoices and Purchase Orders and before you update either you decide that you only need to update one of the two updated sets and discard the other. DataContext's global context approach really allows only two options which is the SubmitChanges() or recrate your data context. If you have two sets of updated data and you want to only update one set there's really no way to do this. In other words if you have multiple simulataneous data operations that might not be directly linked/atomic, you don't have the individual control to just update a part of the changed data unless you manually roll back the changes by undoing them which is pretty ugly.

In addition if you have a single instance of the DataContext you also need to consider that options applied then are global. Options such ObjectTrackingEnabled or DeferredLoadingEnabled can't be set easily without potentially affecting other operations that also need that same DataContext.

So while this appraoch can work in some situations - especially query only based  applications - it probably isn't a great idea, as it's just too coarse of a scope and gives too little control over the DataContext operation.

Create a Thread Specific DataContext

The 'global' approach above also couldn't work for Web applications because Web applications have multiple threads - one for each request - accessing data, often simultanouesly. But it might still be useful to a have a request specific DataContext that is tied to the active thread, or in the case of a Web request to the active ASP.NET HttpContext. Along the same lines it might also be useful to tie a DataContext to a specific thread so it can be reused on that thread.

The following is a DataContextFactory implementation that allows tieing to HttpContext.Current if available or the current thread context:

/// <summary>
/// This class provides several static methods for loading DataContext objects 
/// in a variety of ways. You can load the data context as normal one new instance
/// at a time, or you can choose to use one of the scoped factory methods that
/// can scope the DataContext to a WebRequest or a Thread context (in a WinForm app
/// for example).
/// 
/// Using scoped variants can be more efficient in some scenarios and allows passing
/// a DataContext across multiple otherwise unrelated components so that the change
/// context can be shared. 
/// </summary>
public class DataContextFactory
{
    /// <summary>
    /// Creates a new Data Context for a specific DataContext type
    /// 
    /// Provided merely for compleness sake here - same as new YourDataContext()
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <returns></returns>
    public static TDataContext GetDataContext<TDataContext>()
            where TDataContext : DataContext, new()
    {
        return (TDataContext)Activator.CreateInstance<TDataContext>();
    }
 
    /// <summary>
    /// Creates a new Data Context for a specific DataContext type with a connection string
    /// 
    /// Provided merely for compleness sake here - same as new YourDataContext()
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="connectionString"></param>
    /// <returns></returns>
    public static TDataContext GetDataContext<TDataContext>(string connectionString)
            where TDataContext : DataContext, new()
    {
        Type t = typeof(TDataContext);
        return (TDataContext) Activator.CreateInstance(t,connectionString) ;
    }
 
 
    /// <summary>
    /// Creates a ASP.NET Context scoped instance of a DataContext. This static
    /// method creates a single instance and reuses it whenever this method is
    /// called.
    /// 
    /// This version creates an internal request specific key shared key that is
    /// shared by each caller of this method from the current Web request.
    /// </summary>
    public static TDataContext GetWebRequestScopedDataContext<TDataContext>()
            where TDataContext : DataContext, new()
    {
        // *** Create a request specific unique key 
        return (TDataContext)GetWebRequestScopedDataContextInternal(typeof(TDataContext), null, null);
    }
 
    /// <summary>
    /// Creates a ASP.NET Context scoped instance of a DataContext. This static
    /// method creates a single instance and reuses it whenever this method is
    /// called.
    /// 
    /// This version lets you specify a specific key so you can create multiple 'shared'
    /// DataContexts explicitly.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    public static TDataContext GetWebRequestScopedDataContext<TDataContext>(string key)
                               where TDataContext : DataContext, new()
    {        
        return  (TDataContext) GetWebRequestScopedDataContextInternal(typeof(TDataContext),key, null);
    }
 
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    public static TDataContext GetWebRequestScopedDataContext<TDataContext>(string key, string connectionString)
                               where TDataContext : DataContext, new()
    {
        return (TDataContext) GetWebRequestScopedDataContextInternal(typeof(TDataContext), key, connectionString);
    }
 
    /// <summary>
    /// Internal method that handles creating a context that is scoped to the HttpContext Items collection
    /// by creating and holding the DataContext there.
    /// </summary>
    /// <param name="type"></param>
    /// <param name="key"></param>
    /// <param name="connectionString"></param>
    /// <returns></returns>
    static object GetWebRequestScopedDataContextInternal(Type type, string key, string connectionString)                                   
    {
        object context;
 
        if (HttpContext.Current == null)
        {
            if (connectionString == null)
                context = Activator.CreateInstance(type);
            else
                context = Activator.CreateInstance(type, connectionString);
 
            return context;
        }
 
        // *** Create a unique Key for the Web Request/Context 
        if (key == null)
            key = "__WRSCDC_" + HttpContext.Current.GetHashCode().ToString("x") + Thread.CurrentContext.ContextID.ToString();
 
        context = HttpContext.Current.Items[key];
        if (context == null)
        {
            if (connectionString == null)
                context = Activator.CreateInstance(type);
            else
                context = Activator.CreateInstance(type, connectionString);
 
            if (context != null)
                HttpContext.Current.Items[key] = context;
        }
 
        return context;
    }
 
 
    /// <summary>
    /// Creates a Thread Scoped DataContext object that can be reused.
    /// The DataContext is stored in Thread local storage.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    public static TDataContext GetThreadScopedDataContext<TDataContext>()
                               where TDataContext : DataContext, new()
    {
        return (TDataContext)GetThreadScopedDataContextInternal(typeof(TDataContext), null, null);
    }
 
 
    /// <summary>
    /// Creates a Thread Scoped DataContext object that can be reused.
    /// The DataContext is stored in Thread local storage.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    public static TDataContext GetThreadScopedDataContext<TDataContext>(string key) 
                               where TDataContext : DataContext, new()
    {
        return (TDataContext)GetThreadScopedDataContextInternal(typeof(TDataContext), key, null);
    }
 
 
    /// <summary>
    /// Creates a Thread Scoped DataContext object that can be reused.
    /// The DataContext is stored in Thread local storage.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    static object GetThreadScopedDataContextInternal(Type type, string key, string ConnectionString) 
    {
        if (key == null)
            key = "__WRSCDC_" + Thread.CurrentContext.ContextID.ToString();
 
        LocalDataStoreSlot threadData = Thread.GetNamedDataSlot(key);
        object context = null;
        if (threadData != null)
            context = Thread.GetData(threadData);
 
        if (context == null)
        {
            if (ConnectionString == null)
                context = Activator.CreateInstance(type);
            else
                context = Activator.CreateInstance(type,ConnectionString);
 
            if (context != null)
            {
                if (threadData == null)
                    threadData = Thread.AllocateNamedDataSlot(key);
 
                Thread.SetData(threadData, context);
            }
        }
 
        return context;
    }
 
 
    /// <summary>
    /// Returns either Web Request scoped DataContext or a Thread scoped
    /// request object if not running a Web request (ie. HttpContext.Current)
    /// is not available.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <param name="ConnectionString"></param>
    /// <returns></returns>
    public static TDataContext GetScopedDataContext<TDataContext>(string key, string connectionString)
    {
        if (HttpContext.Current != null)
            return (TDataContext) GetWebRequestScopedDataContextInternal(typeof(TDataContext), key, connectionString);
 
        return (TDataContext)GetThreadScopedDataContextInternal(typeof(TDataContext), key, connectionString);
    }
 
    /// <summary>
    /// Returns either Web Request scoped DataContext or a Thread scoped
    /// request object if not running a Web request (ie. HttpContext.Current)
    /// is not available.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    public static TDataContext GetScopedDataContext<TDataContext>(string key)
    {
        if (HttpContext.Current != null)
            return (TDataContext)GetWebRequestScopedDataContextInternal(typeof(TDataContext), key, null);
 
        return (TDataContext)GetThreadScopedDataContextInternal(typeof(TDataContext), key, null);
    }
 
    /// <summary>
    /// Returns either Web Request scoped DataContext or a Thread scoped
    /// request object if not running a Web request (ie. HttpContext.Current)
    /// is not available.
    /// </summary>
    /// <typeparam name="TDataContext"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    public static TDataContext GetScopedDataContext<TDataContext>()
    {
        if (HttpContext.Current != null)
            return (TDataContext)GetWebRequestScopedDataContextInternal(typeof(TDataContext), null, null);
 
        return (TDataContext)GetThreadScopedDataContextInternal(typeof(TDataContext), null, null);
    }
 
}

This approach might be a little more palatable than the global approach in the last bullet, especially for Web applications in that single page requests in a Web app typically stay within a specific range of related operations that can often be accomplished within a single data context. Using this approach lets you create a single DataContext that can be easily shared for a single page request.

The class above basically lets you create data contexts in HttpContext's Item collection or on a ThreadContext bag. These objects are then generically accessible by name from anywhere that has access to the static DataContextFactory.

In essence this is a variation of the global scheme but one that is a bit more generic and even easy enough to hook up so that you can use several DataContexts side by side easily.

Because this is tied to a static factory object  it also makes it much easier to share these instances between components. You can access these shared objects both from the application layer as well as from a business layer without having to track an instance in your own code.

Create a per Business Object instance of DataContext

Another semi-persistent approach is to use a DataContext object that's hung off a business object. In this way you have a business object and a related DataContext that ties operations of that business object to a specific DataContext. Presumably your business object's operations would be fairly atomic in nature and so you effectively can wrapper the business object around the DataContext. If for some reason you need two instances that perform tasks side by side with different DataContext options you could simply fire up another business object instance and configure that independently.

The idea is that each business object manages its own DataContext instance since a business object is already a typical unit of related operations. So a typical method in a business object then has direct access to the data context:

/// <summary>
 /// Gets a list of recent entries 
 /// </summary>
 /// <param name="userPk"></param>
 /// <param name="Count"></param>
 /// <returns></returns>
 public IQueryable<EntryEntity> GetEntries(int userPk)
 {
    IQueryable<EntryEntity> q =
        from e in this.Context.EntryEntities                
        orderby e.TimeIn descending
        select e;
 
    return q;
 }

The other advantage of this approach is that the DataContext is effectively wrapped up inside of the business object. In this way the business object controls DataContext instantiation and destruction, and more importantly you can pass the data context to other components in the system simply by passing the business object.

For clarification a business object in this scenario would be an object that performs operations against the entity model and LINQ to SQL. LINQ to SQL provides the entity/data access layer while the business object provides the application logical layer. In addition a business object would also provide more high level features like Load/Delete/Save/NewEntity/Validate etc. functionality that doesn't require actual access to the LINQ model directly - the business objects would handle all the data manipulation.

I've been using this approach as part of my Business Object wrapper around LINQ to SQL for some time and it works very well. You do potentially end up with multiple DataContext instances (one for each business object) but the number is typically small  (ie. 1 to 3 at a time typically). Overhead for creating a DataContext is not nill, but in my experience it's not enough to worry with small numbers of instances created per request.

The real advantage for me is the fact that I don't have to worry about the data context lifetime. I create my business objects on the application level, call a method and it internally manages creating the instance making the calls, managing change state and updates and then I can simply get rid of the object and be done. No worries about lifetime beyond the business object lifetime. In addition the business object provides an easier way yet to consistently handle basic CRUD operations without having to write LINQ syntax.

This approach can also be combined with the Thread specific approach in the last bullet. Rather than explicitly creating a data context you could decide if your object could be shared amongst many operations and in that case could be loaded from the thread context rather than creating a new instance each time.

DataContext and Threading

One thing I was wondering about today was how DataContext would work if you access a DataContext across threads - for example for an asynchronous operation in an ASP.NET. It turns out that this actually works fine (although I don't think it's thread safe for simultaneous access from multiple threads simultaneously.

The reason I was worried about this is that a few months back I'd been looking at Visual WebGui and it didn't work well in its semi-stateful mode of persisting a DataContext across requests. Basically what happened with VIsual WebGui is that loading the page and then later accessing the same DataContext would break apparently of the way that the threading and reconnection of the state for callbacks works.

To test I ran a kind of a silly test in an ASP.NET page to simulate a disconnected operation. It's totally faked up with a short wait operation to let the thread operation complete. Obviously this is not a practical approach for anything async - using AsycnTasks is a much better choice for any disconnected operations.

public partial class ThreadContext : System.Web.UI.Page
{
    TimeTrakkerContext Context = new TimeTrakkerContext();
    IQueryable<CustomerEntity> CustomerList;
    CustomerEntity Customer;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        CustomerList =
            from c in Context.CustomerEntities
            where c.Company.StartsWith("West")
            select c;
 
        this.Customer = CustomerList.First();
 
        Response.Write("Original Values:<br>" + this.Customer.Company + " " + this.Customer.Address + " " + Thread.CurrentThread.ManagedThreadId.ToString());
 
        this.Customer.Company = "West Wind Technologies " + DateTime.Now.ToLongTimeString(); 
 
        Thread thread = new Thread(new ThreadStart(this.AccessDataContextOnThread));
        thread.Start();
 
        Thread.Sleep(2000);
    }
 
    public void AccessDataContextOnThread()
    {
        this.Customer.Address = "33 Kaiea Place " + DateTime.Now.ToLongTimeString();
        this.Context.SubmitChanges();
 
        Response.Write("<hr>From Thread:<br>" + this.Customer.Company + " " + this.Customer.Address + Thread.CurrentThread.ManagedThreadId.ToString());
 
    }
}

However it does demonstrate clearly that you can in fact access the DataContext across separate threads. For asynchronous scenarios in ASP.NET this should work well enough so you can do offloading with AsyncTasks or off to separate threads to continue processing after a request has completed or asynchronously while ASP.NET frees up an HttpApplication thread.

Even so DataContext works across threads, using it with multiple threads simultaneously surely would be disastrous, and wouldn't make much sense. One user updating while another is still editing would result in very undesirable operations indeed <s>... in any case DataContext is not thread safe anyway and all you'd accomplish is a hard crash eventually so avoid simultaneous multi-thread access.

In the end the key for DataContext change management is to ensure that the DataContext is always accessible and doesn't need to be recreated. As long as you can find some way to hang on to a single data context it can be used and be expected to correctly deal with change management.

Unfortunately as I pointed here and here , once you disconnect from the DataContext, the story with LINQ to SQL reconnecting and synching with another DataContext is bleak at best. So the best strategy is to keep hanging on to to the DataContext if you at all can <shrug>. Not always possible when you really need to cross machine boundaries with Web Services, but at least in that scenario you do get a fresh de-serialized instance of an object that can potentially be resynched by using Table.Attach().

For anything else it'll be better to implement something like one of the strategies above to hang on to your DataContext.