I'm having problems understanding IQueryable<>

Question

So I'm trying to understand IQueryable<>. A tutorial I'm reading suggests using it but not really sure why. The code simply returns some values using LINQ to SQL. I've done this plenty of times in the past, but not using IQueriable<>

Why use it with my functions that return more than 1 value?

Here's my code:

public IQueryable<Items> GetItems()
    {
        return from item in db.Items
               where item.IsActive == true
               orderby item.ItemNumber
               select item;
    }

Answer 1

IQueryable represents the query as an expression tree without evaluating it on the server. This lets you specify further processing before actually generating SQL.

In the above case, this means that you can do stuff with the result of calling GetItems(), and have the original query and the extra stuff sent as a single query:

var recentItems = from item in GetItems()
                  where item.Timestamp > somedate
                  select item;

foreach (var item in recentItems)
{
    // Do something with an active recent item.
}

Nothing is sent to the server until we try to consume the result in the foreach loop. At that point, the LINQ-to-SQL provider assesses the entire expression, including the bits generated inside GetItems() and the bits specified after, and emits a single SQL statement that selects all items that are both active and recent.

To clarify a technicality, the IQueryable<T> is an IEnumerable<T>, and its provider computes the final SQL when you try to invoke the GetEnumerator() method on it. You can either do this explicitly by calling it, or implicitly by using it in a foreach statement. Also, extension methods like ToArray() will do one of these internally, thus producing the same effect.

Answer 2

An IQueryable (before it is enumerated) is not the results them selves but the logic used to return those results.

To use a LINQ2SQL example... imagine you return an IQueryable of Clients. Under the hood that will not be a list of clients (until you ToList() it) but will actualy be a SQL Query like so:

SELECT * FROM [Clients]

Now this is handy because we have not hit the database yet! So lets say when that IQueriable comes back we want to refine it down to Clients called "Bob" we can do:

var clients = GetClients().Where(c => c.Name == "Bob");

Now the IQueriable looks like this under the hood:

SELECT * FROM [Clients] WHERE Name = 'Bob'.

Now when I do clients.ToList(), that query will run, the database is hit, and I have a list of clients called bob without having to have selected all clients then trawl through them in memory, or perform 2 separate database hits.

For an example of this biting you in the behind try and get to child elements when your datacontext has dropped out of scope(e.g: run your select inside a using statement). This is where load options come in handy, in LINQ2SQL.

Hope that helps