LINQ Query times out on server but instantly executes on local

Question

I've asked this question before and got down-voted because I didn't provide code. But I strongly believe my problem is not code related and I am very desperate to solve this problem.

Basically, I need to get counts of 2 different things from a LINQ query, the query is being built up and is pretty long.

The weird thing is: when the select LINQ query is executed on the database server, it times out on the 2nd count (no matter how long I set the CommandTimeout to), but when I grabbed the same database instance from the database server and put it on my local then execute the LINQ query it gets both counts instantly.

I SQL Profiler the translated SQL query on both the database server and the local server and they are the same. When translated SQL query that's generated from my local is ran on the database server's SQL Management Studio manually, it also returns the count instantly.

This is why I highly suspect it is not a code issue or N+1 issues like many have suggested. I suspect its an environment issue, but that's beyond my knowledge why this is happening. If anyone has ran into this before, can someone provide an opinion? If you need LINQ code please let me know I can shorten it and upload.

        IQueryable<StudentDm> studentQuery = GetListQuery();
        IQueryable<StudentTestDm> studentTestQuery = studentQuery.SelectMany(a => a.StudentTests);

        if (studentAcademicTestProficiencyGrowthParameter.TestTypeId.HasValue)
        {
            studentTestQuery = studentTestQuery.Where(a => a.TestTypeId == studentAcademicTestProficiencyGrowthParameter.TestTypeId);
        }

        DateTime serviceDateFrom = (studentAcademicTestProficiencyGrowthParameter.ServiceDateFrom.HasValue) ? studentAcademicTestProficiencyGrowthParameter.ServiceDateFrom.Value : DateTime.MinValue;
        DateTime serviceDateTo = (studentAcademicTestProficiencyGrowthParameter.ServiceDateTo.HasValue) ? studentAcademicTestProficiencyGrowthParameter.ServiceDateTo.Value : DateTime.MaxValue;

        // test 1 query
        IQueryable<StudentTestDm> studentTest1Query = studentTestQuery;

        if (studentAcademicTestProficiencyGrowthParameter.Test1SchoolYear.HasValue)
        {
            studentTest1Query = studentTest1Query.Where(a => a.Year == studentAcademicTestProficiencyGrowthParameter.Test1SchoolYear);
        }

        if (studentAcademicTestProficiencyGrowthParameter.Test1TestDate.HasValue)
        {
            studentTest1Query = studentTest1Query.Where(a => a.TestDate == studentAcademicTestProficiencyGrowthParameter.Test1TestDate);
        }

        // we grab the grouped students who has non-proficient test, and who does not have proficient test
        IQueryable<IGrouping<StudentDm, StudentTestDm>> groupedStudentWithTest1 = studentTest1Query.GroupBy(a => a.Student).Where(a => a.Select(b => b.TestLevel.ProficiencyId).Contains((int)LookUpEnum.LookUpEnum.TestLevelProficiency.NotProficient)
                                                                                                                                    && !a.Select(b => b.TestLevel.ProficiencyId).Contains((int)LookUpEnum.LookUpEnum.TestLevelProficiency.Proficient));

        // get the count of students whos attended less than 30 days of services
        count1 = groupedStudentWithTest1.Where(a => a.Key.StudentServices.Where(b => b.Date >= serviceDateFrom && b.Date <= serviceDateTo).GroupBy(b => b.Date).Count() < 30).Count();

        // test 2 query
        IQueryable<StudentTestDm> studentTest2Query = studentTestQuery;

        if (studentAcademicTestProficiencyGrowthParameter.Test2SchoolYear.HasValue)
        {
            studentTest2Query = studentTest2Query.Where(a => a.Year == studentAcademicTestProficiencyGrowthParameter.Test2SchoolYear);
        }

        if (studentAcademicTestProficiencyGrowthParameter.Test2TestDate.HasValue)
        {
            studentTest2Query = studentTest2Query.Where(a => a.TestDate == studentAcademicTestProficiencyGrowthParameter.Test2TestDate);
        }

        // we are grabbing the students who are not-proficient in test 1 but are proficient in test 2
        IQueryable<IGrouping<StudentDm, StudentTestDm>> groupedStudentWithImprovedTest2 = studentTest2Query.GroupBy(a => a.Student).Where(a => a.Select(b => b.TestLevel.ProficiencyId).Contains((int)LookUpEnum.LookUpEnum.TestLevelProficiency.Proficient))
                                                                                                                                   .Where(a => groupedStudentWithTest1.Select(b => b.Key.Id).Contains(a.Key.Id)); 

        // THIS QUERY TIMES OUT (ON Database Server): get the count students who attended less than 30 days of services
        count2 = groupedStudentWithImprovedTest2.Where(a => a.Key.StudentServices.Where(b => b.Date >= serviceDateFrom && b.Date <= serviceDateTo).GroupBy(b => b.Date).Count() < 30).Count();

Answer 1

See chat discussion. The query followed the 'optional filtering' pattern, and the issue was parameter sniffing.

The query was captured from SQL Profiler and run in SSMS but the issue could not be reproduced.

However when all the preceding SET options were also added from SQL Profiler, the problem was reproduced in SSMS

Adding OPTION (RECOMPILE) to the end of the query appeared to solve the issue.

(Specifically this was added right before the closing quote in the sp_executesql)

This link EF 6 Parameter Sniffing shows a way to add OPTION (RECOMPILE) via LINQ. I will await a reponse from the original poster to ascertain whether this really works or not.

The link says "It's possible to use the interception feature of EF6"

And shows some code like this (abridged):

public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{


    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        addQueryHint(command);
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        addQueryHint(command);
    }

    private static void addQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}