Jon Kruger

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<Product> list = dc.Products
     .Where(p => p.ProductName.StartsWith("A"));
list = list.Take<Product>(10);
Debug.WriteLine(list.Count<Product>());


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


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


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, 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, 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 instead of an IEnumerable, do so so that you have flexibility to continue to add onto that query.

• Leave a comment... (12)