Understanding LINQ to SQL and IQueryable / IEnumerables C# .NET

I am often asked what the difference is between IQueryable, IEnumerable and LINQ. This is a loaded question, but I will try my best to explain it in this blog. This blog will focus mostly on each from a database point of view but we will NOT be going over using any ORM providers such as Entity Framework.

What exactly is an IQueryable and LINQ -> SQL?

Well, that’s a great question. An IQueryable actually extends IEnumerable which means that it can do iterations (for each loops) in the same way that IEnumerable can. IQueryable is very often used to build up queries to be used to call a database. Since an IQueryable is an in memory query (in other words has not been executed to the database yet, you can chain filters to the queryable before it executes.

For example, if you wanted to do a select on a product by it’s name, you would build the select query and then the where clause (filter) using LINQ to SQL (now known as LINQ to Entities).

Note: An IQueryable is NOT calling the database. To call the database you must turn it into a concrete object! If you do a .ToList() at the end of your query, that will make the call to the database. Until then it is just a string SQL statement in memory. Common functions that will trigger a call to the database are Where(), ToList() and Select().

 So what is and how do you use LINQ to SQL

LINQ to SQL (LINQ to Entities) ORM (Object relational mapping) is a query language build within the .NET framework which provides the developer the ability to query a database without having intimate understanding of the TSQL language. This is done by providing a programmatic syntax (using Lamda expressions) similar to C#. This code then gets compiled down into a query which can be sent to the database and return the results to the developers code.

A very basic example would be something like this. Suppose you have a database structure similar to the one below. A pretty simple ordering database schema. The Products table is the main system and you will notice that there is a one to many relationship between Product and Category.

linq-sql-diagram

Now, with the given diagram above, you can use LINQ to query the database using entity framework (I will not be going into the intricacies of EF at this time). If anyone comments below that they would like me to, I certainly can.I will also be doing many intro courses on my you tube channel if you are interested.

Here is a great image that explains what each syntax keyword means.

linq-query-syntax

Reference: TutorialsTeacher.com

You will notice that strList is considered an IQueryable. This is because until the select s is called, the query is just an in memory TSQL script and has NOT yet been executed. This means you cannot pull any actual data from it until the select is called.

So How do we use it?

Lets say that you want to query against the database for a product that had the name of Beverages. How would you do that in LINQ? Simple. Here is the code.

 

linq-to-sql-example-1

The syntax here (which I cannot remember offhand what it is called) will give you exactly that. The code is creating an entity context and using it to retrieve data from the products table. Then you are defining a filter condition. You are saying get me all the products where the CategoryName column in the Category link table has a value of Beverages.

Note: If you write this syntax out and leave out the ‘select p’, you will not be running the query. It will still be in memory as mentioned above and ‘p’ will be null.

This syntax is actually very similar to raw SQL actually, so if you are looking for something that is expressive for a DBA to look at, this might be the syntax for you.

The other syntax (and my favorite) are called LINQ Methods or Chaining. Using the same database schema example as above lets say that now we want to query the database for a product with a ProductName of Toy 1. That syntax may look something like this:

linq-sql-example-2

Since this article is not about EF, we will skip the assignment and saving to the database part. After creating the database context, the LINQ calls the method Single on the database where ProductName equals Toy 1Single means that it will only return one record. If more than one record is found, an exception will be thrown stating that more than one record is found. If you want to return the first record use the FirstOrDefault method.

I hope everyone has a better understanding of the diferrence between IQueryable and LINQ to Entities. Next article I will explain normal IEnumerables and how they differ from IQueryable.

So When to Use IQueryable and When to Use IEnumerable?

Excellent question, I’m glad you asked! Since IQueryable extends IEunmerable, it would be a good assumption that they have all the same capabilities. That assumption would absolutely correct. The key difference is how and when they handle and manipulate the data.

To follow my videos on C# basic and advanced topics, click here

IEnumerable in this context is used whenever you want to return the data from the database, until you convert an IQueryable to an IEnumerable, the query hasn’t been sent to the database for execution. IEnumerable is also best use when you already have an list of concreate objects in the form of  IList, Dictionary, HashTable etc…

At the end of the day, you really want to make sure that you don’t mix them up when building your call to the database otherwise you can see some very undesirable results.

A great example of this is when you want to do a similar where clause as before:

var products = databaseContext.Products.ToList();

products.Where(x => x.ProductName ==”Toy 1″);

Although this seems like you are doing the right thing by filtering the Product by name, the problem is that the first line returns ALL the products and then you filter the local list of all the products to see what names are ‘Toy 1’.

Obviously, this is not ideal if you have 100,000 records and you want 1 toy. Instead, you should build up your query to send the filter within the query to execute. Here is a better way to do it.

var products = databaseContext.Products.Where(x => x.ProductName ==”Toy 1″).ToList();

 

 

I hope this has all made sense to everyone. Please feel free to leave some comments and let me know if there is anything I can clarify and or things I can blog about in the future.

 

As always, To follow my videos on C# basic and advanced topics, click here

Happy Coding :).

Leave a Reply

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