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<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
Bottom line — if you have a LINQ to SQL statement and you can save it as an IQueryable
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.
on October 20th, 2007 at 12:00 pm
I tried it and it worked (I got the “TOP 10″).
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?
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
on November 18th, 2008 at 8:09 am
[...] IEnumerable vs IQuerable Watch the following article:IEnumerable<T> vs IQueryable<T> [...]
on April 30th, 2009 at 9:34 pm
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.
on June 24th, 2009 at 11:50 am
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
on June 24th, 2009 at 11:57 am
@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);
on June 24th, 2009 at 1:46 pm
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
on June 24th, 2009 at 1:49 pm
@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);
on September 8th, 2009 at 12:45 am
[...] IQueryable<T> vs. IEnumerable<T> in LINQ to SQL queries by Jon Kruger [...]
on September 18th, 2009 at 2:16 am
Good article. It helped me alot.