C# – EF LINQ ToList is very slow

cef-code-firstentity-frameworklinqnet

I am using ASP NET MVC 4.5 and EF6, code first migrations.

I have this code, which takes about 6 seconds.

var filtered = _repository.Requests.Where(r => some conditions); // this is fast, conditions match only 8 items
var list = filtered.ToList(); // this takes 6 seconds, has 8 items inside

I thought that this is because of relations, it must build them inside memory, but that is not the case, because even when I return 0 fields, it is still as slow.

var filtered = _repository.Requests.Where(r => some conditions).Select(e => new {}); // this is fast, conditions match only 8 items
var list = filtered.ToList(); // this takes still around 5-6 seconds, has 8 items inside

Now the Requests table is quite complex, lots of relations and has ~16k items. On the other hand, the filtered list should only contain proxies to 8 items.

Why is ToList() method so slow? I actually think the problem is not in ToList() method, but probably EF issue, or bad design problem.

Anyone has had experience with anything like this?

EDIT:

These are the conditions:

_repository.Requests.Where(r => ids.Any(a => a == r.Student.Id) && r.StartDate <= cycle.EndDate && r.EndDate >= cycle.StartDate)

So basically, I can checking if Student id is in my id list and checking if dates match.

Best Answer

Your filtered variable contains a query which is a question, and it doesn't contain the answer. If you request the answer by calling .ToList(), that is when the query is executed. And that is the reason why it is slow, because only when you call .ToList() is the query executed by your database.

It is called Deferred execution. A google might give you some more information about it.

If you show some of your conditions, we might be able to say why it is slow.

Related Topic