Entity Framework use CompiledQuery but allow runtime filter values

Question

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 DataLayer
{
    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?

Ladislav Mrnka · Answer 1 · 2011-05-12T09:08:26.633

0

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));

edited May 12 '11 at 09:08

answered May 12 '11 at 08:21

Ladislav Mrnka

360,892
59
660
670

LINQ to Entities doesn't like it :(. "...does not recognize the method Filter1 method, and this method cannot be translated into a store expression.". – sipsorcery May 12 '11 at 09:01
That would be the ideal way but unfortunately in this case the filters are already defined in a separate class and there are lots of them that need to be re-used in different queries. The approach worked with L2S but not so well with EF as the queries now need to be compiled to make the performance acceptable. – sipsorcery May 12 '11 at 09:30
Can you just test if extension method works if you do not compile the query? – Ladislav Mrnka May 12 '11 at 10:39
Yes the extension method works fine if the query is not compiled. – sipsorcery May 12 '11 at 11:01
I'm somehow afraid that compiled queries do not support reusable blocks if they don't support extension methods which work with non compiled queries. – Ladislav Mrnka May 12 '11 at 11:32

score 0 · Accepted Answer · answered May 13 '11 at 03:45

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.

Entity Framework use CompiledQuery but allow runtime filter values

2 Answers2