.NET .ToList function is WAY WAY too slow

Question

We're having alot of troubles here with the .ToList command, it's used in VB.NET with a MVC ASP.NET web project.

We have ~2000 entries in our database, we use a LINQ command to SELECT and ORDER the 2000 entries. The result is transformed into a list by the .ToList method for our pager and grid builder. Problem is, the .ToList takes WAY WAY TOO long (we're talking 40-60seconds to execute) so our websites looks slow as hell.

We tested the equivalent SQL command on the database and it responds quickly. It's not a problem with the commands or a slow database server. We tried an IEnumrable witch was alot faster but we need it in the .ToList format at the end for our grids. What's the deal with the .ToList ? Anything we can do ?

Here's the code :

 'list = (From c In _entities.XXXXXXXXSet.Include("XXXXXX").Include("XXXXXX") _
                Where Not (c.XXXXXX Is Nothing AndAlso c.XXXXXX = String.Empty) _
                And c.XXXXXX = codeClient _
                And c.XXXXXX > dateLimite _
                Order By c.XXXXXX Descending _
                Select c).ToList()

We divided the code and to leave only the .ToList function alone and that's really what sucks up all the time. The LINQ command executes in no time.

Thanks alot. Tom

Answer 1

Of course the LINQ command "executes" in no time, because it just represents the query. The query is only executed once you iterate over it, which is exactly what the ToList method does.

I would advise you to use the Skip and Take operators in your pagers to narrow down the result queried from the database. Doing this, you only request the 10 or 20 elements or whatever you need, resulting in a much smoother experience.