Lazy loading in Entity framework 

Today i will be writing an article on the concept of the lazy and eager loading of the data in the entity framework. This concept is one of the most important concepts related to the database hits from the application, which can affect the performance of the application due to the basics behind these two. So let's start with it.

In entity framework, it is quite normal situation to have entities that are related to each other. For ex : we may have a User table that contains basic user information like UserId, Username, Password, emailaddress etc. and another table UserDetails which contains contact details of the same user with attributes like Id, UserId(Foreign key from Users table), Contact Address, State, City etc. .So here, the user table is having one-to-many relation with the UserDetails table.

Entity framework provides us with an ability in which we can load the data of a parent entity as well as its related child entity at the same time i.e. when we load the data of the user table, we also get the related data of each User from the UserDetails table. This is know as the Eager Loading.

On the other hand, if we do not want to load the related entity data at the same time when main entity is being fetched, we use the concept of Lazy loading.

To start with this process, we will be creating a sample project and add an .edmx model into it with two entities User (parent entity) & UserDetails(child entity).   We will also be using the SQL Server Profiler to check how the queries are executed in the database at the back end.

1. So, hope you have setup the project and added the two entities into the database and edmx model into the sample project like below :

2.  Next, start the SQL Profiler and select File-> New Trace & start a new trace. Connect using the settings of the SQL server.

3. Set the basic details like Trace Name as per your requirements and keep the Events Selection Tab settings as default. Click on Run.

The above 3 steps will be common to the Lazy/Eager loading. After that they will differ.

Here comes the Lazy loading

4.  Add the following code in your application. I have created a console based application for my ease.

This is the case where the Lazy loading is enabled by default. You can check the same in the 

Edmx.designer.cs file also.

5.  Now run the application. In the above code, note the step 1. At this step, the data of only the main entity i.e. the Users is loaded and not that of the UserDetails. The data of the related entity is loaded when the step 2 is being executed i.e. when the nested foreach loop is executed.

Let's see what the SQLProfiler has got in store for us, for the queries executed for this process. See the screenshots below:

In above image, the highlighted line displays the query that gets executed for the outer foreach which fetches the details of the Users entity. The query is being displayed at the bottom.

In the second image, you can see that the selected query fetches the related data of the first record of the 

main entity i.e. this query fetches the records from UserDetails which are having the fkUserId as 1. You can see the query at the bottom. Similarly, the following two queries will fetch the related records of the User entity records having id's as 2 and 3. You can check the same by changing the selection.

This means that to fetch the data of the related entity, database calls are being made again and again, after the data for main entity has been fetched, which could badly hamper the efficiency of the application. So we need to take care of the scenarios when we should enable this feature .

In above case, if you do no need the UserDetails, you can  remove the foreach loop . You can remove the inner foreach loop and you will see that the SQL will not fetch the related entity data.

And now the Eager Loading

6. Now we will modify the code that we had in our Step 1 to

var userData = objSampleDBEntities.Users.Include("UserDetails");

Here we are explicitily mentioning to fetch the data of the related entity, when the data of the main entity is being fetched.

7. Start a new trace by following the steps that we performed earlier & run the application

8. Now observe the SQL profiler details. See the screenshot below :

Now what happens in this case is that the SQL query is generated using the JOIN and it fetches the data of the related entity i.e. the UserDetails along with the main User entity data. You can see the query at the bottom of the selection which shows the join being applied at the back end by the sql server. This is known as Eager loading which means loading the related entity data along with the data of the main entity.

Now the question arises when to use what option :

1. Use Eager loading when the data in related entities is too much to be loaded at the cost of the queries being made to the database. i.e. fetch all of them at once along with the main entity using the eager loading.

2. Use lazy loading when you only need the main entity data to be fetched and you know that the related data will not be required.

So I hope this article explains the basic concept of the lazy & eager loading.

Get nth highest record from table

This is one of the most common questions asked for database related questions in interviews. One solution is there. I feel there can be a better solution(in terms of performance issues) but i feel this one is also acceptable. So here it is :



Select Min(Salary) from tbSalary where Salary In(Select top 3 Salary from tbSalary order by salary desc)



Here, I have fetched the 3rd highest record from the salary table. You can replace "3" with the record number you want to get.

Firstly, the inner query runs and orders the salary column in descending order. Than the inner query selects Top 3 records from the ordered list.

Secondly, the outer query fires and select the minimum salary from the list selected by the inner query, which gives the required record.

And that's it, u get the result....

One more thing, if u have a better solution, than do tell me here.....Happy querying.....

Common Type System ot C.T.S.

Different langauages in .Net framework like VB and C# use different syntax to declare a data type. For Ex: an integer is declared as int in C# and integer in VB. To avoid any mismatch of these two declaration syntax, a common class System.Int32 has been defined to interpret these. Similarly for other data types like string , datetime , arrays etc , base classes have been defined. These all base classes are derived from a single base type System.Object , which form a system called as Common Type System or C.T.S.

What is difference between Code behind and Inline Coding technique ?

Code behind technique uses coding technique in which code is placed in separate file with extension as .cs and design in separate file with extension as .aspx while Inline coding places both the code and the design in same page with .aspx extension.

Can we use more than one web.config file in our application ?

Yes , we can use more than one web.config in our Apllication but the condition is that we can use them in two different folders only. This implies that the web.config files must be in different folders of our application .

What is IIS ?

I.I.S. stands for Internet Information Services. This server is required to fullfill the client request to the server for pages with .Net extension .aspx which is different from other pages like HTML,CSS. It also handles request for .asmx (Web services), .ascx (Web user controls) and other .Net extensions.

Page.IsPostBack property

It is a boolean property which returns either true or false. If it is false, it implies that the Page is loading for the first time otherwise it implies that it is a Post back request for the page.