LINQ to SQL performance with "SELECT TOP {x}" queries

Question

In looking up how to perform an equivalent to SELECT TOP 5 with LINQ-to-SQL, all the answers I've seen suggest using .Take(), like so:

var myObject = (
from myObjects in repository.GetAllMyObjects()
select myObject)
.Take(10);

I don't yet understand most of how LINQ works behind-the-scenes but to my understanding of C-like languages this would resolve by first assigning a temporary array containing ALL records, then copying the first 10 elements in the array to var. Not such a problem if you're working on a small dataset or without any performance constraints but it seems horribly inefficient to me if you're, for example, selecting the most recent 5 log entries from a table which can contain millions of records.

Is my understanding of how this works wrong? If so, could someone explain what actually happens? Otherwise, what (if any) better (ie more efficient) way is there of only selecting x records through LINQ-to-SQL?

[edit]

I have the hypothetical myObject class sending LINQ-to-SQL output to the debug output as per the suggestion in the accepted answer. I ended up using the DebuggerWriter from here: http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11

Answer 1

Your assumption is incorrect. With Linq to SQL, it evaluates to an Expression<Func<...>> which can be evaluated and the proper SQL generated. You do not need to worry about it loading all the records.

Also, see this following question. You can attach a stream to your DataContext and see the SQL generated. http://stackoverflow.com/questions/637117/how-to-get-the-tsql-query-from-linq-datacontext-submitchanges

Answer 2

LINQ uses deferred execution, and, for LINQ-to-SQL, expression trees.

No query will be executed until you enumerate the result of the Take call, so you don't need to worry about anything.

Answer 3

I just went through this last week! I opened the SQL profiler on my dev data base and stepped through the code. It was very interesting to see the generated SQL for the various queries. I recommend you do the same. It may not be an exact answer to your question but it was certainly enlightening to see how your various components generate entirely different SQL statements depending on the contents of the call.

I believe the "deferred query resolution" or something (?) reading on MSDN would be enlightening as well.