Tutorial :I'm having problems understanding IQueryable



Question:

So I'm trying to understand IQueryable<T>. 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 IQueryable<T>

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;      }  


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


Solution: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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »