Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Linq to SQL DataContext Lifetime Management


:P
On this page:

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.

Posted in ASP.NET  LINQ  

The Voices of Reason


 

Damien Guard
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

This is something I was asking Microsoft (well Scott Guthrie via his blog) for guidance on.

The problem stems from the fact Microsoft have effectively merged two design patterns (Unit of work and Data gateway) into a single object - DataContext.

I believe they want us to consider one DataContext per unit of work hence why the object creation cost was dramatically reduced between beta 2 and RTM (it used to recreate the entire metadata mapping layer).

The real problem with sharing them between threads/users is calling SubmitChanges on one thread might attempt to commit new objects and associations that aren't yet complete on the other.

[)amien

Rick Strahl
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

@Damien - The cross thread issues are pretty serious even IF DataContext was thread safe (which it's not). To be clear I wasn't suggesting that one should do that <g>. The thread bound factory is meant to provide a resusable instance across a single thread/HttpContext so if you do want to share a single instance you can do so fairly easily from a single static factory which is often easier than creating some factory class or method somewhere.

I briefly looked at overhead for object creation in the RC and the overhead was nearly negliable. I suspect it's fine in most situations to create a DataContext when needed for each unit of...

mike
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

While I can normally follow your posts, this was way above my head. But the thing is, that in order to use LINQ to SQL _without_ the LinqDataSource in my pages I have to understand this! I think Microsoft really made a terrible mistake here: it's a too complicated (for non brilliant, non experienced programmers like me). This requires at least 3 readings before I finally get the picture. Meanwhile, I can't even get updates to work because the Attach() method is throwing exceptions left and right in my code!

In ASP.NET the request that loads an entity for editing and the postback containing the new values from the user are completely separate, no way around that. How come someone like Scott Guthrie tells us to use the Attach() method, when in reality this does not work (without timestamp fields or tediously setting all fields to not check for concurrency)? Does he even know, or are all his demos using the LinqDataSource? MS will always say that it is by desing, but imho then the design has a bug.

You are one of the few that are explaining this in depth, thanks for your effort! It helps me understand (with some effort) the nature of Linq to SQL much better than the ASP.NET website or the drag and drop stuff Scott Guthrie makes us believe is sooo wonderful.

Richard Deeming
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

A couple of comments on the DataContextFactory:

1. If you're not passing a connection string to the constructor, and you have specified the "new()" constraint, you don't need to call Activator.CreateInstance - just create an instance:

public static TDataContext GetDataContext<TDataContext>()
    where TDataContext : DataContext, new()
{
    return new TDataContext();
}



2. If your method doesn't use the "new TDataContext()" syntax, you don't need the "new()" constraint.


3. When building the key for the request / thread scoped context, you don't need to use the hash-code or ID of the current context. Apart from the fact that you're limiting your solution to a single data context per app-domain, you're creating a new data slot for every thread, when a single data slot will suffice. Try using a simple key instead:

string key = "__WRSCDC_" + typeof(TDataContext).AssemblyQualifiedName;
if (!string.IsNullOrEmpty(connectionString)) key += "#" + connectionString;



4. For performance reasons, it might be a good idea to offer overloads which accept a Func<string, TDataContext> which can be used to create a new instance. This would avoid the relatively slow call to Activator.CreateInstance.


5. The GetWebRequestScopedDataContext methods look like good candidates for extension methods:

public static TDataContext GetWebRequestScopedDataContext<TDataContext>(
    this HttpContext context,
    string key,
    string connectionString,
    Func<string, TDataContext> create)
    where TDataContext : DataContext
{
    TDataContext result = null;
    if (null == context)
    {
        if (null != create)
        {
            result = create(connectionString);
        }
    }
    else
    {
        if (string.IsNullOrEmpty(key))
        {
            key = "__WRSCDC_" + typeof(TDataContext).AssemblyQualifiedName;
            if (!string.IsNullOrEmpty(connectionString)) key += "#" + connectionString;
        }
        
        result = context.Items[key] as TDataContext;
        if (null == result && null != create)
        {
            result = create(connectionString);
            if (null != result) context.Items[key] = result;
        }
    }
    
    return result;
}

Rick Strahl
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

@Mike - This post is kind of meant to give a little bit deeper insight into ways of dealing with the DataContext, but it doesn't mean that you have to use code like the code I posted here (that's actually a specialty case).

The main point I'm trying to make is that LINQ to SQL works well when you keep to a connected DataContext instance. I suspect you are trying to do exactly what I recommend not to do here <s> - which is disconnect and reconnect and entity, which you really should rarely have to do in the course of a regular ASP.NET Page or sequence of pages. I suspect you do something like store entities in Session and pass it forward, which is - just like passing Datasets in Session - a bad idea. There are other ways to do this such as reloading entities until you save them and storing the content you are updating in the POST data of the page, rather than carrying partial changes forward through the actual entities.

But that's part of the point as well - there are definitely some scenarios where it would just be a lot easier to keep an entity around and then reconnect to the DataContext but that's just a scenario that's very badly implemented with LINQ to SQL and looks like it will still be awkard in the Entity Framework as well. <shrug>

Rick Strahl
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

@Richard - thanks good points! Heck wasn't that you who told me some time ago about the new() constraint? I think that was before I wrote this code <g> - it's been sitting around for a while.

Hmmm... extending HttpContext? Not sure if I like that idea. I've been kinda weary of extension methods especially when extending framework types and bloating these types.

mike
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Well, what I do is on postback, I get the entity from a service layer, change the properties and pass it on to the service layer to have it updated in the database. In the save method I can't attach the entity. It's difficult for me to really understand why. The classes in my service layer have static methods and a static context property that they use to do their work, might that be my mistake?

Don't worry, I wouldn't just copy-paste your code, but it helps to understand so I don't make a mistake that seems too easy to make with Linq to SQL.

Rick Strahl
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

@Mike - Yup you're using traditional ORM approach <g>. The thing to do is create the DataContext and attach it to your service layer. Then you can load your entity pass it to your ASP.NET code to update the values then tell the service layer use the context to SubmitChanges(). So rather than 'passing it back' you can just update the instance and save it.

Now this works only if your service layer is in fact local. If it is remote (Web Service, Remoting or some other 'external' service) then the above won't work. However, in that case Attach should work with the right flags. If this is what you're using for your business layer though I'd argue Linq to SQL is a bad choice precisely because of the lack of disconnected functionality.

mike
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

I don't understand the difference between my method and the one you describe. In your opinion, is this correct or incorrect given the nature of Linq to SQL?

// Simplified
public class WidgetManager {
private static NWContext context = new NWContext();

public static GetWidgetById(int id) {
return context.SingleOrDefault(w => w.Id == id);
}

public static SaveWidget(Widget widget) {
// This is not right?
// Does the static context make this more difficult?
context.Widgets.Attach(widget);
context.SubmitChanges();

/* What are some options?
Option A: Get the original widget now, from the database.
It could be different than the widget that was requested by the page_load, user was thinking about update, coworker updated same widget in the mean time.

Option B: Get the original widget from the caller as a second parameter
Now the page needs to create a second widget (should it care that Linq to SQL makes that necessary?)
It might keep that original widget from the page_load in session or viewstate, why is that frowned upon?
*/

}
}

Either way, I need to study this more, I find it confusing :(

Matt Brooks
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Rick,

Could you explain to me why you're having problems re-attaching entities to a data context? I've recently used LINQ-to-SQL on a simple ASP.NET E-Commerce solution and found attaching entities to work just fine.

A high level view of the operation:
1. First page load: load entity by PK/ID and data bind. Store ID and VERSION field (e.g. in view state).
2. On post-back (e.g. save): create/instantiate new entity; set PK/ID and VERSION field on entity from the stored values in view state; call context.EntityName.Attach(entity) to associate this new entity with the data context.
3. Update the entity fields/data with values posted back to server.
4. Call context.SubmitChanges();

Here you can see I'm working with the disconnected approach and I haven't tried to hold on to any instances.

I would be interested to hear your thoughts.

Roger Jennings
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

From http://oakleafblog.blogspot.com/2008/02/linq-and-entity-framework-posts-for.html:

"Any .NET developer who has adopted or is considering implementing either LINQ to SQL or Entity Framework should read this well-researched post (and its comments) carefully."

--rj

Mike Thomas
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

I started using LinqToSQL in a Web Application context and quickly ran into the same issues with DataContext life cycle. My solution was almost identical to your thread specific method but I tied the context very tightly to the HttpContext and use a HttpModule to create an DataContext at the beginning of every web request (no reason this couldn't be delay loaded...) it can then be accessed through a static class (Scope.DataContext). For a simple web application (no need to worry about out of thread operations) this has worked out quite well...

Nick
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

One of the key problems with any of these approaches is that the DataContext class won't let you alter the LoadOptions property once it has been set. Once you have used the DataContext to run any sort of query, its DataLoadOptions instance is set in stone. This, in my opinion, was a really stupid decision, and I haven't really been able to find any information about why this was done.

Essentially, this leaves you unable to optimize complex queries across relationships unless you know exactly what you're going to be loading before you ever use the DataContext.

Jon Sagara
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Rick,

After banging my head against the wall a couple of weekends ago, I finally settled on an approach similar to your "Business Objects" scheme. Glad to see someone else is doing it that way, too.

Thanks for the insights.

- Jon

Donnie Hale
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick.

I was the one who pointed out the new() constraint on a previous article of yours.

Re. Richard's fifth point on extension methods - His code checks whether the "this" parameter (context) is null and has some specific behavior if it is. But I wasn't under the impression that you could call an extension method on a null reference. For example, would this work:

((HttpContext) null).GetWebRequestScopedDataContext<MyDataContext>(/*args*/);

Donnie

Egil Hansen
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick

An interesting post once more.

As far as I can tell, Microsoft intents the DataContext to only be used for single or related atomic operations, thus giving it a short lifetime (I have seen that stated by a few MS employees on the MSDN LINQ forums). This is supported by the fact that the DataContext class implements IDisposable, which indicates that it uses resources that should be freed as soon as possible (in this case, a database connection).

In regards to the Attach confusion, I highly recommend reading <a href="http://msdn2.microsoft.com/en-us/library/bb546187.aspx">Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL)</a> from MSDN. That explains how to probably update an existing entity, with or without a TimeStamp field, and provide example code for each possible combination.

Basically, there are three options (I am copy/pasting here):

* Optimistic concurrency based on timestamps or RowVersion numbers.
* Optimistic concurrency based on original values of a subset of entity properties.
* Optimistic concurrency based on the complete original and modified entities.

After reading the above mentioned MSDN article, I have not had any issues with attaching disconnected entities (only been working in a asp.net environment though).

I also use the business wrapper approach in a current project, inspired by your previous article. I am curious though, have you made any changes to your original wwBusinessObject? I ended up building my own business object wrapper (did not like the use of reflection and wanted it to implement IDisposable), but it would be interesting to see how your wwBusinessObject has evolved.

Best regards, Egil.

Egil Hansen
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Looks like my attempt at creating at link was a failure :)

Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL): http://msdn2.microsoft.com/en-us/library/bb546187.aspx

Guru Stop
February 05, 2008

# Link List: jQuery, WF, WCF, OOP and Patterns, ASP.NET, Scrum,...

This is also from an internal mail to SilverKey Tech Egypt office dev. team. Slightly modified this time

Tom Brune
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,

Some things I'd like to add to this discussion.

1. DataContexts are Disposable
-------------------------------
Since we don't really know what connection usage is under the hood of the data context, the context should always be disposed after use.

using (DataContext context = new DataContext())
{
...
}

Note that any streaming type data access (DataReader) will not work after the dispose because the connection will be closed. Moving items into a list will get around this problem.

2. Loading and Saving from Different Contexts
---------------------------------------------
The following is definitely not the most efficient way to do things, but does get around the cross context problem. The following code addresses a scenario of loading an entity from contextA, editing it, and saving it to contextB.

1.1 Create contextA
1.2 Load the entity from contextA
1.3 Close context A (see dispose above)
1.4 Edt the entity somehow (change a property value)
1.5 Create contextB
1.6 Load the item from contextB
1.7 Using reflection copy the properties from the entity from contextA to the entity in contextB
1.8 Save to context B

The extra load from context B is overhead, but it does allow objects to be cross context and therefore "disconnected". Here's some support methods to accomplish this.

public static void Save(TEntityType aEntity)
{
using (DataContext context = GetNewDataContextThatNeedsToBeDisposed())
{
if (aEntity.Id == 0)
context.GetTable<TEntityType>().InsertOnSubmit(aEntity);
else
MoveItemIntoCurrentContext(context, aEntity);

context.SubmitChanges();
}
}

private static void CopyProperties(TEntityType aFrom, TEntityType aTo)
{
// Copy each property to new object in another context.
// LINQ cannot save an object created in one data context
// to another data context
foreach (PropertyInfo property in aFrom.GetType().GetProperties())
if (property.CanWrite)
property.SetValue(aTo, property.GetValue(aFrom, null), null);
}

Though LINQ is flexible, I think placing it anywhere other than a business object seems like a bad idea. Creating manager classes with static methods and placing LINQ code inside those classes encapulates the data access logic where it should be.

I'd be interested to hear opinions on this.

Jenkis
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Regarding Tom Brune's post. I too thought of using the approach of reflection copying every property from the disconnected object to a newly fetched instance, but this approach won't work very well with optimistic concurrency (using a timestamp for instance) as the newly fetched instance will automatically be the latest version and hence won't fail concurrency checking.

I suppose you could add a manual check of the timestamp or version field(s), and throw your own exception?

Tom Brune
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Jenkis, what you are saying is true, however one option to avoid this is to create an interface, say "IContextSwitchable" and use partial classes to indicate those entities include those fields. Then skip those fields on the reflection copy. Just a thought.

jdn
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

I was about to write up a long post about what you call the 'Business Object' option, but your post is much better. Thanks.

jdn

Steve from Pleasant Hill
February 05, 2008

# re: Linq to SQL DataContext Lifetime Management

Maybe I'm just a malcontent, but this whole LINQ topic seems a bit sketchy.

But, I went looking for some basis for this effort, and found this:

In an interview with Redmond Developer News, Hejlsberg made the case that LINQ will ultimately bind data querying into programming much as it was done decades ago with programs such as dBase and FoxPro. "It's my hope that in five to 10 years, programming languages simply will have queries as a concept built in, because that's just a must," Hejlsberg says.

I guess one day VB/C#/SQL Server will just be "one". However, constructing difficult queries that are efficient on large sets of data is not always a no-brainer.

Rick Strahl
February 06, 2008

# re: Linq to SQL DataContext Lifetime Management

@Tom - Although the DataContext is IDisposable it's pretty tough to do this if you have to manage the data context across a business layer or even in a set of operations in a class where code is non-linear.

I understand that we should Dispose() but in my experience so far, releasing the reference is enough to close connections. I've done some load testing a while back and I never saw more than the 5 connections set up in the connection pool and no lock ups due to unavailable connections which would surely occur under high load if there was any problem with hanging connections. I also didn't see any memory increases once the load stabilized.

Keep in mind too that DataContext and Connections aren't tied 1 to 1. Connections will only be open while you iterate over data (or if you explicitly use the Connection provided with a Command or streaming to a DataReader()). Once you reach the end of a list connections close automatically.

Re: Reflection - I played aroudn with this a bit, but this gets really nasty once you start looking at related entities and related collections. The problem is you have to walk the whole tree and child objects and prevent circular references etc. It's doable and possibly generic to do this but it's going to take some serious tweaking to make this fully generic so it's reusable. I think it's actually easier to use DataContractSerializer and serialize/deserialize and then .Attach() again and it may actualy be more efficient at that since you don't have two instances you need to walk through with Reflection. You also have to then figure out how to identify new records and deal with those separately etc. etc. I'm sure it can be done, but you know this is what a data platform is supposed to do FOR YOU. The whole point of a high level tool like LINQ to SQL is to abstract the data and update logistics so that you don't have to think about it, yet with LINQ to SQL you have to build an entire infrastructure on top of the base model just to get what should be basic behavior (for disconnected) that almost every application will need at some point.

Rick Strahl
February 06, 2008

# re: Linq to SQL DataContext Lifetime Management

@Steve - it depends on how you look at it. LINQ to SQL has many very cool features. I think ultimately LINQ to SQL is probably doomed into oblivion in favor of the Entity Framework and hopefully Microsoft will get the message loud and clear that disconnected data is an essential scenario that can't just be glossed over.

But as I've said in many posts before even knowing some of hte limitations the technology is very, very tempting because it reduces the amount of application so significantly. For me at least LINQ and then ability to further manipulate returned data is a huge time saver that solves an age old problem I've never really had a good solution for previously (ie. passing queries back and letting the front end decide to add a final filter, orders and the actual output format). The CRUD functionality too is cool although admittedly you can get that from just about any other framework. The compelling thing about LINQ to SQL is that it's easy to get from data to object with minimal fuss, plus LINQ to query the data.

It'll be interesting to see what other vendors and ORM frameworks will do with LINQ. Ultimately I think other frameworks offer more solid solutions than anything Microsoft ships in the box (nHibernate, Frans' llbgen etc.), but right now there's no LINQ support. I suspect that will change in the future (Frans' been blogging about implementing LINQ support for example). It'll be a standard thing in the future for .NET frameworks and that's a good thing.

But it'll also mean that this is maybe the end of the home built framework because building a custom LINQ Provider (at least correctly) is no picnic.

Richard Deeming
February 06, 2008

# re: Linq to SQL DataContext Lifetime Management

@Donnie - Extension methods are transformed by the compiler to static method calls, so they can work with null references.

For example:
((HttpContext) null).GetWebRequestScopedDataContext<MyDataContext>(/*args*/);


will be translated to:
DataContextFactory.GetWebRequestScopedDataContext<MyDataContext>(null, /*args*/);


However, in practice it would probably be better to always throw an exception if the first parameter is null, otherwise there would be no easy way to determine whether a method invocation would fail on a null reference.

teedyay
February 06, 2008

# re: Linq to SQL DataContext Lifetime Management

Ooh, nice!

We were just putting our LINQ framework/ way-of-working together when this came up.

We do only web-based stuff and had decided it would be generally best (in terms of ease-of-use) to have a DataContext per page, with the option to create more ad hoc for unusual fringe cases.

We like your idea of associating it with the thread (option 3) - that seems a nicer way.

I'm really not convinced by option 4: it seems to be hanging on to the old style of ORM. We think it's worth making the leap to the LINQ model, rather than hacking the new system to make it look like the old one.

LINQ Recipes
February 06, 2008

# Rick Strahl's LINQ to SQL DataContext Lifetime Management

Rick Strahl's LINQ to SQL DataContext Lifetime Management

jdn
February 06, 2008

# re: Linq to SQL DataContext Lifetime Management

Actually, I use something that I call a Business Process model, which is similar to your Business Object model, but which ties together a single DataContext with the set of all related business objects of that process.

http://www.blogcoward.com/archive/2008/02/07/398.aspx

jdn

David Fauber
February 10, 2008

# re: Linq to SQL DataContext Lifetime Management

I've been going with the one datacontext per atomic action approach so far. This has mostly been coding some personal (ie non-work related) code that just has to push "generic" data to a webpage. I may look into the per-business-object approach for larger projects that actually have some business logic. The threading approach frightens me, though.

Dan Lynn
February 11, 2008

# re: Linq to SQL DataContext Lifetime Management

That Thread- and HttpContext-scoped static creation class works great. Thanks so much!

James
February 12, 2008

# re: Linq to SQL DataContext Lifetime Management

Very interesting article and to the point.

I wanted to point out that i used a similar approach to the Request option you provided although it looks more clean. In the current application I'm working on each asp.net page is inherited from a base page class which is in turn inherited from the asp.net page class, so we added a protected property which exposes our DataContext and the property creates the DataContext on the first request. In the Page_Unload we call the dispose method to clean up the DataContext.

This is what the base class looks like:
namespace OurApplication.BaseClasses
{
    public class Page : System.Web.UI.Page
    {
        private OurDataContext _dataContext;
        protected OurDataContext DataContext
        {
            get
            {
                if (_dataContext == null)
                    _dataContext = new OurDataContext(Settings.OurDataSource);
                
                return _dataContext;
            }
        }

        protected void Page_Unload(object sender, EventArgs e)
        {
            if(_dateContext != null)
                _dataContext.Dispose();
        }
    }
}


And this is how we use it:
namespace OurApplication.WebPages
{
    public class Default : OurApplication.BaseClasses.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var news = from n in DataContext.News
                       select n;
        }
    }
}

LJ
February 13, 2008

# re: Linq to SQL DataContext Lifetime Management

From the link above (@egil):

// Attach with "true" to say this is a modified entity
// and it can be checked for optimistic concurrency because
//  it has a column that is marked with "RowVersion" attribute
db.Customers.Attach(customer, true)
try
{
    // Optional: Specify a ConflictMode value
    // in call to SubmitChanges.
    db.SubmitChanges();
}
catch(ChangeConflictException e)
{
    // Handle conflict based on options provided
    // See MSDN article How to: Manage Change Conflicts (LINQ to SQL).
}


So, if you are just updating your object AND you are using RowVersion or a binary timestamp you can add the optional parameter
true
to the DataContext.Attach() method and it will figure out the changes that need to be done, avoiding the overhead of reflection.

Also, from the same article it says <quote>On the middle tier, you then must call the appropriate Attach method and then InsertOnSubmit, DeleteAllOnSubmit, or InsertOnSubmit()()() (without Attach, for insertions) for each entity before you call SubmitChanges. Do not retrieve data from the database as a way to obtain original values before you try updates.
</quote> so I would avoid creating a new context and loading it to get the original values to compare against.

If you have nested objects, like children or chlidren with children, I believe you need to attach each of them to the data context as well, AND indicate what to do with it (delete, update, insert), before calling SubmitChanges() on the data context.

Hope that helps someone!

Tim
February 19, 2008

# re: Linq to SQL DataContext Lifetime Management

The thread specific DataContext certainly works well for ASP.NET. Create the context in begin request and the SubmitChanges in end request. This is how most people are managing the session with nHibernate in my experience. Datacontext is very similar to nHibernate's session, ie track changes then submit (flush in nHibernate).
I looking to migrate some of my nHibernate apps over to linq for the great in line syntax, better than hsql strings.
LINQ to SQL has a lot of similarities to nHibernate. Unfortunately in my opinion it will result in the killing off of nHibernate. All that is required is a wide range of linq providers.

leppie
February 27, 2008

# re: Linq to SQL DataContext Lifetime Management

Here is a HTTP module to deal with DataContext.
public class DataContextModule : IHttpModule
{
  public void Dispose() {}

  public void Init(HttpApplication context)
  {
    context.PreRequestHandlerExecute += new EventHandler(context_PreRequestHandlerExecute);
    context.PostRequestHandlerExecute += new EventHandler(context_PostRequestHandlerExecute);
  }

  void context_PreRequestHandlerExecute(object sender, EventArgs e)
  {
    HttpApplication app = sender as HttpApplication;
    if (app.Context.Handler is Page)
    {
      MyDataContext dc = new MyDataContext();
      app.Context.Items["$context"] = dc;
    }
  }

  void context_PostRequestHandlerExecute(object sender, EventArgs e)
  {
    HttpApplication app = sender as HttpApplication;
    if (app.Context.Handler is Page)
    {
      MyDataContext dc = app.Context.Items["$context"] as MyDataContext;
      dc.SubmitChanges(); //if you want to
      dc.Dispose();
      dc = null;
      app.Context.Items.Remove("$context");
    }
  }
}

Benjamin Eidelman
February 27, 2008

# re: Linq to SQL DataContext Lifetime Management

Rick,

I've following your posts about Linq to SQL, I've seen that you also had the "Attach only if deserialized" problem.
Further testing lead me to discover that an entity loaded from DataContext with Deferred Loading, and Object Tracking disabled, is completely dettached.

If we have this, of course we could use what it's called Linq to SQL "POCO Support", this means that we are not forced to use the auto-generated classes with its EntitySets and EntityRefs, we can use just any plain class with the appropiate attribute decoration.

