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

Question

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?

Answer 1

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.

Answer 2

Everything after AsEnumerable() is executed locally rather than on the server. See also

https://stackoverflow.com/a/2013876/141172

This means that all rows in the table are returned from the database, and then filtered in your C# code.

Remove that call so that the filtering happens server-side.

EDIT

Noticed Jon's comment and it reminded me that he reimplemented LINQ to Objects as a learning exercise. His comments about the AsEnumerable() reimplementation are worth reading

I can describe its behaviour pretty easily: it returns source.

That's all it does. There's no argument validation, it doesn't create another iterator. It just returns source.

You may well be wondering what the point is... and it's all about changing the compile-time type of the expression. I'm going to take about IQueryable in another post (although probably not implement anything related to it) but hopefully you're aware that it's usually used for "out of process" queries - most commonly in databases.

Now it's not entirely uncommon to want to perform some aspects of the query in the database, and then a bit more manipulation in .NET - particularly if there are aspects you basically can't implement in LINQ to SQL (or whatever provider you're using). For example, you may want to build a particular in-memory representation which isn't really amenable to the provider's model.

https://msmvps.com/blogs/jon_skeet/archive/2011/01/14/reimplementing-linq-to-objects-part-36-asenumerable.aspx

Answer 3

Your code should like this..

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().AsQueryable()
.Where(p =>(p.SalesPhaseId == naId || p.SalesPhaseId == rejectedId) &&
p.CreatedDate>= fromDate.Date && p.CreatedDate <= toDate.Date).Select(m =>
    new
    {
    m.LeadId,
    m.LeadOwnerId,
    m.SalesPhaseId,
    m.LeadActivities,
    m.Employee,
    m.SalesPhase,
    m.CompanyName,
    m.CreatedDate,
    m.LeadHistories,
    m.LeadAddresses
    }).ToList();

Firstly, You need to use .ToQueryable instead of .ToIEnumerable(). Secondly, you cannot use .Date to datetime properties inside a entity framework linq query. That only works for in-memory collections like list and arrays.