.NET LINQ Expression> Performance Issues

Question

I have two functions as follow -

public IQueryable<RequestSummaryDTO> GetProgramOfficerUSA(Guid officerId)
{
    List<string> officerCountries = UnityProvider.Instance.Get<IProgramOfficerService>().GetPOCountries(officerId).Select(c => c.CNTR_ID.ToUpper()).ToList();
    Expression<Func<TaskRequest, bool>> countriesFilter = (a) => officerCountries.Contains(a.tblTaskDetail.FirstOrDefault().tblOrganization.ORG_Country.ToUpper());

    Expression<Func<TaskRequest, bool>> USAAndNotDelegated = LinqUtils.And(this.USAFilter(), this.NotDelegatedFilter(officerId));
    Expression<Func<TaskRequest, bool>> countriesOrOwned = LinqUtils.Or(countriesFilter, this.OwnedFilter(officerId));
    Expression<Func<TaskRequest, bool>> filter = LinqUtils.And(USAAndNotDelegated, countriesOrOwned);

    return this.Get(filter, TaskRequestState.USA);
}



private IQueryable<RequestSummaryDTO> Get(Expression<Func<TaskRequest, bool>> additionalFilter, TaskRequestState? TaskRequestState = null)
{
   var Tasks = this.TaskRequestRepository.List(additionalFilter).Where(x => x.tblTaskDetail.FirstOrDefault().PD_TaskRequestID == x.Id && 
            (x.tblRequestDetail.AD_Status == (int)RequestStatus.Paid || x.tblRequestDetail.AD_Status == (int)RequestStatus.NotConfirmed));

    if (TaskRequestState == TaskRequestState.USA)
    {
        Tasks = Tasks.Where(w => (w.tblTaskDetail.FirstOrDefault().PD_PStatus == null || w.tblTaskDetail.FirstOrDefault().PD_PStatus == 125));
    }
    else
    {
        Tasks = Tasks.Where(w => w.State == null);
    }

    return Tasks.ToList().Select(TaskSummaryFactory.CreateDto).AsQueryable();
}

I used Expression> and IQueryable in the LINQ. It is supposed to use LINQ To SQL instead of LINQ To Objects. The performance should be good.

But I do not see that. I believe the LINQ pulls every table's data into memory to process using LINQ To Objects. I do not see a join sql query sent to SQL Server tracking by SQL profile but a bunch of individual table selection statement.

It takes over 10 minutes to get something return from

return Tasks.ToList().Select(TaskSummaryFactory.CreateDto).AsQueryable();

I know the problem is in

Expression<Func<TaskRequest, bool>> countriesFilter = (a) => officerCountries.Contains(a.tblTaskDetail.FirstOrDefault().tblOrganization.ORG_Country.ToUpper());

tblTaskDetail is a big table. If I switch to a smaller one, the performance is noticeably improved.

Anyone can help find out why is wrong there.

Thanks,

Update 1 - The statement from Entity Framework logged in SQL Profile are all like this -

exec sp_executesql N'SELECT [Extent1].[ORG_ID] AS [ORG_ID], [Extent1].[ORG_CreatedBy] AS [ORG_CreatedBy], [Extent1].[ORG_CreatedOn] AS [ORG_CreatedOn] FROM [dbo].[tblOrganization] AS [Extent1] WHERE [Extent1].[ORG_ID] = @EntityKeyValue1',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='E8C3F120-AA40-445E-A8A0-2937F330D347'

They are all just having individual table select statement, not joined sql statement.

Update 2 -

I was wrong in Update 1. I missed the join SQL statement. The problem is that the generated SQL is too poor. There are 6 nested select statements, 11 LEFT OUTER JOINs, and 10 OUTER APPLYs. The query is too long and can not post here. Executing the generated SQL takes 9 minutes.

Answer 1

I have upgraded the EF STE 5 to EF6. Now the performance is better. The page loading time is cut down to 1.5 minutes from 12 minutes.