Using IQueryable with Linq

Question

What is the use of IQueryable in the context of LINQ   Is it used for developing extension methods or any other purpose

User · Answer

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.

User · Answer

In essence its job is very similar to IEnumerable lt T gt  - 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 lt Customer gt   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   gt  cust Region     North                    Select cust   gt  new   cust Id  cust Name       which is again interpreted  by the C  compiler  as    var qry   Queryable Select                Queryable Where                    ctx Customers                    cust   gt  cust Region     North                  cust   gt  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 lt Func lt Customer bool gt  gt                     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 lt T gt   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

User · Answer

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 lt T gt   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 lt T gt   and one which returns an IQueryable lt T gt    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 lt Product gt  products   myORM GetProducts     var productsOver25   products Where p   gt  p Cost  gt   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 lt T gt  provider  on the other hand  you can do    IQueryable lt Product gt  products   myORM GetQueryableProducts     var productsOver25   products Where p   gt  p Cost  gt   25 00     The code looks the same  but the difference here is that the SQL executed will be SELECT   FROM Products WHERE Cost  gt   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

User · Answer

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

[c#] Using IQueryable with Linq

Examples related to c#

Examples related to linq

Examples related to iqueryable