Order of execution of LINQ methods returning same results but different SQL in this code. What exactly is going on inside?

Question

So, here's my code:

Notice the positions of the ToList() method here, which is of IEnumerable, comparing it line by line.

Customers.ToList().Where(m=>m.ID > 3).OrderByDescending(m=>m.Name).FirstOrDefault();

Customers.Where(m=>m.ID > 3).ToList().OrderByDescending(m=>m.Name).FirstOrDefault();

Customers.Where(m=>m.ID > 3).OrderByDescending(m=>m.Name).ToList().FirstOrDefault();

Let's go through it line by line:

Customers.ToList().Where(m=>m.ID > 3) .OrderByDescending(m=>m.Name).FirstOrDefault()

• .ToList() - Enumerable
• .Where() - Enumerable
• .OrderByDescending() - Enumerable
• .FirstOrDefault - Enumerable

Customers.Where(m=>m.ID > 3).ToList() .OrderByDescending(m=>m.Name).FirstOrDefault()

• .Where() - Queryable
• .ToList() - Enumerable
• .OrderByDescending() - Enumerable
• .FirstOrDefault() - Enumerable

Customers.Where(m=>m.ID > 3).OrderByDescending(m=>m.Name) .ToList().FirstOrDefault()

• .Where() - Queryable
• .OrderByDescending() - Queryable
• .ToList() - Enumerable
• .FirstOrDefault() - Enumerable

Now, here are their SQL, by order:

SELECT [t0].[ID], [t0].[Name] FROM [Customer] AS [t0] GO

SELECT [t0].[ID], [t0].[Name] FROM [Customer] AS [t0] WHERE [t0].[ID]

SELECT TOP (1) [t0].[ID], [t0].[Name] FROM [Customer] AS [t0] WHERE [t0].[ID] > @p0 ORDER BY [t0].[Name] DESC

It seems that line1 gets the ENTIRE collection and passes it through the wire, while line3 gets only ONE entity.

According to the SQL output, I can infer that:

    line1: Memory-intensive code; requires more bandwidth since more data is passed in the wire;
    line3: Database-intensive code; requires less bandwidth since less data is passed in the wire

I'm curious as to what goes on internally between each lines of codes. They are relatively similar, and I get the same exact results.

What exactly goes on in here (within the insides of C# and its features?)?

Answer 1

Methods like .Where() and .OrderBy() use deferred execution, which means that all they do when called is modify the expression tree for the query - they do not cause the query to be executed. The underlying query is only executed when it is enumerated by something (e.g. by doing a foreach on it).

.ToList() on the other hand is intended to return an in-memory list of the query results, that is, it actually causes the query to be executed. It is conceptually similar to doing something like the following pseudocode

foreach (item in query)
   list.add(item) 

In your examples, the query is executed by the .ToList() and from that point on you are doing a new query, this time against the in-memory collection.

So, in the 1st example the Customers query it is executed immediately by the ToList(), the .Where() and .OrderBy() just modify a new expression tree using the Linq to objects query provider and this Linq to objects query is executed by the FirstOrDefault().

Answer 2

.ToList() method makes it Enumarable and generates SQL at that time. After that you are using LINQ to Objects.

So, for example, at first line you are getting whole table, because you are calling .ToList() on Customers.

It's not a good solution, because you are getting whole records into your memory, so call .ToList() at the end of your query always if there are no reasons to call it before.