refactor LINQ TO SQL custom properties that instantiate datacontext

Question

I am working on an existing ASP.NET MVC app that started small and has grown with time to require a good re-architecture and refactoring.

One thing that I am struggling with is that we've got partial classes of the L2S entities so we could add some extra properties, but these props create a new data context and query the DB for a subset of data. This would be the equivalent to doing the following in SQL, which is not a very good way to write this query as oppsed to joins:

SELECT tbl1.stuff, 
    (SELECT nestedValue FROM tbl2 WHERE tbl2.Foo = tbl1.Bar),
    tbl1.moreStuff
 FROM tbl1

so in short here's what we've got in some of our partial entity classes:

public partial class Ticket {

public StatusUpdate LastStatusUpdate
{
    get
    {
         //this static method call returns a new DataContext but needs to be refactored
         var ctx = OurDataContext.GetContext();
         var su = Compiled_Query_GetLastUpdate(ctx, this.TicketId);
         return su;
    }
}

}

We've got some functions that create a compiled query, but the issue is that we also have some DataLoadOptions defined in the DataContext, and because we instantiate a new datacontext for getting these nested property, we get an exception

"Compiled Queries across DataContexts with different LoadOptions not supported"

. The first DataContext is coming from a DataContextFactory that we implemented with the refactorings, but this second one is just hanging off the entity property getter.

We're implementing the Repository pattern in the refactoring process, so we must stop doing stuff like the above. Does anyone know of a good way to address this issue?

EDIT: I am adding the code for the DataContextFactory that we created during our refactoring efforts. Note that in the code above we have a GetContext() static method in the Linq DataContext class, which news up a datacontext, and is not using the DataContextFactory below:

public class DataContextFactory<T> : IDataContextFactory where T : System.Data.Linq.DataContext
{
    public DataContextFactory()
    {
        _context = Activator.CreateInstance<T>();
    }

    public DataContextFactory(T context)
    {
        _context = context;
    }

    private System.Data.Linq.DataContext _context;
    public System.Data.Linq.DataContext Context
    {
        get 
        {
            return _context; 
        }
    }

    public void SaveAll()
    {
        Context.SubmitChanges();
    }

    public bool IsContextDirty
    {
        get
        {
            return Context != null && (Context.GetChangeSet().Deletes.Count > 0 ||
                                           Context.GetChangeSet().Inserts.Count > 0 ||
                                           Context.GetChangeSet().Updates.Count > 0);
        }
    }
}

Answer 1

Instead of creating the DataContext whenever the method is called, store it in HttpContext.Current.Items.

This will achieve the same effect as storing it in a private field, as it will be unique to the request.