Using IQueryable with Linq

Asked 16/10, 2009 at 15:23 Answered 25/2, 2016 at 14:0

287

What is the use of IQueryable in the context of LINQ?

Is it used for developing extension methods or any other purpose?

Zoologist answered 16/10, 2009 at 15:23 Comment(0)

578

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 an IQueryable<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:

 IEnumerable<Product> products = myORM.GetProducts();
 var productsOver25 = products.Where(p => p.Cost >= 25.00);

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:

 IQueryable<Product> products = myORM.GetQueryableProducts();
 var productsOver25 = products.Where(p => p.Cost >= 25.00);

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

Shoulder answered 16/10, 2009 at 16:8 Comment(13)

Where is the definition of "GetQueryableProducts(); " ? – Kall 13/12, 2011 at 18:28

@StackOverflowUser It's intended to be any method that returns an IQueryable<Product> - would be specific to your ORM or repository, etc. – Shoulder 13/12, 2011 at 18:58

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? – Kall 14/12, 2011 at 4:49

@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... – Shoulder 14/12, 2011 at 19:5

can you please give some example, what to write in this function "GetQueryableProducts". – Kall 15/12, 2011 at 4:4

@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 – Shoulder 15/12, 2011 at 16:55

Does it means that we are not in database till this line IQueryable<Product> products = myORM.GetQueryableProducts(); and when this executes var productsOver25 = products.Where(p => p.Cost >= 25.00); we are going to database ? – Curcuma 1/11, 2012 at 3:52

@Testing You're actually still not going to the DB. Until you actually enumerate the results (ie: use a foreach, or call ToList()), you don't actually hit the DB. – Shoulder 1/11, 2012 at 4:19

@Curcuma is correct. You can examine this by using the SQL Server Profiler – Xylograph 22/12, 2013 at 7:2

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 using Where(...), Take(...), Select(...). – Dionysus 1/12, 2015 at 13:10

@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? – Dithionite 24/12, 2015 at 12:27

@alexander no, with iqueryable, iterating will fetch all results, too. – Shoulder 24/12, 2015 at 16:23

What is important to mention here (I think no one talks about in comments) is the keyword "deferred". The difference between IQueryable and IEnumerable is IQueryable is meant to be used while no deferred execution has occurred. The answer is complete but I think it's worth to mention. Read this post for more information: tutorialsteacher.com/linq/linq-deferred-execution or this one dotnetcurry.com/linq/750/… – Shaft 23/10, 2020 at 15:7

209

