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?