Background
The background for this is that I had a recent conversation in the comments with another clearly knowledgeable user about how LINQ is compiled. I first "summarized" and said LINQ was compiled to a for loop. While this isn't correct, my understanding from other stacks such as this one is that the LINQ query is compiled to a lambda with a loop inside of it. This is then called when the variable is enumerated for the first time (after which the results are stored). The other user said that LINQ takes additional optimizations such as hashing. I couldn't find any supporting documentation either for or against this.
I know this seems like a really obscure point but I have always felt that if I don't understand how something works completely, its going to be difficult to understand why I'm not using it correctly.
The Question
So, lets take the following very simple example:
var productNames =
from p in products
where p.Id > 100 and p.Id < 5000
select p.ProductName;
What is this statement actually compiled to in CLR? What optimizations does LINQ take over me just writing a function that manually parses the results? Is this just semantics or is there more to it than that?
Clarification
Clearly I'm asking this question because I don't understand what the inside of the LINQ "black box" looks like. Even though I understand that LINQ is complicated (and powerful), I'm mostly looking for a basic understanding of either the CLR or a functional equivalent to a LINQ statement. There are great sites out there for helping understand how to create a LINQ statement but very few of these seem to give any guidance on how those are actually compiled or run.
Side Note - I will absolutely read through the John Skeet series on linq to objects.
Side Note 2 - I shouldn't have tagged this as LINQ to SQL. I understand how ORM's and micro-ORM's work. That is really besides the point of the question.