IQueryable: Performance boost over remote Data Access

Csharp
Csharp

IQueryable: Performance boost over remote Data Access

Welcome to CodeSpread!

If we also follow the inheritance principle like OOPS programming, we need to extend our previous discussion “IEnumerable: Power behind Collections”. So this will lead us to write something about IQueryable.

What is an IQueryable Interface?

We can define it as functionality attached to an element which is used to evaluate queries against a known data source of unknown data type. It is available as


public interface IQueryable : IEnumerable

As we can see, it extends IEnumerable so we can easily say that all the inherent functionality of IEnumerable is available to IQueryable.

What is the difference?

IEnumerable:

IEnumerable works with in-memory collection. It is a read-only forward-only collection.

IQueryable:

IQueryable works with remote data source.

For example:

Case 1 :If the case is to iterate through the in-memory collection, use IEnumerable,

Case 2 :if the case is to do any manipulation with the collection like Dataset, use IQueryable.

What are the properties available?

Provider : Points to the query provider that is associated with the data source like LINQ to SQL provider.

Expression: Points to a query expression which is associated with the instance of IQueryable that can be understood by the given query provider.

ElementType: Points to the type of the element(s) that are returned when the expression tree associated with this instance of IQueryable is executed.

IQueryable is not alone and comes as a package with IEnumberable. Enumeration causes the expression tree associated with an IQueryable object to be executed,but using IQueryable can save a lot of resources.

Example

Ref: http://sladescross.wordpress.com/2010/07/30/linq-iqueryable-interface/

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, and one which returns an IQueryable. Say, for example, you have a Products table, and you want to get all of the products whose cost is >$25.


IEnumerable 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 the program. Program then filters the data. In essence, the database does a “SELECT * FROM Products”, and returns EVERY product.

With the right IQueryable provider,


IQueryable 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 a performance standpoint, It will only return 2 records across the network instead of 20,000

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

1 Comment

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • Simply LINQ: What,Why and How.Simply LINQ: What,Why and How.What is LINQ? LINQ is Language-Integrated Query (LINQ). It is primarily an extension to the language's capability where it adds query expressions which processes objects and data. It is a one s...
  • C#: Anonymous Type is not anonymous!C#: Anonymous Type is not anonymous!Why Anonymous Type? Anonymous types provide you the ease of having set of properties (Read only) into a single object without knowing the type. It’s the job of compiler to generate the type inform...
  • C#: Useful JSON in .NetC#: Useful JSON in .NetWhy JSON? All web applications revolve around large chunk of data and availability of that data to our application is of utmost importance. To do that, we have options like fetch data from databas...
  • 23 Dec: Must Read Codes [11-15]23 Dec: Must Read Codes [11-15]We are sharing few must read codes/concepts which are required now and then. Each of these codereads doesn’t require a full length descriptive article so combining them into groups of five. CodeRe...
  • IEnumerable: Power behind CollectionsIEnumerable: Power behind CollectionsOne of my favorite questions which I ask very frequently to test the candidate is “What is Collections?” And I get almost all kind of answers, possibly available on planet Mars. But one answer w...
  • SQL: Usage of char, varchar and varchar(MAX)SQL: Usage of char, varchar and varchar(MAX)These are character data types in SQL. We will take an example and describe each type. Later we determine the cases where one of them can be applied. [code] Create Table testable( Name char(20...
  • My first experience with LISTS in C#My first experience with LISTS in C#Hi guys, this is Sathish, Lets discuss the concepts of lists. These are the concepts which I have planned to discuss with you guys today. What is a List and how it is advantageous than array...
  • OLAP : On-line Analytical Processing OLAP : On-line Analytical Processing What is OLAP? We know that there are two kinds of database applications available, OLAP and OLTP. Before we go for OLAP, I would like to tell you something about OLTP. OLTP is On-line Transaction...