IQueryable<T> vs. IEnumerable<T> in LINQ to SQL queries

I ran into something interesting today when working with LINQ to SQL. Take a look at these three code snippets:

#1:

NorthwindDataContext dc = new NorthwindDataContext();
IEnumerable list = dc.Products
     .Where(p => p.ProductName.StartsWith("A"));
list = list.Take(10);
Debug.WriteLine(list.Count());

#2:

NorthwindDataContext dc = new NorthwindDataContext();
IEnumerable list2 = dc.Products
     .Where(p => p.ProductName.StartsWith("A"))
     .Take(10);
Debug.WriteLine(list2.Count());

#3:

NorthwindDataContext dc = new NorthwindDataContext();
IQueryable list3 = dc.Products
     .Where(p => p.ProductName.StartsWith("A"));
list3 = list3.Take(10);
Debug.WriteLine(list3.Count());

What would you expect the generated SQL statements of all of these to be? I was expecting something like this:

SELECT TOP 10 [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [Products] AS [t0]
WHERE [t0].[ProductName] LIKE @p0

Which is what you get in #2 and #3, but not in #1, where you get this:

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [Products] AS [t0]
WHERE [t0].[ProductName] LIKE @p0

(notice the “TOP 10” is missing)

I’m not exactly sure what is going on under the hood, but it appears that when you do a query (or a portion of a query in this case), you cannot save the result as an IEnumerable<T>, add to the query in a later statement (as I did in #1), and have LINQ to SQL know that it needs to combine the statements into one query. However, if you store the first part of your query as IQueryable<T>, then you can add onto that query later (before it actually gets executed, of course).

Bottom line — if you have a LINQ to SQL statement and you can save it as an IQueryable<T> instead of an IEnumerable<T>, do so so that you have flexibility to continue to add onto that query.