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.

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.

Check for blank space in string using Javascript

Following is a javascript function that helps to check for a blank space in a string :

<script type="text/javascript" language="javascript">

function check()
{
var Pattern=new RegExp(" ")
var val=pattern.exec(document.getElementById("<%=TextboxID.ClientID").value)

if(val==null)
{
alert("No space found");
}
else
{
alert("Blank space not allowed")
}
}

</script>


Finally this function can be called on event on which we want to check for the blank space in the string

Ajax Password Strength Extender

The Ajax password strength extender can ba applied to check for the strength of the password that is choosen by a user on your website. This helps the user to choose a strong password which increases the safety of the password theft also.




Steps to use Password Extender :



1. Add a Textbox on which the Extender is to be applied , on your Webform.

2. Apply the Password strength Extender to the Textbox.

3. Set the following properties for the Password Extender :



TargetControlID : The ID of the control on which Password strength is to be applied.

StrengthIndicatorType : Either text can be displayed or BarIndicator can be used.

PreferredPasswordLength : Length of the Password that should be used.

MinumumUpperCaseCharacters: Specifies the minimum Upper case alphabets required
MinumumLowerCaseCharacters: Specifies the minimum Lower case alphabets required
The above two properties are required only if the RequiresUpperandLowerCaseCharacters is specified as True.
Prefix Text : The text that is required to specify the strength of Password to the user.
Ex: Your Password strength is :
MinimumNumericCharacters : Specifies the minimum number of numeric values that should be used in Password.
MinimumSymbolCharacters : Specifies the minimum number of Special Symbols that should be used om Password

CalculationWeightings : Specifies the percentage strength of the various criterias used in the password. It reqiures 4 values with total of 100 is the maximum.

Ex : CalculationWeightings : 50;20;20;10 means Length forms 50 percent of total strength of the password ,Numeric forms 20 percent , Casing forms the 20percent and Special Symbols form the rest of the strength

DisplayPosition : The position where the Password strength should be displayed.

RequiresUpperandLowerCaseCharacters : Specifies whether the uppercase or lower caser alphabets are required.

HelpStatusLabelID : Specifies the ID of the Label control where the strength criteria is to be displayed to the user.

HelpHandleCSSClass :Specifies the CSS class for the Help control used to display the requirements.

HelpHandlePosition: Specifies the position of the Help Label being used.

TextStrengthDescription : Specifies the text for the strength level for the control

Ex : Average,Good,Very Good, Excellent. This property can be used when StrengthIndicatorType used is Text.

Ajax Mutually Exclusive Checkbox Extender

The Mutually Exclusive Checkbox extender can be applied to a collection of Checkobxes when we want the user to select only one checkbox from a collection of many. This can not be achieved with the normal checkbox (possible but not as possible as with the extender).

Steps to use Mutually Exclusive Checkbox Extender :

1. Add 2-3 Checkboxes on your webform.
2. Add the Mutually Exclusive Checkbox Extender to all the checkboxes.

3. Specify the following properties :

TargetControlID : The ID of Checkbox on which the Extender is to be applied.

Key : A common name given to all the extenders which makes all the checkboxes mutually exclusive for a given key name.

This is all that has to be done for using a Mutually Exclusive Checkbox Extender and your controls are ready to be used.

Ajax Slider Extender

The Slider Extender is applied on a Textbox control and is generally used to allow a user to select a value from the specified range only. For Ex: If we want the user to enter a value from 0 to 500 , we can specify a slider extender to let the user select from that range. The selected value can be displayed in a textbox or label deopending upon the requirements.

Steps to use Slider Extender :

1. Add a Textbox control to your webform .

2. Add the Slider Extender to the Textbox.

3. Specify the following properties :

TargetControlID : The ID of Textbox on which the Extender is to be applied

Minimum Value : The minimum value the slider should be starting from. (By default 0).

Maximum Value : The maximum value the slider can have (By default 100) .

BoundControlID : The ID of the control where you want to display the slider selected value

Steps : The number of steps you want the slider to be divided into.

Decimals : The number of decimal points upto which the value should be displayed

Apart from these , HandleCssClass to specify the CSS class to be used for handle , HandleImageURL for rendering an image for the handle, RailCssClass for CSS class for the slider and TooltipText for the Tooltip can also be used.

What is SQL

SQl or Structured Query Language is Database language that is used to create, update ,delete and persorm other types of operations on a database . SQL uses queries that can be used to perform the required operations on the database. The SQL queries are divided into two main categories :

1. Data Definition Language or DDL : The DDL statementsare used to create and manupulate the structure of the database objects including Tables, views etc.

2. Data Manipulation Language or DML : The DML statements are used to work with the database within the tables created zs compared to the DDL which works with structure of the database objects.

What is .Net Framework

.Net Framework is a platform or collection of tools and languages, used for the development of web based as well as window based applications. .Net framework is mainly comprised of two main components named Base Class Library and Common Langauge Runtime.

1. Base Class Library (BCL): Base class library consists of predefined classes which help in performing various tasks like connectivity with the database, web development, accessing the database,security settings of applications etc. during development of an application. This library of classes is commonly shared by all the languages that are available in the .net framework. Some of these langauges include C#, Visual Basic etc.. Latest version of .Net framework 3.5 supports more than 90 languages.

2. Common Language Runtime (CLR) : This component of .net framework manages the execution of the code written in various langauges . A developer writes his/her code in C# or J#. At compile time , the .net compiler for these languages convert them into Intermediate Language (IL) or Microsoft Intermediate Language (MSIL). At run time , a component of CLR known as JIT Compiler converts this code into its native code that can be understood by the operating system. JIT compiler converts only the required code into native code and not whole of the code.

Apart from this , CLR also perform some important tasks which include :

(a) Memory management : It involves allocating required memory for the execution of a program and deallocating the same after the program is completed.

(b) Thread management : It involves managing execution of two or programs or processes running at the same time.

(c) Garbage Collection : This task is performed by garbage collector. Its job is to get back the memory allocated to the objects that will not be used again by the application .

(d) Exception Handling : Exception handling is a method to control the flow of execution of a program when an error occurs. When an error occurs, an exception is said to be raised.

Custom error in web.config

Sometimes in asp.net , when we run an application , an error occurs. This error shows a default page with error type and its description.This type of page should never be displayed especially to the users as they want an application to be working perfectly allright and as user friendly as possible . To avoid this, we can provide with a customized web page with a message to the user.

To show this page we only need to create a page with a message and add a line of code in our web.config file. Follow these steps and you are done.

1. Create your error message Page with any message like "Sorry for your inconvenience but the page you are trying to access is not available at the moment".

2. Go to web.config and look for "system.web" tag

3. Add the "customErrors" tag between its opening and closing tags and add following attributes for it:

customErrors="On/Off/RemoteOnly" defaultRediret="ErrorPage.aspx"

In case mode is set Off , there is no need to provide with defaultRedirect attribute.

The three modes are described as follows :

1. Off Mode : In this case , whenever an error occurs, the default Error page of ASP.Net is shown both to the remote user and the local user. This is the mode which shows the complete error and by default , this error mode is used.

2. On Mode : In this case, whenever an error occurs , the customized error page we have created is displayed both to the local user and the remote user. In this case , if we do not specify the error page to be used , error page shows how we can enable Remote Mode to view the error.

3. Remote Only : In this case , whenever an error occurs , our customized error page with our Message is displayed to the remote user and default error page is shown to the remote user. This mode is the best as it hides error message from the user and shows it only to the concerned user.

defaultRedirect="ErrorPage.aspx" attribute specifies the page where the user is to be redirected in case of any error . This attribute is added only in case Mode is set to either On or RemoteOnly

Joins in SQL

Joins in SQL are used to retrieve data from two or more tables on the basis of a relationship between the tables. The two tables are normally related to each other on the basis of a Primary Key . Joins are classified into two main categories :

1 Inner Join : An inner join returns all the results for which the columns of the two linking tables match each other. For ex Table1 has Stu_ID , Stu_Name and Table2 has Stu_ID , DeptName . To get Dept Name for each student, we can use following querry using Inner Join.

Select Table1.Stu_Name,Table2.DeptName from Table1 Inner Join Table2 Table1.StuID=Table2.Stu_ID

This will return all the results for two tables for which the Stu_Id of the two tables match with each other. Even if the keyword Inner is not specified, it is taken as Inner Join

2. Outer Join: Outer join in SQL is further classified into two categories ....

(a) Left Outer Join : Left outer join selects all the entries for first table and only those entries of second table for which there is a match between the linked columns. If Left Outer join is applied in above case , the result will be all the entries from Table1 and only the entries from Table2 for which there is a match between the Stu_ID columns of the two columns . It is applied as :

Select Table1.Stu_Name,Table2.DeptName from Table1 Left Join Table2 Table1.StuID=Table2.Stu_ID

(b) Right Outer Join : Right outer join selects some entries for first table for which there is a match between the two tables and all the entries of second table even if there is a not a match between the linked columns. If Right Outer join is applied in above case , the result will be all the entries from Table2 and only the entries from Table1 for which there is a match between the Stu_ID columns of the two columns . It is applied as :

Select Table1.Stu_Name,Table2.DeptName from Table1 Right Join Table2 Table1.StuID=Table2.Stu_ID

Primary Key , Unique Key ,Foreign Key

Primary Key : Primary Key on a column ensures that no duplicate values are inserted in a column. Also applying primary key ensures null values are not allowed. In terms of indexing, Primary Key enforces Clustered Indexing. It implements the Entity integrity Constraint

Unique Key : Unique is similar to Primary key except that null values are allowed in unique key. Also Non-Clustered indexing is implemented by the unique key constraint.
It implements the Entity integrity Constraint

Foreign Key : Foreign key concept is used to establish relation between two tables of a database . In this relationship , a column which is Primary Key for parent table , is referenced by column of a child table i.e. the two columns are linked to each other. The column which refers the Primary key of parent table , becomes the Foreign key for child table.

Constraints in SQL

A constraint in SQL Server is a way to enforce rules on what kind of data can be added or how data can be modified in SQL Tables. These are basically used to avoid any kind of inconsistencies in the database and can be described as assigning properties to the columns. These constraints are categorized into four main categories :

1. Entity Constraints : It ensures that duplicate data is not inserted into the table. This is done by adding Primary Key constraint which ensures that a column can not have a duplicate value. For ex : To make sure that no value of a column named EmpId in a column is repeated , Primary Key constraint is added.

2. Domain Constraints : These constraints are added to ensure that data being added is within the range and type of the datatype of the column. For example to make sure that no value in a column named EmpID is > 50 , this constraint is used. This type of constraint is implemented using the Check constraint which makes sure that no value greater than 50 is added in the EmpID column.

3. Refrential Integrity Constraint : This type of constraint is added to make sure that data from a table is not deleted if any of its columns is having relation with another table in the database i.e. any other table is dependent on the parent table. This type of constraint isimplemented by the concept of Foreign Key Constraint. For ex : if a table named tbEmp is having EmpID as Primary Key and is referring to EmpID column (which acts as Foreign Key) of another table named tbDepartment , the Foreign Key Constraint makes sure that no data from parent table tbEmp is deleted until all the underlying values from tbDepartment are deleted.

4. User Defined Constraints : These are some rules defined by users which are not in the above