Entity Framework use CompiledQuery but allow runtime filter values

I'm attempting to refactor a cumbersome LINQ-to-SQL data layer with an entity framework one. The database schema behind the model is big and a typical query might have 20 to 30 Includes. The EF generates massive SQL statements for such queries, the biggest so far has been 4k lines, but they still execute in a timely fashion so that's not a problem.

The problem is that the EF takes a long time, up to 4 or 5 seconds to generate the query. To overcome that I've used CompileQuery. The problem then is that the existing L2S datalayer has a lot of filters that can be applied to a query depending on user input. A single value in those filters needs to be set at runtime.

The code below doesn't work because the initial static values get compiled into the query but it demonstrates what I'm attempting to do.

public static class DataLay开发者_C百科er
{
    static Func<MyEntities, int, IQueryable<Prescription>> compiledQuery;

    static int? FilterHpID;
    static Expression<Func<Prescription, bool>> filter1 = x => (FilterHpID == null || x.Prescriber.HPID == FilterHpID);

    static DateTime? FilterDateTime;
    static Expression<Func<Prescription, bool>> filter2 = x => (FilterDateTime == null || x.DateTimeDispensed > FilterDateTime);

    public static List<Prescription> Get(int patientID, int? hpID, DateTime? dispensed)
    {
        FilterHpID = hpID;
        FilterDateTime = dispensed;

        if (compiledQuery == null)
        {
            compiledQuery = System.Data.Objects.CompiledQuery.Compile((MyEntities entities, int id) =>
                        (from pre in entities.Prescription
                         where pre.PatientID == id
                         select pre)
                         .Where(filter1)
                         .Where(filter2));
        }

        using (MyEntities entities = new MyEntities())
        {
            return compiledQuery(entities, patientID).ToList();
        }
    }
}

Is there any way I can include my filter expressions in the compiled query AND be able to set values on the filter expressions when executing the query?

Filters must be part of the compiled query and after that you can set them when calling the query. I think you can use something like:

public static IQueryable<Prescription> Filter1(this IQueryale<Prescription> query, 
    DateTime? param)
{
    return query.Where(x => (param == null || x.Prescriber.HPID == param));
}

Then you should be able to define your compiled query as:

 compiledQuery = System.Data.Objects.CompiledQuery
                       .Compile((MyEntities entities, int id, DateTime? param) =>
                           (from pre in entities.Prescription
                            where pre.PatientID == id
                            select pre)
                           .Filter1(param));

It works with normal queries but I have never tried it in compiled queries. If it doesn't work you must place filter expression directly in the compiled query:

 compiledQuery = System.Data.Objects.CompiledQuery
                       .Compile((MyEntities entities, int id, DateTime? param) =>
                           (from pre in entities.Prescription
                            where pre.PatientID == id
                            select pre)
                           .Where(x => (param == null || x.Prescriber.HPID == param));

After searching high and low the conclusion I have come to is there is no way to be able to employ reusable expressions in a compiled query let alone expressions requiring a parameter.

The intent of the code below is not possible in any shape or manner.

static Expression<Func<Prescription, bool>> filter1 = x => (FilterHpID == null || x.Prescriber.HPID == 1);

compiledQuery = System.Data.Objects.CompiledQuery.Compile((MyEntities entities, int id) =>
                    (from pre in entities.Prescription
                     where pre.PatientID == id
                     select pre)
                     .Where(filter1));

We're going to go off and look at using database views as a way around the need to use compiled queries in the first place. If the L2E queries don't have to be compiled to avoid taking a nearly 2 second delay then reusable expression filters can be added.