In essence its job is very similar to IEnumerable<T> - to represent a queryable data source - the difference being that the various LINQ methods (on Queryable) can be more specific, to build the query using Expression trees rather than delegates (which is what Enumerable 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 and Provider - 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 implements IQueryable<Customer>. But we don't use that much directly; consider:

using(var ctx = new MyDataContext()) {
    var qry = from cust in ctx.Customers
              where cust.Region == "North"
              select new { cust.Id, cust.Name };
    foreach(var row in qry) {
        Console.WriteLine("{0}: {1}", row.Id, row.Name);
    }
}

this becomes (by the C# compiler):

var qry = ctx.Customers.Where(cust => cust.Region == "North")
                .Select(cust => new { cust.Id, cust.Name });

which is again interpreted (by the C# compiler) as:

var qry = Queryable.Select(
              Queryable.Where(
                  ctx.Customers,
                  cust => cust.Region == "North"),
              cust => new { cust.Id, cust.Name });

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:

var cust = Expression.Parameter(typeof(Customer), "cust");
var lambda = Expression.Lambda<Func<Customer,bool>>(
                  Expression.Equal(
                      Expression.Property(cust, "Region"),
                      Expression.Constant("North")
                  ), cust);

... Queryable.Where(ctx.Customers, lambda) ...

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:

 SELECT c.Id, c.Name
 FROM [dbo].[Customer] c
 WHERE c.Region = 'North'

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

Element answered 16/10, 2009 at 15:28 Comment(6)

If you don't mind can you update me with simple understandable example(if you have time). – Zoologist 16/10, 2009 at 15:44

Can you explain "Where is the definition of "GetQueryableProducts(); " ?" in Mr Reed Copsey reply – Kall 13/12, 2011 at 18:31

Enjoyed the line of translating expressions to a query is "black art in itself"...a lot of truth to that – Erymanthus 16/12, 2013 at 18:27

Why would none of it be possible if you had used a delegate? – Infielder 5/12, 2018 at 2:39

@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 – Element 6/12, 2018 at 9:3

tl;dr "And this is the point of Queryable / IQueryable<T>: it provides the entry-point for using expression trees." tutorialsteacher.com/linq/expression-tree From C# using LINQ to the Database. Right? – Unquestionable 2/8, 2022 at 14:54

Although Reed Copsey and Marc Gravell already described about IQueryable (and also IEnumerable) enough,mI want to add little more here by providing a small example on IQueryable and IEnumerable as many users asked for it

Example: I have created two table in database

   CREATE TABLE [dbo].[Employee]([PersonId] [int] NOT NULL PRIMARY KEY,[Gender] [nchar](1) NOT NULL)
   CREATE TABLE [dbo].[Person]([PersonId] [int] NOT NULL PRIMARY KEY,[FirstName] [nvarchar](50) NOT NULL,[LastName] [nvarchar](50) NOT NULL)

The Primary key(PersonId) of table Employee is also a forgein key(personid) of table Person

Next i added ado.net entity model in my application and create below service class on that

public class SomeServiceClass
{   
    public IQueryable<Employee> GetEmployeeAndPersonDetailIQueryable(IEnumerable<int> employeesToCollect)
    {
        DemoIQueryableEntities db = new DemoIQueryableEntities();
        var allDetails = from Employee e in db.Employees
                         join Person p in db.People on e.PersonId equals p.PersonId
                         where employeesToCollect.Contains(e.PersonId)
                         select e;
        return allDetails;
    }

    public IEnumerable<Employee> GetEmployeeAndPersonDetailIEnumerable(IEnumerable<int> employeesToCollect)
    {
        DemoIQueryableEntities db = new DemoIQueryableEntities();
        var allDetails = from Employee e in db.Employees
                         join Person p in db.People on e.PersonId equals p.PersonId
                         where employeesToCollect.Contains(e.PersonId)
                         select e;
        return allDetails;
    }
}

they contains same linq. It called in program.cs as defined below

class Program
{
    static void Main(string[] args)
    {
        SomeServiceClass s= new SomeServiceClass(); 

        var employeesToCollect= new []{0,1,2,3};

        //IQueryable execution part
        var IQueryableList = s.GetEmployeeAndPersonDetailIQueryable(employeesToCollect).Where(i => i.Gender=="M");            
        foreach (var emp in IQueryableList)
        {
            System.Console.WriteLine("ID:{0}, EName:{1},Gender:{2}", emp.PersonId, emp.Person.FirstName, emp.Gender);
        }
        System.Console.WriteLine("IQueryable contain {0} row in result set", IQueryableList.Count());

        //IEnumerable execution part
        var IEnumerableList = s.GetEmployeeAndPersonDetailIEnumerable(employeesToCollect).Where(i => i.Gender == "M");
        foreach (var emp in IEnumerableList)
        {
           System.Console.WriteLine("ID:{0}, EName:{1},Gender:{2}", emp.PersonId, emp.Person.FirstName, emp.Gender);
        }
        System.Console.WriteLine("IEnumerable contain {0} row in result set", IEnumerableList.Count());

        Console.ReadKey();
    }
}

The output is same for both obviously

ID:1, EName:Ken,Gender:M  
ID:3, EName:Roberto,Gender:M  
IQueryable contain 2 row in result set  
ID:1, EName:Ken,Gender:M  
ID:3, EName:Roberto,Gender:M  
IEnumerable contain 2 row in result set

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

--IQueryableQuery1 
SELECT 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Employee] AS [Extent1]
WHERE ([Extent1].[PersonId] IN (0,1,2,3)) AND (N'M' = [Extent1].[Gender])

--IQueryableQuery2
SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Employee] AS [Extent1]
    WHERE ([Extent1].[PersonId] IN (0,1,2,3)) AND (N'M' = [Extent1].[Gender])
)  AS [GroupBy1]

IEnumerable execution part

--IEnumerableQuery1
SELECT 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Employee] AS [Extent1]
WHERE [Extent1].[PersonId] IN (0,1,2,3)

--IEnumerableQuery2
SELECT 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Employee] AS [Extent1]
WHERE [Extent1].[PersonId] IN (0,1,2,3)

Common script for both execution part

/* these two query will execute for both IQueryable or IEnumerable to get details from Person table
   Ignore these two queries here because it has nothing to do with IQueryable vs IEnumerable
--ICommonQuery1 
exec sp_executesql N'SELECT 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Person] AS [Extent1]
WHERE [Extent1].[PersonId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

--ICommonQuery2
exec sp_executesql N'SELECT 
[Extent1].[PersonId] AS [PersonId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Person] AS [Extent1]
WHERE [Extent1].[PersonId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=3
*/

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 and IEnumerable<Employee> GetEmployeeAndPersonDetailIEnumerable of SomeServiceClass contains one common line in linq queries

where employeesToCollect.Contains(e.PersonId)

Than why is the AND (N'M' = [Extent1].[Gender]) part is missing in IEnumerable execution part, while in both function calling we used Where(i => i.Gender == "M") inprogram.cs`

Now we are in the point where difference came between IQueryable and IEnumerable

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,

only those rows populated by sql server which could be valid by the whole linq query execution
helps sql server performance by not selecting unnecessary rows
network cost get reduce

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 column gender.

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 over IEnumerable 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 than IEnumerable query(IEnumerableQuery2) which is much more acceptable in my point of view.

Sarasvati answered 25/2, 2016 at 14:0 Comment(2)

I know I am coming late to the party, but I was wondering if you have any thoughts about the positive/benefits of using IEnumerable and on which scenario do you think it could be beneficial to use over IQueryable. – Farce 4/4, 2023 at 17:6

@Farce short ans->https://mcmap.net/q/79863/-what-is-the-difference-between-iqueryable-lt-t-gt-and-ienumerable-lt-t-gt, long ans: If you are using dbcontext or query to any db directly use IQueryable, if searching within list of objects or xml(in memory) use ienumerable – Sarasvati 5/4, 2023 at 11:6

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.

Futures answered 16/10, 2009 at 15:29 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags