Entity Framework query timeout but SQL is instant

Question

I have a couple of expressions that I run a Count() against in EF6. The generated SQL query winds up a somewhat complex one hitting a few tables with a few parameters passed in. However it runs in less than a second if I copy the SQL into SSMS.

In EF the Linq Query takes over 30 seconds and often simply throws a Connection Timeout exception.

Looking at the activity monitor I can see it appears as if the same query is being ran many thousands (if not millions) of times.

The code triggering the queries is made up of a couple of expressions that are combined using LinqKit AsExpandable() and Invoke().

//properties is an IQueryable<Property> and checkDate a DateTime
int propertyCount = FilterCompliantOnDate(properties, checkDate).Count();



public IQueryable<Property> FilterCompliantOnDate(IQueryable<Property> properties, DateTime checkDate)
{
    // SelectedComplianceCategory is a local property (int?)
    return properties.AsExpandable().Where(p=>PropertyIsCompliant.Invoke(p, checkDate, SelectedComplianceCategory));
}



public static readonly Expression<Func<Property, DateTime, int?, bool>> PropertyIsCompliant = (p, checkDate, complianceCategory) =>
    CategoryComplianceRatings.Invoke(p, complianceCategory, checkDate).Any() &&
    CategoryComplianceRatings.Invoke(p, complianceCategory, checkDate)
        .All(cr => cr.ComplianceRating == ComplianceRating.Compliant);



private static readonly Expression<Func<Property, int?, DateTime, IQueryable<PropertyComplianceRating>>> CategoryComplianceRatings =
    (p, categoryId, checkTime) => p.ComplianceRatings.AsQueryable()
    .Where(cr =>
        cr.ComplianceCategory != null &&
        (
            categoryId == null ||
            (categoryId != null && cr.ComplianceCategory.Id == categoryId)
        )
    )
    .GroupBy(cr => cr.ComplianceCategory)
    .Select(g => g
    .Where(cr => cr.Date < checkTime)
    .OrderByDescending(cr => cr.Date)
    .FirstOrDefault()
);

Answer 1

Maybe your query is tacking all references, try in this way with AsNoTracking

database.Table.AsNoTracking().Where(e=>other code)

update

If it doesn't work try moving the 'AsQueryable()' after the Where part and before the GroupBy part.

Answer 2

Check the session settings (arithabort, ansi_nulls...) when you execute from the different environments. Make sure they are all the same. Many times, different settings will result in the same procedure executing with completely different execution durations.