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.
