2009-10-16 15:23:56 8 Comments
What is the use of IQueryable
in the context of LINQ?
Is it used for developing extension methods or any other purpose?
Related Questions
Sponsored Content
21 Answered Questions
15 Answered Questions
[SOLVED] Returning IEnumerable<T> vs. IQueryable<T>
- 2010-05-20 18:13:59
- stackoverflowuser
- 195542 View
- 967 Score
- 15 Answer
- Tags: c# linq linq-to-sql ienumerable iqueryable
9 Answered Questions
19 Answered Questions
[SOLVED] Dynamic LINQ OrderBy on IEnumerable<T> / IQueryable<T>
- 2008-09-03 06:30:31
- John Sheehan
- 246611 View
- 635 Score
- 19 Answer
- Tags: c# linq linq-to-objects
34 Answered Questions
7 Answered Questions
[SOLVED] Multiple "order by" in LINQ
- 2008-11-18 13:34:11
- Sasha
- 537435 View
- 1456 Score
- 7 Answer
- Tags: linq sql-order-by
7 Answered Questions
13 Answered Questions
[SOLVED] LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria
- 2009-11-16 23:59:33
- p.campbell
- 240827 View
- 442 Score
- 13 Answer
- Tags: .net linq linq-to-sql
296 Answered Questions
[SOLVED] Hidden Features of C#?
- 2008-08-12 16:32:24
- Serhat Ozgel
- 645673 View
- 1476 Score
- 296 Answer
- Tags: c# hidden-features
7 Answered Questions
[SOLVED] What's the difference between IQueryable and IEnumerable
- 2010-03-12 14:22:40
- James Hay
- 75529 View
- 142 Score
- 7 Answer
- Tags: linq ienumerable iqueryable .net-3.0
4 comments
@Moumit 2016-02-25 14:00:44
Although Reed Copsey and Marc Gravell already described about
IQueryable
(and alsoIEnumerable
) enough,mI want to add little more here by providing a small example onIQueryable
andIEnumerable
as many users asked for itExample: I have created two table in database
The Primary key(
PersonId
) of tableEmployee
is also a forgein key(personid
) of tablePerson
Next i added ado.net entity model in my application and create below service class on that
they contains same linq. It called in
program.cs
as defined belowThe output is same for both obviously
So the question is what/where is the difference? It does not seem to have any difference right? Really!!
Let's have a look on sql queries generated and executed by entity framwork 5 during these period
IQueryable execution part
IEnumerable execution part
Common script for both execution part
So you have few questions now, let me guess those and try to answer them
Why are different scripts generated for same result?
Lets find out some points here,
all queries has one common part
WHERE [Extent1].[PersonId] IN (0,1,2,3)
why? Because both function
IQueryable<Employee> GetEmployeeAndPersonDetailIQueryable
andIEnumerable<Employee> GetEmployeeAndPersonDetailIEnumerable
ofSomeServiceClass
contains one common line in linq querieswhere employeesToCollect.Contains(e.PersonId)
Than why is the
AND (N'M' = [Extent1].[Gender])
part is missing inIEnumerable
execution part, while in both function calling we usedWhere(i => i.Gender == "M") in
program.cs`What entity framwork does when an
IQueryable
method called, it tooks linq statement written inside the method and try to find out if more linq expressions are defined on the resultset, it then gathers all linq queries defined until the result need to fetch and constructs more appropriate sql query to execute.It provide a lots of benefits like,
like here in example sql server returned to application only two rows after IQueryable execution` but returned THREE rows for IEnumerable query why?
In case of
IEnumerable
method, entity framework took linq statement written inside the method and constructs sql query when result need to fetch. it does not include rest linq part to constructs the sql query. Like here no filtering is done in sql server on columngender
.But the outputs are same? Because 'IEnumerable filters the result further in application level after retrieving result from sql server
SO, what should someone choose? I personally prefer to define function result as
IQueryable<T>
because there are lots of benefit it has overIEnumerable
like, you could join two or more IQueryable functions, which generate more specific script to sql server.Here in example you can see an
IQueryable Query(IQueryableQuery2)
generates a more specific script thanIEnumerable query(IEnumerableQuery2)
which is much more acceptable in my point of view.@Marc Gravell 2009-10-16 15:28:31
In essence its job is very similar to
IEnumerable<T>
- to represent a queryable data source - the difference being that the various LINQ methods (onQueryable
) can be more specific, to build the query usingExpression
trees rather than delegates (which is whatEnumerable
uses).The expression trees can be inspected by your chosen LINQ provider and turned into an actual query - although that is a black art in itself.
This is really down to the
ElementType
,Expression
andProvider
- but in reality you rarely need to care about this as a user. Only a LINQ implementer needs to know the gory details.Re comments; I'm not quite sure what you want by way of example, but consider LINQ-to-SQL; the central object here is a
DataContext
, which represents our database-wrapper. This typically has a property per table (for example,Customers
), and a table implementsIQueryable<Customer>
. But we don't use that much directly; consider:this becomes (by the C# compiler):
which is again interpreted (by the C# compiler) as:
Importantly, the static methods on
Queryable
take expression trees, which - rather than regular IL, get compiled to an object model. For example - just looking at the "Where", this gives us something comparable to:Didn't the compiler do a lot for us? This object model can be torn apart, inspected for what it means, and put back together again by the TSQL generator - giving something like:
(the string might end up as a parameter; I can't remember)
None of this would be possible if we had just used a delegate. And this is the point of
Queryable
/IQueryable<T>
: it provides the entry-point for using expression trees.All this is very complex, so it is a good job that the compiler makes it nice and easy for us.
For more information, look at "C# in Depth" or "LINQ in Action", both of which provide coverage of these topics.
@user190560 2009-10-16 15:44:11
If you don't mind can you update me with simple understandable example(if you have time).
@Pankaj 2011-12-13 18:31:38
Can you explain "Where is the definition of "GetQueryableProducts(); " ?" in Mr Reed Copsey reply
@afreeland 2013-12-16 18:27:06
Enjoyed the line of translating expressions to a query is "black art in itself"...a lot of truth to that
@Backwards_Dave 2018-12-05 02:39:23
Why would none of it be possible if you had used a delegate?
@Marc Gravell 2018-12-06 09:03:10
@Backwards_Dave because a delegate points (essentially) to IL, and IL isn't sufficiently expressive to make it reasonable to try to deconstruct the intent sufficiently to build SQL. IL also allows too many things - i.e. most things that can be expressed in IL couldn't be expressed in the limited syntax that it is reasonable to turn into things like SQL
@Reed Copsey 2009-10-16 16:08:43
Marc Gravell's answer is very complete, but I thought I'd add something about this from the user's point of view, as well...
The main difference, from a user's perspective, is that, when you use
IQueryable<T>
(with a provider that supports things correctly), you can save a lot of resources.For example, if you're working against a remote database, with many ORM systems, you have the option of fetching data from a table in two ways, one which returns
IEnumerable<T>
, and one which returns anIQueryable<T>
. Say, for example, you have a Products table, and you want to get all of the products whose cost is >$25.If you do:
What happens here, is the database loads all of the products, and passes them across the wire to your program. Your program then filters the data. In essence, the database does a
SELECT * FROM Products
, and returns EVERY product to you.With the right
IQueryable<T>
provider, on the other hand, you can do:The code looks the same, but the difference here is that the SQL executed will be
SELECT * FROM Products WHERE Cost >= 25
.From your POV as a developer, this looks the same. However, from a performance standpoint, you may only return 2 records across the network instead of 20,000....
@Pankaj 2011-12-13 18:28:03
Where is the definition of "GetQueryableProducts(); " ?
@Reed Copsey 2011-12-13 18:58:32
@StackOverflowUser It's intended to be any method that returns an
IQueryable<Product>
- would be specific to your ORM or repository, etc.@Pankaj 2011-12-14 04:49:59
right. but you mentioned where clause after this function call. So system is still unaware of the filter. I mean it still fetch all records of products. right?
@Reed Copsey 2011-12-14 19:05:30
@StackOverflowUser No - that's the beauty of IQueryable<T> - it can be setup to evaluate when you get the results - which means the Where clause, used after the fact, will still get translated into a SQL statement run on the server, and pull only the required elements across the wire...
@Pankaj 2011-12-15 04:04:52
can you please give some example, what to write in this function "GetQueryableProducts".
@Reed Copsey 2011-12-15 16:55:05
@StackOverflowUser It depends on what technology you're using. For example, with EF, you could just return an ObjectQuery<T>, ie: msdn.microsoft.com/en-us/library/bb345303.aspx
@testing 2012-11-01 03:52:49
Does it means that we are not in database till this line
IQueryable<Product> products = myORM.GetQueryableProducts();
and when this executesvar productsOver25 = products.Where(p => p.Cost >= 25.00);
we are going to database ?@Reed Copsey 2012-11-01 04:19:34
@Testing You're actually still not going to the DB. Until you actually enumerate the results (ie: use a
foreach
, or callToList()
), you don't actually hit the DB.@netfed 2013-12-22 07:02:42
@testing is correct. You can examine this by using the SQL Server Profiler
@Memet Olsen 2015-12-01 13:10:52
The point is that your query gets executed when the iteration starts (when you call
ToArray()
,ToList()
,First()
,SingleOrDefault()
, etc...), until that point you just build up the query by usingWhere(...)
,Take(...)
,Select(...)
.@Alexander Derck 2015-12-24 12:27:57
@ReedCopsey With an
IEnumerable
when you iterate over the results, will it still fetch all data first and then filter it compared to fetching only the data needed by iteratingÌQueryable
?@Reed Copsey 2015-12-24 16:23:11
@alexander no, with iqueryable, iterating will fetch all results, too.
@Zaker 2018-06-04 12:33:22
@ReedCopsey What in case if I have a combined query for IEnumerable. Something like this IEnumerable<Product> products = myContext.Products.Where(p => p.Cost >= 25.00). Will this be going out to be two calls. Will it work similar to IQueryable loading only filtered. How will this work.
@dove 2009-10-16 15:29:24
It allows for further querying further down the line. If this was beyond a service boundary say, then the user of this IQueryable object would be allowed to do more with it.
For instance if you were using lazy loading with nhibernate this might result in graph being loaded when/if needed.