This allows to create your own disconnected change tracking (there's no Microsoft solution to this problem in current versions, either in the upcoming Entity Framework)

We developed a Code Generator Custom Tool replacement for the default "MSLinqToSQLGenerator", allowing us to take advantage of O/R Designer UI, but customizing the code generation.

We uploaded this tool to CodePlex as GPL: http://www.codeplex.com/ULinqGen, as it could be good jump-start to anyone wishing to use Linq to SQL in an N-Tier application.

We're planning to add some disconnected change tracking features in future releases, but keeping the capability to use your own change tracking implementation. Of course it'll be nice to have you opinion on this.

Regards,

JamesLv
March 10, 2008

# re: Linq to SQL DataContext Lifetime Management

The "thread specific DataContext" solution has a problem, say we will invoke 2 business methods during a request, based on the architecture, they will share the same DataContext stored in the HttpContext. Now method 1 returns a query and method 2 need to apply an LoadOptions and return another query, if you really do this, an exception will come out.
"Setting load options is not allowed after results have been returned from a query."

Rune Gulbrandsen
March 14, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi.

After having a long look at the LINQ to SQL trying to figure out how to use it in a "normal" DAL-scenario, I thought that maybe my approach was wrong.

I guess the MS crew has been looking a lot at what people like Martin Fowler has been talking about as the next abstraction level in programming language, which is exactly the concept of context. Today we create a lot of objects and every time we use them, we need to create a runtime for how we are relating them to each other. This is something we do over and over for each scenario we use the objects. We construct our "object context" over and over. The next logical step is to deliver class frameworks with complete object contexts, so that the end-user just creates a new domain context for the classes and the context is reusable in a lot of applications without any hazzle. You just tweak the context a bit to fit your requirements, and you are ready to go.

Maybe one way to go would be to implement the same context thought to the business layer, and create a business context for all the BOs, which also had a reference to the data context. The data context would then live and die with the business context. Then you could manipulate all the objects in the object context, and when you confirmed the changes in the object context, then the related data context changes would be updated.

It just seems to me to be a better approach to the whole data context concept. Just looked at LINQ to SQL for 3-4 weeks, so I'm no expert.

.Net Unplugged
March 22, 2008

# LINQ to SQL e aplica

LINQ to SQL e aplica

Mohammad Azam
March 31, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,

Thanks for the awesome post! I got one question how would you handle the scenario when your business objects contains both the instance methods and the static methods. Take a look at the following code:

Since, the DataContext is attached to the entity I can access it in the instance methods but then how should I access them in the static methods.

public partial class tblDepartment : BusinessBase
{
public bool Save()
{
if (this.DepartmentID <= 0)
{
DataContext.tblDepartments.InsertOnSubmit(this);
DataContext.SubmitChanges();

if (this.DepartmentID > 0) return true;
else return false;
}

return false;
}

public static tblDepartment GetById(int id)
{

return null;

}

}

Rick Strahl
March 31, 2008

# re: Linq to SQL DataContext Lifetime Management

@Mohammad - why would you have static methods in a business object that needs to access context sensitive data?

Static methods should be - well, static and self contained. usually statics are for utility functionality (things like conversions or manipulation of values) so I'm not sure where using a static method would ever make sense for a business operation.

If you really need static methods then you have to instantiate the business object (or the context only) on its own to reestablish context or using a Factory Pattern.

Mohammad Azam
March 31, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,

Thanks for the fast reply. Actually, I am not using Repositories or Managers for my DAL. LINQ to SQL already creates a DAL for us so I don't think I want to provide any extra layer. Here is my DAL class object.

public partial class tblDepartment : BusinessBase
{
public bool Save()
{
using(VirtualRoomDBDataContext dc = new VirtualRoomDBDataContext())
{
if (this.DepartmentID <= 0)
{
dc.tblDepartments.InsertOnSubmit(this);
dc.SubmitChanges();

if (this.DepartmentID > 0) return true;
else return false;
}

return false;
}
}


public static tblDepartment GetById(int id)
{
VirtualRoomDBDataContext dc = new VirtualRoomDBDataContext();
return dc.tblDepartments.Where(d => d.DepartmentID == id).SingleOrDefault();

}

}

Now, to perform action on a particular instance I can do the following:

tblDepartment department = new tblDepartment();
// assign properties;

department.Save();

If I want to use a method that should be available to all the objects then I would use the static methods like the following:

tblDepartment.GetById(23); // returns the tblDepartment object

For some methods I am disposing the data context right after I am done with it. But with methods like GetById I need to access the childs of the tblDepartment object so I cannot dispose. Off course I can use the DataLoadOptions but then I don't prefer that in my case.

Thanks for you reply Rick!

Konstantin
April 03, 2008

# re: Linq to SQL DataContext Lifetime Management

Rick,

You are saying that DataContext of LINQ to SQL is very different from other ORM's. But is it so different from HNibernate's Session? As I understand the approach is very similar, isn't is so?

Roger
April 25, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi,
I have experimentet with attach/detach of entities, but just seem to be running into problems all the time. Even if objects seems to be detached, I get an exception when relating entities.
I use a somewhat similar solution as yours. I'm developing a ASP.NET application for intranet use and use the asp.net Cache object instead, caching with a key made up from the users identity. This way I make sure there is only one user on one ObjectContext and I don't have to recreate the ObjectContext and reload entities on each postback. The context object(or manager class that contains the context) is removed from the cache if it is idle for 10 minutes, using a callback mehod from the cache object. The Manager class(with the context) is accessible from a base class implemented by my user controls.
Maybe you have some comments about this approach? Some hidden traps?

Simone Belia
May 04, 2008

# ORM e Unit Of Work

Per chi utilizza o sta iniziando ad utilizzare un ORM, la problematica che si riscontra di frequente

Mike Lockyer
May 11, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,

Been reading all your articles on LINQ for SQL, DataContext issues and Tiers and really don't like to have a TimeTrackerContext obect in the code behind page but can't see any better option at the moment

My intention was to start teaching this sort of stuff to undergraduates in September (using ASP.NET) but can't find an easy to understand solution.

Any advice would be most appreciated.

I had built my own solution but just came across a weird behaviour when I had 2 datacontexts.

The first was created and used to get a list of films

then I used another DataContext to update a film

and then used the first DataContext to retrieve the films again - the query was executed but the list of films remained unchanged - odd !!

Once I realised what had happened I expected the SELECT statement NOT to be called a second time as it was presumable caching the results but it does call the SELECT but not use the results - is that weird or just my lack of understanding ?

I have a short code example if anyone wants to see it

Thanks for great articles

Best wishes

Mike

Rick Strahl
May 11, 2008

# re: Linq to SQL DataContext Lifetime Management

@Mike - The per business object approach completely hides the data context from the ASP.NET pages. That's the way I build my applications and that's the way I would recommend working. This ensures proper abstraction of your business logic from the UI layer, plus it gives you a good way to hold on to your data context in a way that's not data centric but business logic centric.

You can find more info and the code for this approach here:
http://www.west-wind.com/WebLog/posts/160237.aspx

Igor
May 14, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick

There is one problem and i could not find a good solution for this.

The problem appears when the transaction of SubmitChanges (or explicit transaction) is rolled back and you expected it and you would like to reuse the DataContext again.

I didn't find any way how to reuse this DataContext (which last operation was rolled back).

Even when the transaction is rolled back - the pending changes of DataContext are actually not rolled back.
And when you try to reuse the DataContext and do any next operations with it and then try to SubmitChanges - the previous changes (that were actually rolled back) are submitted to database again.

Now i don't see any better way than do always something like this:

try
{
op1...
op2...
dbContext.SubmitChanges();
}
catch
{
dbContext = new MyDataContext(....);
}

but this actually makes me think about "Create a new Context for each atomic operation " approach. that would relieve me from such problems.

any thoughts?
or probably there is some solution?

thanks!

RANJI
May 22, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,
I have an unrealted question regarding DataContext. Like you suggested, my team wraps the datacontexts in appropriate business objects but how do you deal with linq to sql classes within your datacontexts when they have to participate in more than one datacontext.
for instance, my user table participates in the SecurityDataContext as well as PurchaseOrderDataContext. So now, I am forced to have 2 different classes for a given database table(user)/ or linq to sql class(user). And then ofcourse, my User business object class.
Where am I going wrong?

Rajinder
July 17, 2008

# re: Linq to SQL DataContext Lifetime Management

I have one problem. I am using static Datacontext Variable. it works fine.. But sometime i got error on website, e.g.
Datareader is already open.. then my website goes down for you say 4-5 minutes.. It works fine again..
Is this due to connection pooling?
Plese suggest me..
Im developing a website like www.telebid.com.
What approch should i use in that?
I mean static object or something else..

Thanks and Regards..

Steven Pack
July 21, 2008

# re: Linq to SQL DataContext Lifetime Management

Great post Rick. Before using the web requested scoped method of your factory, my life was one of ObjectDisposedExceptions and Entity Not Attached errors. Now I'm loving life.

Your code combinded with Richard Deeming's suggestions (to provide a Func method for object construction) works a treat.

Thanks for taking the time to post.

Regards,
Steve Pack

John Debo
August 07, 2008

# re: Linq to SQL DataContext Lifetime Management

Sorry for the naive question..what is the login/pwd to be used for the downloaded code. I am unable to move past the login page :(

Thanks

Viktar Karpach
August 14, 2008

# re: Linq to SQL DataContext Lifetime Management

Great post Rick.

As Rajinder, I used static DataContext in Business Controller. My site is query based, so it should not be a problem, but unfortunately it is. Once a while it shows some weird errors like Datareader is already open or null reference for something deep inside of LINQ.

I started to look for another approach. So, far what I read above there is no good solution.

Before LINQ, I was using Subsonic ActiveRecord and never had any problems. Looks like LINQ is indeed new generation of abstraction languages.

What do you think about Entity Framework?

Omel
August 15, 2008

# re: Linq to SQL DataContext Lifetime Management

TO moderator,

I have some problem about my code behind use vb.net to login file. I thought problem at selected file to db and open the linq db. But i don't know to correctly...pls help me.



Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim connectionString As String = ConfigurationManager.ConnectionStrings("AssignmentConnectionString").ToString()
Dim db As New DataClasses1DataContext(connectionString)

Dim sql As String

Dim Usr As New User

If (StrComp(Usr.UserName, Usr.Psswd, 1) = 0) Then
End If


'Sql = "SELECT * FROM User WHERE UserName = '" & Request("UserName") & "' and Psswd = '" & Request("Password") & "'"
sql = "SELECT [UserName], [Psswd] FROM [User] WHERE (([UserName] = @UserName) AND ([Psswd] = @Password))"

If (Usr.UserName IsNot DBNull.Value) Then
'e.Authenticated = True
lblmsg.Text = "Success"


Else
'e.Authenticated = False
lblmsg.Text = "Pls Try Again"
End If
End Sub

Omel
August 15, 2008

# re: Linq to SQL DataContext Lifetime Management

TO moderator,

I have some problem about my code behind use vb.net to login file. I thought problem at selected file to db and open the linq db. But i don't know to correctly...pls help me.



Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim connectionString As String = ConfigurationManager.ConnectionStrings("AssignmentConnectionString").ToString()
Dim db As New DataClasses1DataContext(connectionString)

Dim sql As String

Dim Usr As New User

If (StrComp(Usr.UserName, Usr.Psswd, 1) = 0) Then
End If


'Sql = "SELECT * FROM User WHERE UserName = '" & Request("UserName") & "' and Psswd = '" & Request("Password") & "'"
sql = "SELECT [UserName], [Psswd] FROM [User] WHERE (([UserName] = @UserName) AND ([Psswd] = @Password))"

If (Usr.UserName IsNot DBNull.Value) Then
'e.Authenticated = True
lblmsg.Text = "Success"


Else
'e.Authenticated = False
lblmsg.Text = "Pls Try Again"
End If
End Sub

Mike Podonyi
August 17, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,

I love this Factory and agree that it is the best to Save the DataContext in the Items Collection (From a Webdeveloper Point Of View).

But stop... Is this really a "Factory". Spoken in Pattern it seems for me more like a "Service Locator".

What do you think?

Thanks
Mike

wilsont
September 09, 2008

# re: Linq to SQL DataContext Lifetime Management

Hi Rick and Igor

I am facing the exact problem as Igor do, reinit(dbContext = new MyDataContext(....);) the datacontext in the catch does work but in my case my datacontext share with all the business object as this is a static object in the parent class of all the business object.

So you can imagine reinit it in the catch function make other business object Table dis-attach from the datacontext too as this is static.

Does anyone have any workaround on this problem? Can I only remove those problematic changes in the datacontext so I do not have to reinit it again?

Mike
September 22, 2008

# re: Linq to SQL DataContext Lifetime Management

Maybe a bit late for this post, but is it a good idea to allow the connectionstring to be specified? What happens here:

GetWebRequestScopedDataContext<MyDataContext>("KEY", "A");
GetWebRequestScopedDataContext<MyDataContext>("KEY", "B");

In fact you get the same context. But you have to know that from reading the implementation or from documentation. Unfortunately, the only method without documentation is in fact the above mentioned method!

Gustave
November 06, 2008

# re: Linq to SQL DataContext Lifetime Management

Thanks for the great article. We where struggling to find out which method, atomic or global, as we where constructing our business objects. The "Create a per Business Object instance of DataContext" bullet totally makes sense and we are probably going that route. As we where where managing the DataContext atomically the re factoring is going to be fast. Thanks again. :)

Rick O'Shay
December 15, 2008

# re: Linq to SQL DataContext Lifetime Management

I would not have listed a global Context as a potential option since creating a *global* anything without considering concurrency issues is a non-starter. In this case it's not worth entertaining, and barely less complicated than "one Context per transaction".

There seem to be two legitimate options. One is creating a context for each transaction in the business class (e.g., a partial class you provide as a companion to the auto-generated entity). The other is to associate the context with a thread. Java has built-in support for thread local variables called (surprise!) thread locals. Hibernate users have been using that for eons. Can't you do the same thing in C#?

[ThreadStatic] SouthernBreezeDataContext context = new SouthernBreezeDataContext( database ); 

Rick Strahl
December 15, 2008

# re: Linq to SQL DataContext Lifetime Management

@Rick - Well but this is specific to Linq to SQL and using per operation Context actually buys you nothing if you hang on to Entities because entities are not updateable across multiple data contexts. So thread specific or in Web apps request specific is probably all that should ever be done.

But that also means that for desktop apps Linq to SQL is probably not a good match, especially if the app might have multiples of the same entity instances in operation at any given point in time that need to be udated. I don't think there's any solution to that scenario with L2S which - well, sucks!

Rick O'Shay
December 16, 2008

# re: Linq to SQL DataContext Lifetime Management

I suppose it should be mentioned that stuffing a Context in the conversation object is a very simple and effective solution, insofar as the state is retained until you dispose of the context.

What I mean by conversation is the popular pattern of using one HTTP session resident object per use-case, versus spraying the session with loose objects. Keeping a Context around in the Conversation (and by extension the HTTP session) works great.

The whole ASP.NET family of technologies seems geared toward small applications with light traffic, and putting a Context in your session conversation fits that mold. Of course, it's not a scalable solution by any stretch of the imagination.

Ahmed's Random Expressions
January 15, 2009

# Adventures with LINQ to SQL

A while back I got the opportunity to use LINQ to SQL (L2S) for an internal web application. What is

Oscar Bautista
April 07, 2009

# re: Linq to SQL DataContext Lifetime Management

Hello. I am hoping you can help. I am developing a tiered website using Linq to Sql. I created a new class(or object) in DBML designer called memberState. This object is not an actual table in the database. I have this method in my middle layer:

public override IEnumerable<memberState> GetMembersByState(string @state)
{
using (BulletinWizardDataContext context = DataContext)
{
IEnumerable<memberState> mems = (from m in context.Members
join ma in context.MemberAddresses
on m.UserId equals ma.UserId
join s in context.States
on ma.StateId equals s.StateId
where s.StateName == @state
select new memberState
{
userId = m.UserID,
firstName = m.FirstName,
middleInitial = m.MiddleInitial,
lastName = m.LastName,
createDate = m.CreateDate,
modifyDate = m.ModifyDate
}).ToArray<memberState>();
return mems;
}
}

The tables in my joins (Members, States, and MemberAddresses are actual tables in my Database). I created the object memberStates so I could use it in the query above (notice the "Select New memberState". When the data is updated on the web page how do I persist the changes back to the Member Table? My Member Table consists of the following columns: UserId, FirstName, MiddleInitial, LastName, CreateDate, ModifyDate. I am not sure how save the changes back to the database.
Thanks,

Eric Swann
April 07, 2009

# re: Linq to SQL DataContext Lifetime Management

Hi Rick, thanks for this article, it helped me a lot. I had a similar issue but came up with a slightly different system. This allows registration of a context and a caching strategy with a Context Provider.
http://ericswann.org/blog/archive/2009/04/06/linq-to-sql-datacontext-provider-revisited.aspx

Gopinath
June 02, 2009

# re: Linq to SQL DataContext Lifetime Management

Hi,
I'm getting the following error when tried to get an instance of data provider


Error 1 'MyDataContext' must be a non-abstract type with a public parameterless constructor in order to use it as parameter 'TDataContext' in the generic type or method 'DataContextFactory.GetWebRequestScopedDataContext<TDataContext>(string)'

The code i used to get instance is


DataContextFactory.GetWebRequestScopedDataContext<MyDataContext>(sConnection);

Can you please tell what is wrong with the my code?

Mateus
June 26, 2009

# re: Linq to SQL DataContext Lifetime Management

Hi there,

Excelent job, hope surff is going alright this summer.

Here is a question thats is breaking my mind.

1 DBML or Multiple DBMLs.

My DB has somewhere close to 150 tables if I create 1 single DBML I will have this huge object, but I will have Unit of Work anywhere I try and so on.
The multiple idea is good, because it breaks the size and gives little manageble DBMLs but it causes other problems like

Agent
AgentFinacialAccounts
FinancialAccounts

Agent is part of the core, so this many to many relationships will happen many times, so lets say we have one DBML with Agent and other related tables, another one will have FinancialAccounts and related tables.
Now AgentFinancialAccounts could leave in the Agents one, or in the FinancialAccounts one, or even in both( I don't like this ).
Anywhere we put it there will be a downside.
Exemple:

I need for every agent created that a financialAccount is created.
So I create the agent, then create the financialAccount, at this point, both have Ids = to 0.
Since AgentFinancialAccounts leave in only one side I can only add a record there after I make a trip to the database and save the changes and get real Ids. If you want to make this process obcure to the user, you have lots of trouble. How are going to tell either the Agent or the FinancialAccount when the other one is done, so you can create the relation record.
The solution in a pratical matter is to have the many-to-many table in both sides, so independently of where things started you can finish it. But then you have redundance of table, and I am almost sure you are going to have troubles ahead.


Hopefully I was clear enough.

Any help will be good...

Thanks

Free Article Directory
July 06, 2009

# re: Linq to SQL DataContext Lifetime Management

Hi All

I want to use DataContext in my Web Application http://www.doyouknow.in

Anyone can suggest which one is a better approach

=> Use as an Application Object ?
=> Use as an Session Object ?
=> Use in every page request ?

Thans in Advance.
Jimit

Rick Strahl
July 06, 2009

# re: Linq to SQL DataContext Lifetime Management

@Jimit - How you use the data context depends on the application, but storing the context in Application or Session is a really bad idea. You shouldn't keep a Context around between requests (Session) and certainly not for multiple users (Application) since the change context will just be a mess if all updates from all users go through a single context.

Personally I use the per business object approach and occasionally for special occasions per unit of work. Per business object (in web applications at least) guarantees you're specific to the current request and still get some persistence and reuse if you use multiple business object methods to retrieve/update data. And if I need a more granular approach I can still use I can still create a new instance of a bus object and use that for unit of work operations. Alternately if you don't use a business layer you can apply these same rules to a global page/request context instance.

.NET Blog
July 08, 2009

# Linq to SQL: A small CRUD application

Linq to SQL: A small CRUD application

Matt H
July 20, 2009

# re: Linq to SQL DataContext Lifetime Management

Great article, and thanks for the DataContextFactory.

Just as a query/suggestion, is there any reason why you wouldn't use a [ThreadStatic] DataContext object, rather than access the object via Thread.GetData()?

Jerome Meyers
July 29, 2009

# re: Linq to SQL DataContext Lifetime Management

Rick,

I've encountered a problem using the Per Business Object bullet you described above. I wonder if you have encountered it as well.

I understand the situation like this: if we are using a single DataContext to manage a Business Object then I notice a couple of ways we can approach the scenario:

1) The business object is a parent entity that we mapped from our database:
Create the DataContext
Load the business object
Set a property on the business object with the DataContext

2) The business object is actually a sort of context of mapped entities and their relationships:
Create the business object
Load the object graph

In either case it seems that we will only go through the trouble if we have ObjectTracking = True because we want to not only LOAD the entities, but also to UPDATE them, or perhaps insert newly related entities and navigate those relations.

The problem arises when we call SubmitChanges and there is some sort of error that rolls back the transaction on the database side. Since we have all this data loaded, and were probably presenting aspects of it in a UI, we want to keep the context around, because if the user wants to try again, they are going to need the original DataContext. But the reality is it is no easy thing to roll back the changes on the DataContext itself. Lets say my user has loaded in an Order and is trying to create a LineItem. There was an error because of quantities in stock. The user wants to go on to another LineItem and call the Customer to get instructions on the failed attempt. They create a new LineItem and click Save but the Context still tries to insert the old LineItem. What to do?

I've tried creating generic methods that "flush" the DataContext, but with only some success. For instance (I know you don't like extension methods, but I like these {if they work}):

[System.Runtime.CompilerServices.Extension()]
public void ClearInserts(DataContext tempDataContext)
{
    // Remove any inserted items.
    foreach (object i in tempDataContext.GetChangeSet.Inserts) {
        tempDataContext.GetTable(i.GetType).DeleteOnSubmit(i);
    }
}

[System.Runtime.CompilerServices.Extension()]
public void ClearDeletes(DataContext tempDataContext)
{
    // Reinsert any deleted Items.
    foreach (object i in tempDataContext.GetChangeSet.Deletes) {
        tempDataContext.GetTable(i.GetType).InsertOnSubmit(i);
    }
}


It looks good, but in fact, doesn't work in practice when the inserted entity has foreign key relationships tagged as NOT NULL.

In my world, an Order's LineItem must be associated with a GarmentType and is so through a FK relationship on GarmentTypeID. So when I create the LineItem I get the appropriate GarmentType from the DataContext and associate the two. Then when I try and save but have failures I remove the association and then "flush" the DataContext (simplified version):

this.LineItem.GarmentType = null;

this.OrderDC.ClearInserts();
this.OrderDC.ClearDeletes();

this.LineItem = null;


What I get, nevertheless, is:

"An attempt was made to remove a relationship between a GarmentType and a LineItem. However, one of the relationship's foreign keys (LineItem.GarmentTypeID) cannot be set to null."

The only way I've found around it, which seems to me to be a real hack, is:

this.LineItem.GarmentType = new GarmentType();


The error occurs not when I am setting the LineItem.GarmentType = null, but rather when the DataContext iterates over its Inserts or Deletes... for if I change the order of ClearInserts/ClearDeletes to ClearDeletes/ClearInserts the error always happens on the first method called on the foreach line.

Is there another way to flush out this DataContext so that it can be used with the loaded Entities. In many cases I've taken to just reloading the entire object graph and refreshing the UI layer so that the user can actually make changes.

Is this the only way?

-Jerome

Rick Strahl
July 29, 2009

# re: Linq to SQL DataContext Lifetime Management

@Jerome - I don't think what you're describing has anything to do with the scope of the data context. These are general issues that you have to deal with with LINQ to SQL's lifetime based context and the only solution to updating data individually and switching update/tracking modes is to use per instance data contexts (pure unit of work). Use and remove immediately after use. The problem with this approach is it has some additional overhead of reloading DataContext for nearly every operation.

I use the business object scope, but I have to keep an eye on how those resources are used explicitly.

As to your updates and the schema - that too is a problem of LINQ to SQL and personally I remove relationships from the model and foreign keys from tables if possible and instead manage them manually. I've found that database foreign key relationships can be painful to get in line with your model especially if you update the model incorrectly (ie. delete parent before children which won't work for example).

I realize this isn't always possible but LINQ to SQL is a limited tool when you're dealing with rigid data structures anyway. <shrug>

Jerome Meyers
July 30, 2009

# re: Linq to SQL DataContext Lifetime Management

Rick,

Well, I understand what I was saying to have to do with the scope of the DataContext because it seems to be a factor that one must consider in the process of determining what usage model to pursue. If we are going keep a single DataContext around for the life cycle of a business object than that DataContext ought to be robust enough to handle the errors that typically pop up in that life cycle. However, it seems to take a bit of ugly hacking around to get that to pan out.

Somewhat off topic, but doesn't removing the relationships from the model and foreign keys from the tables reduce LINQ to SQL to being little but a fussy connection object? I guess it'd be a clean way to execute stored procedures.

Really, what I get out of all this, is that the amount of work-arounds a developer has to make in order to "successfully" use LINQ to SQL technology in a real-world or "close to" real world application seems somewhat prohibitive. I've managed to get it to work in medium sized application, for the most part, but it was no easy thing. None of the books cover the subject in the necessary depth, and up to date information is hard to come by. A lot of the advanced topics addressed on the web seem to be addressed to DLINQ and many times the methods used in the code no longer exist.

