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

Posted on October 19th, 2007 in LINQ by 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.

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

Subscribe to comments with RSS or TrackBack to 'IQueryable<T> vs. IEnumerable<T> in LINQ to SQL queries'.

  1. Matt Casto said,

    on October 20th, 2007 at 6:37 am

    I wonder what the result would be if you did something like:

    var list = dc.Products.Where(p => p.ProductName.StartsWith(”A”));

    I’m not near any computers with Visual Studio installed on them this weekend, so I’ll have to wait until next week to try it out.

  2. Jon said,

    on October 20th, 2007 at 12:00 pm

    I tried it and it worked (I got the “TOP 10″).

  3. WebGyver said,

    on December 20th, 2007 at 1:00 pm

    Wow! Awesome stuff. I’m nowhere near the level you guys are on, of course, but I have learned a lot from reading through & working through your examples.

    However, I was wondering if you could enlighten me a little bit about the problems that I would encounter with LINQ (at this point in time) as far as working with SQL Server?

    To be more specific, I’m faced with rewriting a legacy application, and we’ve done a ton of development with Stored Procedures in the past. Is LINQ really going to make my life easier (in 2008)?

    What are the most obvious problems — if any — that I would have to deal with? Links? Ideas? Suggestions?

  4. Jon Kruger said,

    on December 20th, 2007 at 9:45 pm

    WebGyver-

    I’m in a similar situation as you in that I was rewriting a legacy application. I have been very impressed with LINQ to SQL and I would highly recommend it. We are working with a legacy database (SQL Server 2005) that has some really unusual stuff in it, and LINQ to SQL has been able to handle it all.

    IMO, if you want to get all of the benefits out of LINQ to SQL (or any ORM for that matter), I would try and get away from the stored procedures and allow LINQ to SQL to handle all the work. LINQ to SQL will optimize all of the queries for you, and if you do have a situation where you need to use stored procs, LINQ to SQL can wrap those nicely for you so that calling the stored proc is as easy as calling a method.

    Good luck!

    Jon

  5. IEnumerable vs IQuerable « Paris Polyzo’s Weblog said,

    on November 18th, 2008 at 8:09 am

    […] IEnumerable vs IQuerable Watch the following article:IEnumerable<T> vs IQueryable<T> […]

Post a comment