Geek Answers Handbook

.NET LINQ Expression <Func <T, bool >> Performance Issues

I have two functions:

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 LINQ. It is supposed to use LINQ To SQL instead of LINQ To Objects. Performance should be good.

But I don’t see it. I suppose LINQ retrieves all the table data into memory for processing using LINQ To Objects. I do not see a sql connection request sent to track SQL Server by SQL profile, but a bunch of a separate table select statement.

It takes more than 10 minutes to get something from

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

I know the problem is

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

tblTaskDetail is a large table. If I switch to a smaller one, performance will improve markedly.

Anyone can help figure out why this is not so.

Thanks,

1 - Entity Framework, SQL, :

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 '

, sql.

2 -

1. SQL. , SQL . 6 select, 11 LEFT OUTER JOINs 10 OUTER APPLY. . SQL 9 .

0
1

EF STE 5 EF6. . 1,5 12 .

0

More articles:


All Articles