-Jerome

Rick Strahl
July 30, 2009

# re: Linq to SQL DataContext Lifetime Management

@Jerome - I agree with you 100%. LINQ to SQL is not an end all solution by any means and you do need to be aware of its shortcomings and either be ready to deal with the workarounds or find something else that works better. FWIW, most ORMs have some issues that are difficult to resolve and complex related entity updates can cause issues in many ORMs and require some custom tweaking.

That said, although I know of the problems that need to be dealt with I've found that in many applications I've worked on these are edge cases that are not hit very frequently and if hit relatively straight forward to work around. Especially if I don't rely on LINQ to SQL to enforce relationships I've been able to make things work with little fuss by manually controlling update orders and such. The thing is that even with these hassles L2S has been a big boon in reducing data access/business logic code dramatically because a solid 80-90% just works easily smoothly. For the remainder if worse comes to worse I can still easily fall back to raw ADO.NET if necessary and I'm none the worse off for it.

Again understand that's my experience and it's not what I would call a glowing recommendation :-}. I critical but pragmatic enough to recognize the benefits as well as the hazards of using this technology.

DevelopMENTAL Madness
July 30, 2009

# LINQ to SQL: Reusing DataContext

LINQ to SQL: Reusing DataContext

OLA
August 02, 2009

# re: Linq to SQL DataContext Lifetime Management

Have you tried using Dependency Injection(DI),to inject your datacontext in a particular context that required it.Take for instance in the following scenario:

 private AlumniDataContext _dataContext;

        public ProfileRepository(AlumniDataContext dataContext)
        {
            this._dataContext = dataContext;
        }


I have been using this in my app and its been excelently.

OLA
August 02, 2009

# re: Linq to SQL DataContext Lifetime Management

Usage:
 /// <summary>
        /// Linq To Sql implementation for MembershipProfile
        /// </summary>
        /// <returns>IQueryable of MembershipProfile</returns>
        public IQueryable<MembershipProfile> SelectAll()
        {
            return (from membershipProfile in _dataContext.Alumni_Memberships
                    join user in _dataContext.aspnet_Users
                    on membershipProfile.UserId equals user.UserId
                    orderby membershipProfile.LastName, membershipProfile.Country ascending
                    select new MembershipProfile
                    {
                        MembershipID = membershipProfile.MembershipID,
                        FirstName = membershipProfile.FirstName,
                        LastName = membershipProfile.LastName,
                        MaidenName = membershipProfile.MaidenName,
                        Gender = membershipProfile.Gender
                       }
                    );
        }

.NET Blog
August 10, 2009

# Linq to SQL: A small CRUD application

Linq to SQL: A small CRUD application

Lukask Blog
September 23, 2009

# LINQ to SQL DataContext Best Practices

LINQ to SQL používá (používalo) docela dost lidí (podle mě). Mezi “uživatele” patřím i já, ale jelikož

Russ
September 29, 2009

# re: Linq to SQL DataContext Lifetime Management

one issue I am having using vb.net 2008 express and linq and vista is I aways have to restart sql server whether 2005 or 2008 express after using an application with linq if I want to see the database objects in sql server management studio - get file is in use; or visa versa if using sql server managment studio to view a database the linq code gives windows user does not have permission. starting and stopping sql server temporarily fixes the issue. What is the deal with this?

Yami
October 14, 2009

# re: Linq to SQL DataContext Lifetime Management

With respect to the per business object approach how does this approach work when passing data between boundaries such as web services? Is L2S a good tool if you need to pass data using a web service, make changes, and then update?

Maxi
November 02, 2009

# re: Linq to SQL DataContext Lifetime Management

Dear Rick,

I have read a lot you articles and they help me a lot.
This article gives me a lot insight on ways that I can hang on to my data context.

But I am dealing with a web server farm, distributed cache structure.
I may have data which being cached from another web server and deserialize back in another web server. I think hanging on by thread or by business object would work, or is my understanding incorrect?

What should be done if I am developing in such multi-tiers structure?

Rick Strahl
November 03, 2009

# re: Linq to SQL DataContext Lifetime Management

@Maxi - simple: don't cache the DataContext across requests. There should never be a reason to hang to a context across requests so there's no issue for Web farms.

Daniel Edstrom
December 08, 2009

# re: Linq to SQL DataContext Lifetime Management

Should it be this complicated to use LINQ?

Elliot L
February 17, 2010

# re: Linq to SQL DataContext Lifetime Management

Hi Rick,

A very informative post - I'm the Lead Developer at AssemblyPoint. We recently migrated our application to Windows Azure. The application depended upon a DataContext object cached in session state - which isn't an option in Azure. We considered rewriting the application to not use a cached DataContext object and decided that it would involve too much work. Hence, we wrote a library that could setup a new DataContext correctly based on the modified vs. original entity.

I've referenced your article in my blog.

Thanks,

Elliot L.

M Shafqat
March 08, 2010

# Linq to SQL DataContext Lifetime Management

Good Explanation

SEO West Palm Beach
May 24, 2010

# SEO West Palm Beach

There a many different ways to look at this. But I think you have some very valid points here.

Jason Butera
March 10, 2011

# re: Linq to SQL DataContext Lifetime Management

Rick,

How would you suggest implementing this factory in a WCF service? When I use your factory code in an ASP.net website or winform app, it works flawlessly. However, I get a lot weirdness in a WCF service even when I use the InstanceContextMode.PerCall behavior. Errors like "The Database generated a key that is already in use" when I'm adding a record in an autonumbered table with two separate wcf calls from the same console app. Basically, I hit the service to insert a record, then I deleted the records in the database and reseeded the table, then hit the service again. I know what the message means, however, I thought the PerCall contextmode would not have pulled the lastly used datacontext from the AllocateNamedDataSlot.

I'm reusing a data assembly with your datacontextfactory implemented. It's safe for my only in my WCF service to always use a new instance of the datacontext. How could I always return a new datacontext by modifying your code when it's being called by a WCF service? Checking for HttpContext tells me web, what tells me WCF?

Sascha Holl
May 15, 2011

# re: Linq to SQL DataContext Lifetime Management

I've come across to your blog while trying to solve a similar problem. I've created a more generic solution and wanted to share with other people hanging on the same issue. Let me know if it was helpful.

