IEnumerable Vs IQueryable

When working with a database, sometimes the situation may become confusing. Some questions may develop such as what we should use to fetch data from SQL Server, should I go with IEnumerable or should I go with IQueryable?

So here I am trying to show the difference between these two and you can use them depending on your requirements.

IEnumerable

1. System.Collection namespace
2. No Base Interface
3. Supports Deferred Execution
4. No Lazy Loading
5. No Custom Query support
6. Suitable for LINQ to Object and LINQ to XML Queries
7. When querying data from a database IEnumerable executes a SELECT query on the server side. It loads the data in-memory on the client side and does a filter.

Now we will see how IEnumerable works in real life.

I have a Table Employee in my database as in the following:


Figure 1: Employee table

Now I created a new application and right-click on the solution then select Add New Item -> LINQ To SQL Classes -> Employee.dbml.

Now write your query to select records from the Employee Table as in the following:


Figure 2: Selecting records from Employee table

1. EmployeeDataContext dc = new EmployeeDataContext();  
2. IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));  
3. list = list.Take<Employee>(10);  

Now debug and you will see the following:


Figure 3: After debugging

The following is the SQL Query corresponding to the preceding statement:

1. SELECT [t0].[ID], [t0].[Name], [t0].[Email], [t0].[Country]  
2. FROM [dbo].[Employee] AS [t0]  
3. WHERE [t0].[Name] LIKE @p0  

There is no Select TOP statement. So the filter will be applied on the client side after getting all the records from the database.

IQueryable

1. System.Linq namespace.
2. Best to query data from Out-Memory.
3. When querying data from a database IQueryable executes a SELECT Query on the server side with all filters.
4. Suitable for LINQ To SQL Queries.
5. Supports Deferred Execution.
6. Supports custom query using Create Query and Execute methods.
7. Supports Lazy Loading.

Now we will see it programmatically as in the following:


Figure 4: Code Snippet

