Compiled LINQ query & DataLoadOptions... with a twist!

Question

I know about the method discussed here:

Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

... but this doesn't work for my situation as i get a :

"Setting load options is not allowed after results have been returned from a query."

I am using Codesmith PLINQO scripts to generate entities and manager code, and the manager code looks something like this:

public partial class SearchManager
{       
    #region Query
    // A private class for lazy loading static compiled queries.
    private static partial class Query
    {
        internal static readonly Func<MyDataContext,IOrderedQueryable<Search>> 
            GetAll = CompiledQuery.Compile(
                (MyDataContext db) =>
                from s in db.Search
                orderby s.Name
                select s);
    } 
    #endregion


    public IQueryable<Search> GetAll()
    {
        return Query.GetAll(Context);
    }
}

I first tried dropping a static DataLoadOptions into the Searchmanager class like this:

public static readonly DataLoadOptions MyOptions = 
    (new Func<DataLoadOptions>(() =>
    {
        var option = new DataLoadOptions();
        option.LoadWith<Search>(x => x.Rule);
        return option;
    }))();

... then providing it to the Context in the GetAll method like:

public IQueryable<Search> GetAll()
{
    Context.LoadOptions = MyOptions;
    return Query.GetAll(Context);
}

...and that gave me the error i noted above. Is this because the query is already compiled, and thus can't have "extra" DataLoadOptions's added? If so, how would it be possible to apply the DataLoadOptions prior to the the query being compiled?

Answer 1

In the setter property of the DataContext class, there is a condition that checks if the DataContext has any objects in its Cache, and the LoadOptions is NOT null, and the LoadOptions instance you are trying to set is not the same as the one already set, then you get that exception.

Alternative #1. Create a new Context for each query (probably not a good idea)
Alternative #2. Call the ClearCache method using reflection, then make a new LoadOptions statically, assign it to the Context, then finally, get the compiled query.

public partial class SearchManager
{       
    #region Query
    // A private class for lazy loading static compiled queries.
    private static partial class Query
    {
        internal static readonly Func<MyDataContext,IOrderedQueryable<Search>> GetAll 
        {
            get {
                return CompiledQuery.Compile(
                    (MyDataContext db) =>
                        from s in db.Search
                        orderby s.Name
                        select s);
            }
        } 
    #endregion

    public IQueryable<Search> GetAll()
    {
        Context.ClearCache();
        Context.LoadOptions = MyOptions;
        return Query.GetAll(Context);
    }

    public static readonly DataLoadOptions MyOptions = 
        (new Func<DataLoadOptions>(() => MakeLoadOptions<Search>(x=>x.Rule)))();
}

public static class Extensions {
    public static void ClearCache(this DataContext context)
    {
        const BindingFlags FLAGS = BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic;
        var method = context.GetType().GetMethod("ClearCache", FLAGS);
        method.Invoke(context, null);
    }

    public static DataLoadOptions MakeLoadOptions<TEntity, TResult>(this Expression<Func<TEntity,TResult>> func) {
        DataLoadOptions options = new DataLoadOptions();
        options.LoadWith(func);
        return options;
    }
}

Answer 2

You can only set the load options once for a compiled query. The error must be getting thrown on the second call. Move the assignment to a static constructor and that should solve your problem.

Answer 3

The error message itself tells you exactly what is wrong. You cannot apply DataLoadOptions after a Linq query has returned results. Or, maybe a better way to say this is as follows. If you want to apply DataLoadOptions, do so before executing the query. You cannot do so afterwards.

Answer 4

public IQueryable<Search> GetAll() {
     Context.LoadOptions = MyOptions;
     return Query.GetAll(Context);
 } 

This assignment is too late, if the Context has already returned any query results. This has nothing to do with compiled queries and everything to do with assigning the LoadOptions property of DataContext. Unfortunately, this behavior of the LoadOptions property is not documented on msdn.