public enum InstantiationMode
{
    PerClass,
    PerConstructor
}
 
 
public delegate T Create<T>();
public delegate T Create<P0, T>(P0 p0);
// public delegate T Create<P0 .. P8, T>(P0 p0 .. P8 p8);
public delegate T Create<P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, T>(P0 p0, P1 p1, P2 p2, P3 p3, P4 p4, P5 p5, P6 p6, P7 p7, P8 p8, P9 p9);
 
 
public static class ObjectFactory
{
    static ObjectFactory()
    {
        InstantiationMode = InstantiationMode.PerClass;
    }
 
 
    public static InstantiationMode InstantiationMode { get; set; }
 
 
    public static T GetHttpContextScopedObject<T>()
        where T : new()
    {
        var key = BuildObjectUniqueKey<T>();
        var obj = TryReadHttpContextScopedObject(key);
 
        if (obj == null)
        {
            obj = new T();
            StoreHttpContextScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
 
    public static T GetHttpContextScopedObject<T>(Create<T> create)
    {
        Contract.Requires(create != null);
 
        var key = BuildObjectUniqueKey<T>();
        var obj = TryReadHttpContextScopedObject(key);
 
        if (obj == null)
        {
            obj = create();
            StoreHttpContextScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
    public static T GetHttpContextScopedObject<P0, T>(Create<P0, T> create, P0 p0)
    {
        Contract.Requires(create != null);
 
        var key = BuildObjectUniqueKey<T>(new object[] { p0 });
        var obj = TryReadHttpContextScopedObject(key);
 
        if (obj == null)
        {
            obj = create(p0);
            StoreHttpContextScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
    // public static T GetHttpContextScopedObject<P0, T>(Create<P0 .. P8, T> create, P0 p0 .. P8 p8)
 
    public static T GetHttpContextScopedObject<P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, T>(Create<P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, T> create, P0 p0, P1 p1, P2 p2, P3 p3, P4 p4, P5 p5, P6 p6, P7 p7, P8 p8, P9 p9)
    {
        Contract.Requires(create != null);
 
        var key = BuildObjectUniqueKey<T>(new object[] { p0, p1, p2, p3, p4, p5, p6, p7, p8, p9 });
        var obj = TryReadHttpContextScopedObject(key);
 
        if (obj == null)
        {
            obj = create(p0, p1, p2, p3, p4, p5, p6, p7, p8, p9);
            StoreHttpContextScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
 
    public static T GetHttpContextScopedObject<T>(params object[] args)
    {
        Contract.Requires(args != null);
            
        var key = BuildObjectUniqueKey<T>(args);
        var obj = TryReadHttpContextScopedObject(key);
 
        if (obj == null)
        {
            obj = Activator.CreateInstance(typeof(T), args);
            StoreHttpContextScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
 
    public static T GetThreadScopedObject<T>()
        where T : new()
    {
        var key = BuildObjectUniqueKey<T>();
        var obj = TryReadThreadScopedObject(key);
 
        if (obj == null)
        {
            obj = new T();
            StoreThreadScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
 
    public static T GetThreadScopedObject<T>(Create<T> create)
    {
        Contract.Requires(create != null);
 
        var key = BuildObjectUniqueKey<T>();
        var obj = TryReadThreadScopedObject(key);
 
        if (obj == null)
        {
            obj = create();
            StoreThreadScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
    public static T GetThreadScopedObject<P0, T>(Create<P0, T> create, P0 p0)
    {
        Contract.Requires(create != null);
 
        var key = BuildObjectUniqueKey<T>(new object[] { p0 });
        var obj = TryReadThreadScopedObject(key);
 
        if (obj == null)
        {
            obj = create(p0);
            StoreThreadScopedObject(key, obj);
        }
 
        return (T)obj;
    }
    
    // public static T GetThreadScopedObject<P0, T>(Create<P0 .. P8, T> create, P0 p0 .. P8 p8)
 
    public static T GetThreadScopedObject<P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, T>(Create<P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, T> create, P0 p0, P1 p1, P2 p2, P3 p3, P4 p4, P5 p5, P6 p6, P7 p7, P8 p8, P9 p9)
    {
        Contract.Requires(create != null);
 
        var key = BuildObjectUniqueKey<T>(new object[] { p0, p1, p2, p3, p4, p5, p6, p7, p8, p9 });
        var obj = TryReadThreadScopedObject(key);
 
        if (obj == null)
        {
            obj = create(p0, p1, p2, p3, p4, p5, p6, p7, p8, p9);
            StoreThreadScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
 
    public static T GetThreadScopedObject<T>(params object[] args)
    {
        Contract.Requires(args != null);
            
        var key = BuildObjectUniqueKey<T>(args);
        var obj = TryReadThreadScopedObject(key);
 
        if (obj == null)
        {
            obj = Activator.CreateInstance(typeof(T), args);
            StoreThreadScopedObject(key, obj);
        }
 
        return (T)obj;
    }
 
 
    private static object ReadThreadScopedObject(object key)
    {
        Contract.Requires(key != null);
 
        var obj = TryReadThreadScopedObject(key);
        if (obj == null)
            throw new Exception();
        return obj;
    }
 
    private static object TryReadThreadScopedObject(object key)
    {
        Contract.Requires(key != null);
 
        var slot = Thread.GetNamedDataSlot(key.ToString());
        if (slot != null)
            return Thread.GetData(slot);
        return null;
    }
 
    private static void StoreThreadScopedObject(object key, object obj)
    {
        Contract.Requires(key != null);
        Contract.Requires(obj != null);
 
        if (!TryStoreThreadScopedObject(key, obj))
            throw new Exception();
    }
 
    private static bool TryStoreThreadScopedObject(object key, object obj)
    {
        Contract.Requires(key != null);
        Contract.Requires(obj != null);
 
        if (TryReadThreadScopedObject(key) != null)
            return false;
 
        var slot = Thread.AllocateNamedDataSlot(key.ToString());
        Thread.SetData(slot, obj);
        return true;
    }
 
 
    private static object ReadHttpContextScopedObject(object key)
    {
        Contract.Requires(key != null);
 
        var obj = TryReadHttpContextScopedObject(key);
        if (obj == null)
            throw new Exception();
        return obj;
    }
 
    private static object TryReadHttpContextScopedObject(object key)
    {
        Contract.Requires(key != null);
        return HttpContext.Current.Items[key];
    }
 
    private static void StoreHttpContextScopedObject(object key, object obj)
    {
        Contract.Requires(key != null);
        Contract.Requires(obj != null);
 
        if (!TryStoreHttpContextScopedObject(key, obj))
            throw new Exception();
    }
 
    private static bool TryStoreHttpContextScopedObject(object key, object obj)
    {
        Contract.Requires(key != null);
        Contract.Requires(obj != null);
 
        if (TryReadHttpContextScopedObject(key) != null)
            return false;
 
        HttpContext.Current.Items[key] = obj;
        return true;
    }
 
 
    private static object BuildObjectUniqueKey<T>(params object[] args)
    {
        Contract.Requires(args != null);
 
        string prefix = "__SSDOF__";
        switch (InstantiationMode)
        {
            case InstantiationMode.PerClass:
                return prefix + typeof(T).AssemblyQualifiedName;
            case InstantiationMode.PerConstructor:
            default:
                var sb = new StringBuilder(prefix + typeof(T).AssemblyQualifiedName);
                for (int i = 0; i < args.Length; ++i)
                    sb.Append(args.GetType().AssemblyQualifiedName);
                return sb.ToString();
        }
    }
}

Rick Strahl
October 09, 2011

# re: Linq to SQL DataContext Lifetime Management

@Sasha - great idea. Going to integrate something like this into my own library!

Carlos Ble
February 10, 2012

# re: Linq to SQL DataContext Lifetime Management

Thanks for your great post Rick! We are using your knowledge and improving part of the code. http://www.carlosble.com/2012/02/linq-to-sql-horrors/

Okan SARICA
April 22, 2013

# re: Linq to SQL DataContext Lifetime Management

Hi
these context creation is very usefull i think this code not working on trust level medium

i am using a public hosting so this is a restriction for me and i must obey this trust level rule

what do you suggest than ?

Rick Strahl
April 22, 2013

# re: Linq to SQL DataContext Lifetime Management

@Okan - you should still be able to use HttpContext activation in medium trust. I believe only the thread based activation shouldn't work. Also make sure your context is public - if it's internal it won't work because private reflection is not available in medium trust.

Dave
May 20, 2013

# re: Linq to SQL DataContext Lifetime Management

Hi
We implemented this functionality into our visual studio solution a while back and have only just noticed that something is not working. Our project uses LINQ to get the info from the database then assigns the properties to public properties within our POCO classes. We can then modify the POCO class properties within our WinForms, then to save the data to the database we re-assign the public properties of the POCO classes back to the LINQ classes to update the database using LINQ. This all works fine however if we have the exact same setup on another computer (computer 2) and click the refresh button on a form on computer 2 (after updating the database on the form on computer 1) the results are not refreshed on the grid on computer 2. The data is somehow being cached. I have an c-sharp example project with one LINQ Class, one POCO class and a database with one table in it which shows this if it is possible to upload it somewhere where someone could perhaps have a look at this or give some advice on this?
Many thanks
Dave

old_guy
July 12, 2020

# re: Linq to SQL DataContext Lifetime Management

so here it is during COVID-2020, and i find myself reading an article about Linq2Sql...and find myself on a project where they said..." turn our Access database into a SQL one and use Linq2Sql"... lol

So, i jumped into my Einstein time-machine back to 2003 to deal with Jet, while surfing my way upto 2010 and ACE drivers.. finally, landing firmly back into 2008 to deal with Linq2Sql and... ya, that DataContext is a bugger.

After having worked with EFCore since dotnet core RC 1.0, I find this experience brutally painful.

Getting a simple factory working with a repository interface has proven to be challenge worth failing against... but you can only do so much with this older technology where a concrete class like one made with SQLMetal is a herculean monolith of dumpster fires.


Doozy Tools
October 07, 2022

# re: Linq to SQL DataContext Lifetime Management

Seeing this post again after 14 years as I came across my comment in 2009 in this article. Look like world is moved far from here but concepts are still same with new terminology. We are still working with .NET and our recent online free tool site Doozy Tools is still using LINQ and DataContext. We are more reliant on JavaScript which is now able to process PDF files without uploading it.


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024