The linq query taking too much time. Need to reduce the Time

Here i am using the below query and its taking lots of time around 14 to 15 seconds for retrieving the large amount of data. In below Query the CreatedDate is of DateTimeOffset data type.

var naId = UnitOfWork.SalesPhases.FirstOrDefault(p => p.PhaseName =="NA").SalesPhaseId;
var rejectedId = UnitOfWork.SalesPhases.FirstOrDefault(p => p.PhaseName =="Rejected").SalesPhaseId;

var data = UnitOfWork.Leads.Query().AsEnumerable()
.Where(p =>(p.SalesPhaseId == naId || p.SalesPhaseId == rejectedId) &&
p.CreatedDate.Date >= fromDate && p.CreatedDate.Date <= toDate).Select(m =>
        new
        {
        m.LeadId,
        m.LeadOwnerId,
        m.SalesPhaseId,
        m.LeadActivities,
        m.Employee,
        m.SalesPhase,
        m.CompanyName,
        m.CreatedDate,
        m.LeadHistories,
        m.LeadAddresses
        }).ToList();

I tried using the AsQueryable instead of the AsEnumerable but it gives the below error:

"The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

Can you help me out to reduce the execution time of the query?

Jon Skeet
people
quotationmark

Your use of AsEnumerable is forcing the filtering to be done locally. It's pulling in all the data, then filtering it in your app. That's clearly very inefficient. Now, it seems that part of your query can't be directly expressed in LINQ to SQL. I see two options here.

Firstly you could do most of your filtering in SQL, but then do the date filtering locally:

var data = UnitOfWork.Leads.Query()
                     // Do this part of the query in SQL
                     .Where(p => p.SalesPhaseId == naId || 
                                 p.SalesPhaseId == rejectedId) 
                     .AsEnumerable()
                     // Do the rest of the query in-process
                     .Where(p => p.CreatedDate.Date >= fromDate &&
                                 p.CreatedDate.Date <= toDate)
                     .Select(...)

That's suitable if the first part will filter it down massively, and then you only need to do local processing of a small set of data.

Alternatively, you could work out what your date filtering means in terms of DateTime. It looks like you could do:

// This may not be required, depending on the source.
fromDate = fromDate.Date;
// This will be, although you may be able to get rid of the ".Date" part.
toDate = toDate.Date.AddDays(1);

var data = UnitOfWork.Leads.Query()
                     // Do this part of the query in SQL
                     .Where(p => (p.SalesPhaseId == naId || 
                                  p.SalesPhaseId == rejectedId) &&
                                 p.CreatedDate >= fromDate &&
                                 p.CreatedDate < toDate)
                     .Select(...)

That's created an equivalent query, but without using the Date property in the query itself.

people

See more on this question at Stackoverflow