Thanks to Julie Lerman's Deconstructing LINQ to SQL post, I learned about Mike Taulty's two-part series that digs into the inner workings of the LINQ to SQL API:
Deconstructing LINQ to SQL (Part 1) discusses the differences and similarities between IEnumerable<T> and IQueryable<T>. Mike's abbreviated take is:
To me, the primary difference between IQueryable and IEnumerable with respect to LINQ is that I view IQueryable as offering the potential for "capturing the whole query and executing it in one go" whereas I view IEnumerable as "executing a set of functions in sequence on lists in order to produce more lists".
Deconstructing LINQ to SQL (Part 2) discusses how IEnumerable<T> and IQueryable<T> relate to LINQ to SQL. Here's Mike's conclusion:
I do now know where the T-SQL comes from and I do know that the infrastructure uses SqlCommand and so on but I still wouldn't like to say that I have this "nailed" because I think it's pretty hard to walk through without actually single-stepping the live source code and I don't have that (and, at some point, it's sensible to give up and accept that "it works.")
As I mentioned in this earlier Yet Another Primer on New Language Features in Orcas post, Mike is a Microsoft UK evangelist "involved in getting information out to developers about what's happening with the Microsoft platform through online mechanisms like newsletters, blogs, videos and through offline mechanisms such as technical sessions."