software solutions / project leadership / agile coaching and training

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

Posted on October 19, 2007

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, 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.

14 Comments »

  1. 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.

    Matt Casto — October 20, 2007 @ 6:37 am

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

    Jon — October 20, 2007 @ 12:00 pm

  3. 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?

    WebGyver — December 20, 2007 @ 1:00 pm

  4. 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

    Jon Kruger — December 20, 2007 @ 9:45 pm

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

    IEnumerable vs IQuerable « Paris Polyzo’s Weblog — November 18, 2008 @ 8:09 am

  6. You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

    SQL Tutorials — April 30, 2009 @ 9:34 pm

  7. Jon:
    I have an issue may be you can help me. I am using ASP.Net MVC (Coolite Controls) LINQ to SQL using StoredProcedure with Paging. Code is as below.

    Controller Call:
    ——————
    public AjaxStoreActionResult GetSelectRefGrids(int limit, int start, string dir, string sort, int paramProgramCode)

    Case 1:
    ————–

    var query = (from o in this.DBContext.mvc_getSelectGrids_ForProgramCode(paramProgramCode, start, limit, ref total)
    select new
    {
    o.RgID,
    o.GridLayoutLocation,
    o.ObjectType,
    o.Label,
    o.AttribCodes
    });

    int total = query.ToList().Count; // <> Invalid Operation Exception
    query = query.Skip(start).Take(limit);
    return new AjaxStoreActionResult(query, total);

    >> The Code fails returning Error as “Message = “The query results cannot be enumerated more than once.”
    >>
    >> So I changed Stored Procedure to Handle Paging and Return Total as OUT param

    Case 2:
    ——————-

    System.Nullable total = null
    var query = (from o in this.DBContext.mvc_getSelectGrids_ForProgramCode(paramProgramCode, start, limit, ref total)
    select new
    {
    o.RgID,
    o.GridLayoutLocation,
    o.ObjectType,
    o.Label,
    o.AttribCodes
    });
    return new AjaxStoreActionResult(query, total);

    >> Any suggestions go by Case 1 and to handle the error “The query results cannot be enumerated more than once.” – Thanks Venkat

    Venkat — June 24, 2009 @ 11:50 am

  8. @Venkat,

    Try changing it to this:

    var query = (from o in this.DBContext.mvc_getSelectGrids_ForProgramCode(paramProgramCode, start, limit, ref total)
    select new
    {
    o.RgID,
    o.GridLayoutLocation,
    o.ObjectType,
    o.Label,
    o.AttribCodes
    });

    query = query.Skip(start).Take(limit); // I moved this line up
    int total = query.ToList().Count; // Invalid Operation Exception
    return new AjaxStoreActionResult(query, total);

    Jon Kruger — June 24, 2009 @ 11:57 am

  9. Jon:

    Thanks for quick response. It did not solve the problem.

    This issue is in the same line as
    http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/f403e047-e38c-45ab-9155-dbf83c6cc2fa

    Venkat — June 24, 2009 @ 1:46 pm

  10. @Venkat,

    Try this then…

    @Venkat,

    Try changing it to this:

    var query = (from o in this.DBContext.mvc_getSelectGrids_ForProgramCode(paramProgramCode, start, limit, ref total)
    select new
    {
    o.RgID,
    o.GridLayoutLocation,
    o.ObjectType,
    o.Label,
    o.AttribCodes
    });

    var list = query.ToList();
    list = query.Skip(start).Take(limit).ToList();
    int total = list.Count; // Invalid Operation Exception
    return new AjaxStoreActionResult(list, total);

    Jon Kruger — June 24, 2009 @ 1:49 pm

  11. [...] IQueryable<T> vs. IEnumerable<T> in LINQ to SQL queries by Jon Kruger [...]

    IQueryable vs IEnumerable « Ramani Sandeep — September 8, 2009 @ 12:45 am

  12. Good article. It helped me alot.

    Cherukuri Venkateswarlu — September 18, 2009 @ 2:16 am

  13. Hi Jon, good stuff!

    I fleshed out your examples in a blog post here: http://hamishgraham.net/post/RE-IQueryable-vs-IEnumerable-in-LINQ-to-SQL-queries.aspx

    Hamish Graham — March 27, 2012 @ 6:38 am

  14. [...] IQUERYABLE<T> VS. IENUMERABLE<T> IN LINQ TO SQL QUERIES (covers “implement query boundaries (IQueryable vs. IEnumerable)” section)   [...]

    Blogged By Chris » Microsoft Exam 70-487 Study Guide — January 8, 2013 @ 10:23 pm

Leave a comment





SERVICES
SOFTWARE SOLUTIONS
I have over 10 years of software development experience on several different platforms (mostly Ruby and .NET). I recognize that software is expensive, so I'm always trying to find ways to speed up the software development process, but at the same time remembering that high quality is essential to building software that stands the test of time.
PROJECT LEADERSHIP
I have experience leading and architecting large Agile software projects and coordinating all aspects of a project's lifecycle. Whether you're looking for technical expertise or someone to lead all aspects of an Agile project, I have proven experience from multiple projects in different environments that can help make your project a success.
AGILE COACHING
I believe that Agile processes and tools should be applied with common sense. I've spent the last 6 years working on Agile projects as a consultant in many different environments, both in leadership roles and as a practitioner doing the work. I can help you find out how Agile can work best in your organization, not just apply a prescriptive process.
TEST DRIVEN DEVELOPMENT TRAINING
TDD Boot Camp is a hands-on, three day, comprehensive training course that will teach you all of the skills, tools, frameworks that you will need to use test-driven development to develop real world .NET applications. If you're not looking for something that intensive, check out the the half-day version.
Have any questions? Contact me for more information.
PRESENTATIONS
The Business of You: 10 Steps For Running Your Career Like a Business
From CONDG 2012, Stir Trek 2014
From Stir Trek 2013, DogFoodCon 2013
From Stir Trek 2012, QA or the Highway 2014
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
(presented with Paul Bahler and Kevin Chivington from IGS Energy)
From CodeMash 2011
An idea of how to make JavaScript testable, presented at Stir Trek 2011. The world of JavaScript frameworks has changed greatly since then, but I still agree with the concepts.
A description of how test-driven development works along with some hands-on examples.
From CodeMash 2010
From CodeMash 2010