1. EmployeeDataContext dc = new EmployeeDataContext();  
2. IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));  
3. list = list.Take<Employee>(10  

Now debug the query:


Figure 5: After debugging

1. SELECT TOP (10) [t0].[ID], [t0].[Name], [t0].[Email], [t0].[Country]  
2. FROM [dbo].[Employee] AS [t0]  
3. WHERE [t0].[Name] LIKE @p0  

So now you can see the difference between these 2 and use it depending on your business needs.


Image 6. 

Partial Classes In C# with Real Example

Partial Class in C# 

In this article, I explain partial classes in C# language with examples. A partial class splits the definition of a class over two or more source (.cs) files. You can create a class definition in multiple physical files but it will be compiled as one class when the calsses are compiled.

Suppose you have a "Person" class. That definition is divided into the two physical source files, Person1.cs and Person2.cs. Then these two files have a class that is a partial class. You compile the source code then create in a single class. Let's see that in Figure 1.1. 

 

 
Figure 1.1 : Partial class and compiled class

Advantages of a partial class

Here is a list of some of the advantages of partial classes.

1. You can separate UI design code and business logic code so that it is easy to read and understand. For example you are developing an web application using Visual Studio and add a new web form then there are two source files, "aspx.cs" and "aspx.designer.cs" . These two files have the same class with the partial keyword. The ".aspx.cs" class has the business logic code while "aspx.designer.cs" has user interface control definition.
    
2. When working with automatically generated source, the code can be added to the class without having to recreate the source file. For example you are working with LINQ to SQL and create a DBML file. Now when you drag and drop a table it creates a partial class in designer.cs and all table columns have properties in the class. You need more columns in this table to bind on the UI grid but you don't want to add a new column to the database table so you can create a separate source file for this class that has a new property for that column and it will be a partial class. So that does affect the mapping between database table and DBML entity but you can easily get an extra field. It means you can write the code on your own without messing with the system generated code.
    
3. More than one developer can simultaneously work the code for the same class.
    
4. You can maintain your application better by compacting large classes. Suppose you have a class that has multiple interfaces so you can create multiple source files depending on interface implements. It is easy to understand and maintain an interface implemented on which the source file has a partial class. Let's see the following code snippet.
   1. public interface IRegister    
   2. {    
   3.     //Register realted function    
   4. }    
   5. public interface ILogin    
   6. {    
   7.     //Login related function    
   8. }    
   9. //UserRegister.cs file    
   10. public partial classUser : IRegister, ILogin    
   11. {    
   12.     //implements IRegister interface    
   13. }    
   14. //UserLogin.cs file    
   15. public partial classUser    
   16. {    
   17.     //implements ILogin interface    
   18. } 

Points that you should be careful about partial classes

There are some points that you should be when you are developing a partial class in your application.

1. You need to use partial keyword in each part of partial class.
2. The name of each part of partial class should be the same but source file name for each part of partial class can be different.
3. All parts of a partial class should be in the same namespace.
4. Each part of a partial class should be in the same assembly or DLL, in other words you can't create a partial class in source files of a different class library project.
5. Each part of a partial class has the same accessibility.
6. If you inherit a class or interface on a partial class then it is inherited on all parts of a partial class.
7. If a part of a partial class is sealed then the entire class will be sealed.
8. If a part of partial class is abstract then the entire class will be an abstract class.

Example

I will develop an example that explains how to use a partial class in your project. Suppose you are working with LINQ to SQL in your application. So you create a data context, in other words a .dbml file and drag and drop the necessary tables. Each table creates a partial class in the data context designer file and each table field becomes a property for the table. Suppose you have a "Person" table that has the three fields "Id","Name" and "DateOfBirth" and you want to show the age of each person in a grid view. What will you do? If you add a new column to the table for age in database for the "Person" table then it fails the normalization rule so you should not do that. If you add a new property to auto-generated code then it will not be mapped to the database. So you need to create a partial class portion in a separate source file that has the "Age" property. This "Age" property calculates the age of the person when you bind a person list to the grid view. Let's see each step-by-step.

1. Create a "Person" table in the database.
   
   You need to create a person table in the database that has the three fields "Id","Name" and "DateOfBirth". The "Id" field is the primary key.
   1. CREATE TABLE Person  
   2. (  
   3. Id int identity(1,1) primary key,  
   4.   
   5. Name nvarchar(50),  
   6.   
   7. DateOfBirth Date defaultgetUtcDate()  
   8.   
   9. )
2. Create a web application from Visual Studio.
    
3. Right-click on the project in the Solution Explorer then go to "Add" and click on "Class".
    
4. Choose "LINQ to SQL Classes" from the list and provide the name "Person" for the DBML name. Then click on "Add".
    
5. Drag the User table from the database in the Server Explorer and drop onto the O/R Designer surface of the "Person.dbml" file.
   
   
   Figure 1.2: Person entity
   
   Now you can open the "Person.designer.cs" file. In the file the "Person" partial class has been created for drag and drops a "Person" table from database on O/RM surface.
    
6. Create a partial class part that has the "Age" property to calculate the age. This file is named "PersonExtension.cs".
   1. using System;  
   2. namespace PartialClassExample  
   3. {  
   4.    public partial class Person  
   5.     {  
   6.        public int Age  
   7.         {  
   8.            get { returnConvert.ToInt32(System.DateTime.UtcNow.Date.Year - _DateOfBirth.Value.Year); }  
   9.         }  
   10.     }  
   11. }
7. Create a UI design to show a person's details in the grid view.
   1. <%@Page Language="C#" AutoEventWireup="true" CodeBehind="PersonUI.aspx.cs" Inherits="PartialClassExample.PersonUI"%>  
   2. <!DOCTYPE html>  
   3. <html xmlns="http://www.w3.org/1999/xhtml">  
   4. <head id="Head1" runat="server">  
   5.    <title></title>  
   6. </head>  
   7. <body>  
   8.    <form id="form1" runat="server">  
   9.    <div>  
   10.        <asp:GridView ID="gridPerson" runat="server">  
   11.        </asp:GridView>  
   12.    </div>  
   13.    </form>  
   14. </body>  
   15. </html>
8. Write code for the "Page_Load" event to bind a grid view by person list in the code behind file.
   1. using System;  
   2. using System.Linq;  
   3. namespace PartialClassExample  
   4. {  
   5.    public partial class PersonUI : System.Web.UI.Page  
   6.     {  
   7.        protected void Page_Load(object sender, EventArgs e)  
   8.         {  
   9.            using (PersonDataContext context =new PersonDataContext())  
   10.             {  
   11.                var query = from person in context.GetTable<Person>()  
   12.                             select new  
   13.                             {  
   14.                                 person.Id,  
   15.                                 person.Name,  
   16.                                 person.DateOfBirth,  
   17.                                 person.Age  
   18.                             };  
   19.                var content = query.ToList();  
   20.                 gridPerson.DataSource = content;  
   21.                 gridPerson.DataBind();  
   22.             }  
   23.         }  
   24.     }  
   25. }
9. Run the application, you will see the Age column in the grid view that shows the age of each person. Let's see that in Figure 1.3.
   
   
   Figure 1.3: Output