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.

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.

What is M.S.I.L. or Microsoft Intermediate Language

MSIL stands for Microsoft Intermediate Language. When a programmer writes a program using VB or C# ,the compiler of that language converts it into an Intermediate code or Microsoft Intermediate Language and sends it to CLR which further converts it into native code for the machine.

Main components of .Net Framework

Base Class Library(BCL) and Common Language Runtime(CLR) are the main components of .Net Framework. BCL is the collection all the basic classes which are required for apllication development. These include classes like database classes,data access etc. CLR is used to convert the MSIL into code which is native to the machine.

Difference between Page.ClientScript.RegisterStartupScript and ClientScript.RegisterClientScriptBlock ?

RegisterStartupScript places the script at Bottom of page and RegisterClientScriptBlock at the Top of the page.
So placing your script on the page depends upon type of script.If the script uses a control than it should be placed at the bottom of the page rather than as controls will not be formed by the time script is fired.