LINQ ToList (). Take (10) vs Take (10) .ToList (), which generates a more efficient request

Question

Given the following LINQ statements, which will be more efficient?

ONE:

public List<Log> GetLatestLogEntries() { var logEntries = from entry in db.Logs select entry; return logEntries.ToList().Take(10); } 

TWO:

 public List<Log> GetLatestLogEntries() { var logEntries = from entry in db.Logs select entry; return logEntries.Take(10).ToList(); } 

I am aware that .ToList () executes the request immediately.

Answer 1

The first version will not even compile, because the return value of Take is IEnumerable<T> , not List<T> . Therefore, you will need:

 public List<Log> GetLatestLogEntries() { var logEntries = from entry in db.Logs select entry; return logEntries.ToList().Take(10).ToList(); } 

This will allow you to get all the data from the database and convert it to a list, and then take the first 10 records, and then convert them to a list again.

Getting Take(10) in the database (i.e. the second form) certainly looks a lot cheaper for me ...

Note that there is no Queryable.ToList() method - you end up calling Enumerable.ToList() , which will retrieve all the records. In other words, the ToList call ToList not involved in the SQL translation, whereas Take does.

Also note that using a query expression here doesn't make much sense either. I would write this as:

 public List<Log> GetLatestLogEntries() { return db.Log.Take(10).ToList(); } 

Keep in mind, you may need to call OrderBy - otherwise it will just take the first 10 records it finds, which may not be the very last ...

Answer 2

The second version will be more efficient (both when using time and in memory). For example, imagine that you have a sequence containing 1,000,000 elements:

• The first version iterates through all 1,000,000 elements, adding them to the list as it appears. Then, finally, the first 10 items from this large list will be taken.

• The second version should only iterate over the first 10 elements, adding them to the list as it appears. (The remaining 999,990 points do not even need to be considered.)

Answer 3

Your first option will not work, because .Take(10) convert it to IEnumerable<Log> . The return type is List<Log> , so you will need to do return logEntries.ToList().Take(10).ToList() , which is more inefficient.

By executing .ToList().Take(10) , you force .Take(10) be a LINQ object to the objects, while in another way the filter can be passed to a database or other underlying data source. In other words, if you first execute .ToList() , ALL objects must be transferred from the database and allocated in memory. THEN you filter to the first 10. If you are talking about millions of database rows (and objects), you can imagine how it is VERY inefficient and not scalable.

The second one will start right away because you have .ToList() , so there is no difference.

Answer 4

How about this?

 I have 5000 records in "items" 

version 1:

  IQueryable<T> items = Items; // my items items = ApplyFilteringCriteria(items, filter); // my filter BL items = ApplySortingCriteria(items, sortBy, sortDir); // my sorting BL items = items.Skip(0); items = items.Take(25); return items.ToList(); 

it took: 20 seconds on the server

version 2:

  IQueryable<T> items = Items; // my items items = ApplyFilteringCriteria(items, filter); // my filter BL items = ApplySortingCriteria(items, sortBy, sortDir); // my sorting BL List<T> x = items.ToList(); items = x.Skip(0).ToList(); items = x.Take(25).ToList(); return x; 

it took: 1 second on the server

What do you think now? Any idea why?

Answer 5

The second option.

The first will evaluate the entire enumerable by plunging it into List (); then you set an iterator that will go through the first ten objects and then exit.

The second first sets the Take () iterator, so no matter what happens later, only 10 objects will be processed and sent down for processing (in this case, ToList (), which will take these ten elements and return them as a specific list).