How to use a precompiled Linq query

Question

Making my first attempt at precompiling a linq query and it looks like I'm doing something wrong.

I want to use the precompiled query in a validator query that will wind up being called around 14k times per use. I want to instantiate the query in the constructor (again, first time using these so not sure if this is the proper use) so the class object will be able to call the compiled query instead.

Here is the Func as a property and instantiated in the constructor

    private static Func<DataContext, ZipCodeTerritory, IQueryable<ZipCodeTerritory>> SearchEffectiveDate;
    public ZipCodeValidatorCompiled()
    {
        SearchEffectiveDate = CompiledQuery.Compile((DataContext db, ZipCodeTerritory zipCode)
        =>
        from z in db.GetTable<ZipCodeTerritory>()
        where z.DrmTerrDesc.Equals(zipCode.DrmTerrDesc) &&
              z.IndDistrnId.Equals(zipCode.IndDistrnId) &&
              z.StateCode.Equals(zipCode.StateCode) &&
              (z.ZipCode.Equals(null) ? z.ZipCode.Equals(null) : z.ZipCode.Equals(zipCode.ZipCode))
        select z
        );
    }

And here is how it is called in one of the validation methods

    private static string ValidateEffectiveDate(ZipCodeTerritory zipCode)
    {
        using (var _db = new AgentResources())
        {
            IQueryable<ZipCodeTerritory> terrList = SearchEffectiveDate(_db, zipCode);

            foreach (var zip in terrList)
            {
                if (zip.EffectiveDate >= zipCode.EndDate)
                {
                    return
                        "End Date must be greater than Effective Date of any other record sharing the same DRM Territory Description, Territory, State and Zip Code; ";
                }
            }                
        }

        return null;
    }

The issue I'm running into is with this statement in the validation method

IQueryable<ZipCodeTerritory> terrList = SearchEffectiveDate(_db, zipCode);

Visual Studio SearchEffectiveDate(_db, zipCode) is giving me the following errors:

Error 20 Delegate 'System.Func>' has some invalid arguments
Error 21 Argument 1: cannot convert from 'Monet.Models.AgentResources' to 'System.Data.Linq.DataContext'

I am using this tutorial on speeding up Linq queries.

FYI, The first argument of the query doesn't necessarily have to be defined to be `DataContext`, it can be derived from it. So you can use the data context generated for you in its place so you can have stronger typing in your queries. — Jeff Mercado, Jan 08 '14 at 23:52
Not directly. EF's `DbContext` doesn't have direct support for compiled queries. But you can get an `ObjectContext` from it which _can_ be used in compiled queries. Cast it to `IObjectContextAdapter` and get the `ObjectContext` to create your query. I'll see about crafting an answer to your question, it should be doable, with a few adjustments. — Jeff Mercado, Jan 09 '14 at 19:56
On second thought, I guess it isn't really possible through EF. But it seems like it wouldn't be needed in EF5. http://stackoverflow.com/questions/9739925/entity-framework-compiled-query — Jeff Mercado, Jan 09 '14 at 20:30

score 0 · Answer 1 · answered Jan 08 '14 at 22:38

The problem here was that I wasn't using a DataContext object, but instead my DbContext object of type AgentResoources. By adding a static DataContext object to the class as a parameter and instantiating it in the constructor, using a standard connection string as you would for a SqlConnection ojbect, I was able to make the code compile.

Parameters/Constructor

    private static Func<DataContext, ZipCodeTerritory, IQueryable<ZipCodeTerritory>> SearchEffectiveDate;
    private static DataContext _dbContext;
    public ZipCodeValidatorCompiled()
    {
        _dbContext = new DataContext(ConfigurationManager.AppSettings.Get("db"));
        SearchEffectiveDate = CompiledQuery.Compile((DataContext db, ZipCodeTerritory zipCode)
        =>
        from z in db.GetTable<ZipCodeTerritory>()
        where z.DrmTerrDesc.Equals(zipCode.DrmTerrDesc) &&
              z.IndDistrnId.Equals(zipCode.IndDistrnId) &&
              z.StateCode.Equals(zipCode.StateCode) &&
              (z.ZipCode.Equals(null) ? z.ZipCode.Equals(null) : z.ZipCode.Equals(zipCode.ZipCode))
        select z
        );
    }

Method

IQueryable<ZipCodeTerritory> terrList = SearchEffectiveDate(_dbContext, zipCode);
foreach (var zip in terrList)
{
    if (zip.EffectiveDate >= zipCode.EndDate)
    {
        return
            "End Date must be greater than Effective Date of any other record sharing the same DRM Territory Description, Territory, State and Zip Code; ";
    }
}

How to use a precompiled Linq query

1 Answers1