Thursday, September 30, 2010

Entity Framework : Part 8 – Lazy loading with Entity Framework 4


This is the eight part of the Entity Framework 4 learning series. I’ll be demonstrating the technique called Lazy Loading or what is also called as Deferred Loading using Entity Framework. Lazy loading is a term used to load dependent objects. This concept is not just applicable to ORM but even other concepts like Dependency Injection frameworks use it. The dependency injection containers which I have used like Spring.Net, Castle Windsor etc offer this facility. In the ORM world NHibernate supports lazy loading. The idea behind lazy loading is you defer the loading of objects until they are required by the runtime objects.


Like earlier post we’ll have to have the entity data model ready using the Northwind database schema. Please refer to my earlier post if you are not ware how to build the entity data model based on existing database objects.

Simple example of Lazy Loading

Lets write a simple query to fetch the top 20 employees from the Northwind database and count the number of orders placed by each employee.

            NORTHWNDEntities context = new NORTHWNDEntities();

            var employees = context.Employees.Take(20).ToList();

            foreach (Employee employee in employees)
                Console.WriteLine("Total number of order by employee : {0}", employee.Orders.Count);   

We start off with creating the ObjectContext which is NORTHWNDEntities in our case. We filter the 20 employees using Take method on the employees entity collection. Then we loop over the individual employees and display the count of Orders to the console window.

By default Entity Framework 4 enables the Lazy Loading feature. Because of this there are multiple hits to the database server. The initial query which filters the 20 employees is executed when we start iterating over the loop. And then for each employee a separate query is executed within the foreach loop.

Turn-off Lazy Loading

We can turn off the lazy loading feature and fetch the related records in one query itself. This process is termed as eager loading. To turn off Lazy Loading we can set LazyLoadingEnabled property of the ContextOptions on context to false.

            context.ContextOptions.LazyLoadingEnabled = false;

Now if we run the earlier code, we get the count of orders as 0. This is because only the first query is executed.

Explicitly Include results

If we want the resultset to include the dependent records we need do explicitly tell the framework to include them along with the first query results.

            var employees = context.Employees.Include("Orders").Take(20);

Use the Load Method to request for related records

Another alternative is to call the load method inside the loop to load the related records.


                Console.WriteLine("Total number of order by employee : {0}", employee.Orders.Count);

Please note that in this case we should not use the Include method mentioned earlier on the statement which filters the 20 employees. We would run the query similar to the first case and then use the load method to load related objects.


Lazy loading is helpful if we have a large object graph and we need to perform some in memory calculations one at a time. We should be careful while using Lazy loading with service layer. Since services use serialization to transfer data over the wire, lazy loading might increase the processing time during serialization. In such scenarios we should avoid using lazy loading.

As usual I have uploaded the complete working solution to dropbox which can be downloaded here.

Unit next time Happy Programming :)


Further Reading

Here are some books I recommend related to the topics in this post.


Monday, September 27, 2010

Entity Framework : Part 7 – Complex CRUD Operations with Entity Framework 4


This is the seventh part of the Entity Framework 4 Learning Series. In the last post I made slight change to the original plan of writing a post on Lazy Loading in the next post. In the last post I showed CRUD operations on simple entities. This post I’ll be demonstrating CRUD operations on relational entities. I’ll continue with Lazy loading in the next post.


Like we did in the couple of last post, lets get started with the first step of generating the entity data model. If you are not aware of how to create the entity data model based on existing database, please follow the steps mentioned in the earlier post on  Query Relational Data Using EF.

1. Create relational data using Entity Framework

We’ll try to build on the earlier posts. So lets take a scenario. based on our entity model we can see that both Customers as well as Employees can place Orders. For our purpose lets create a new Employee and place some Order for him or her. Using the entity relationship lets try persisting his to the database.

            NORTHWNDEntities context = new NORTHWNDEntities();


            Employee newEmployee = new Employee()


                    FirstName = "Nilesh",

                    LastName = "Gule",

                    Title = "Tech lead",

                    Address = "Outer Ring Road",

                    City = "Bengalore",

                    Country = "India",





            Order currentOrder = new Order { OrderDate = DateTime.Today, RequiredDate = DateTime.Today.AddDays(7) };









Lets see what have we done here line by line. The first line merely creates an instance of the object context for us which is NORTHWNDEntities. Then we create an instance of Employee using the object initializer syntax. We add this to the Employees entity set using the AddObject method.

We also create a new instance of Order entity which maps to the Orders table in the Northwind database. For simplicity I have set only the OrderDate and RequiredDate properties. I have added this to the orders collection of the employee using the Orders Navigation Property.

Finally I called the SaveChanges method on the context. The last two lines are simple helper functions which display the details related to the employee and the order.

DisplayEmployeeDetails is defined as

        private static void DisplayEmployeeDetails(Employee employee)


            Console.WriteLine("Customer ID : {0}", employee.EmployeeID);

            Console.WriteLine("Customer name : {0} {1}", employee.FirstName, employee.LastName);

            Console.WriteLine("Address : {0}", employee.City);

            Console.WriteLine("Address : {0}", employee.Address);

            Console.WriteLine("Number of Orders : {0}", employee.Orders.Count);


There are two interesting things in the above function implementation. The first line is used to output the EmployeeID which is assigned back after the PrimaryKey is assigned in the database. The second point to note is the last line which outputs the count of orders for the selected employee. Actually its highlighted in the other helper function even better

        private static void DisplayOrderDetails(Order order)


            Console.WriteLine("Order ID : {0}", order.OrderID);

            Console.WriteLine("Employee ID : {0}", order.EmployeeID);

            Console.WriteLine("Employee Name : {0} {1}", order.Employee.FirstName, order.Employee.LastName);


The first line displays the OrderID for the order. But we never wrote any code for assigning the OrderID explicitly. Neither did we write any code for persisting the Order to the database. The real magic happens behind the scenes and Entity Framework works out the dependencies between objects while saving the changes.

Also note the second line above. We are displaying the EmployeeID. Again we did not write the code to assign this value anywhere. It was automatically assigned by the framework by identifying the association between the Order and the Employee entities. Just to confirm that the order entity displays correct information, I have made use of the navigation property Employee and displayed the employee FirstName and LastName properties.

If you run the solution, the output confirms that the relationships are set up correctly. You can imagine how much code we would have to write if we were coding this using ADO.NET. It could have easily taken us couple of hours to come up with a complete persistence related code. That’s the beauty of Entity Framework. It saves you from writing a lot of mundane and repetitive code and lets us concentrate on actual business logic.

2. Read relational data using Entity Framework

We have seen couple of examples in the helper method which accesses relational data using the navigational properties. I have also demonstrated this same feature in earlier posts. So I won’t repeat myself again here as its quite simple and straightforward.

3. Update relational data using Entity Framework

Updates are nothing different as compared to create. Since we use the object context the dependencies are taken care off while handling the updates as well. I’ll leave that as an exercise for the benefit of the readers.

4. Delete relational data using Entity framework

Lets take a simple scenario. We’ll enhance the example used in the first part of this post. Lets query the database and get the list of orders placed by one of the employee. We’ll go and delete this employee and then try and fetch the orders again.

            Employee existingEmployee = context.Employees.First();


            int employeeID = existingEmployee.EmployeeID;


            var ordersForFirstEmployee = context.Orders.Where(order => order.EmployeeID == employeeID);


            Console.WriteLine("Total Orders : {0}", ordersForFirstEmployee.Count());






            ordersForFirstEmployee = context.Orders.Where(order => order.EmployeeID == employeeID);


            Console.WriteLine("Total Orders : {0}", ordersForFirstEmployee.Count());

We get the first employee from the database in the first line. Then we find all the orders associated with the employee. Then we display the count of orders and delete the employee object. In the last two lines we try to fetch the orders for the same employee and display the count again. If you try running this piece of code we get an exception because we have a relationship between the employee and the orders entities. We’ll see in future posts how to handle cascade deletes.


In this post we saw how Entity framework 4 handles primary key and foreign key constraints for us behind the scenes. Because of the navigation properties we are able to navigate both sides of the relationships. Since the object context is fully aware of the relationship between entities it also prevents us from doing accidental deletes.

As always I have uploaded the complete working code to dropbox which is available for download here.

Until next time Happy Programming :)

Monday, September 20, 2010

Entity Framework : Part 6 – Perform CRUD operations using Entity Framework 4


This is the sixth part of the Entity Framework  4 learning series. Today I am going to demonstrate how to perform create new entities and persist them to the database, read data from existing entities, perform updates to the existing entities and delete the unwanted entities. These commonly used operations are collectively called CRUD which stands for Create, Read, Update and Delete.

The read part was covered in the earlier posts related to querying data and sorting as well. So I won’t go too much into those things again. I’ll try and cover the other 3 aspects in this post.


Like we did in the couple of last post, lets get started with the first step of generating the entity data model. If you are not aware of how to create the entity data model based on existing database, please follow the steps mentioned in the post here.

Since I am using the sample Northwind database i wouldn’t want to fiddle around too much with it. The best part is I can just replace the .mdf file which I have backed up to restore the database to its original state.For the purpose of this demo I’ll try to work mostly with a single entity and perform all operations on the same.

Perform Create and Read Operations Using Entity Framework 4

Lets start with one of the simplest entity in the entity model which is Employee. Although it has got many properties for the sake of this example I can populate only few of them and save the object in the database. Following is the code snippet I will use to do this.

            NORTHWNDEntities context = new NORTHWNDEntities();


            Employee latestEmployee = new Employee


                    Address = "Bangalore",

                    BirthDate = DateTime.Today.AddYears(-30),

                    City = "Bangalore",

                    Country = "India",

                    FirstName = "Nilesh",

                    LastName = "Gule",

                    Title = "Mr",

                    TitleOfCourtesy = "Sir",

                    HireDate = DateTime.Today.AddYears(-3)



            //add using AddToEmployees method




As usual we start off with an instance of ObjectContext which happens to be NORTHWNDEntities in our case. I create an instance of Employee and set the properties using object initializer syntax. Then I use the AddObject method on the Employees object set and pass it the new instance of Employee entity. Finally I save the changes to the database using SaveChanges method of the context.

If everything went fine we would have added the new customer to the database. But how do we verify that? We can query the employee instance above and check if the EmployeeID property has a valid value assigned to it.

            Console.WriteLine("Employee ID : {0}", latestEmployee.EmployeeID);

This is another advantage of using an ORM tool like EF. We need not write any additional code to fetch the primary key and assign it to a property on the entity object. The framework takes care of it automatically. For the sake of clarity I create d a small helper method which will output the values of the properties of employee as follows

        private static void DisplayDetails(Employee employee)


            Console.WriteLine("First Name : {0}", employee.FirstName);

            Console.WriteLine("Last Name : {0}", employee.LastName);

            Console.WriteLine("Address : {0}", employee.Address);

            Console.WriteLine("City : {0}", employee.City);

            Console.WriteLine("Date Of Birth : {0}", employee.BirthDate.Value.ToShortDateString());

            Console.WriteLine("Hire Date : {0}", employee.HireDate.Value.ToShortDateString());


In order to make use of this helper function I’ll need to pass an instance of Employee entity to it. To ensure that our entity was saved properly to the database, I’ll query it again and pass it to the helper function as

            int employeeID = latestEmployee.EmployeeID;


            //fetch existing entity

            Employee fromDB = context.Employees.Single(employee => employee.EmployeeID == employeeID);



If you notice carefully, in the above statements we have used the read feature for reading the details of the entity from database.

I can use the alternate method of adding the employee entity to the context. The ObjectContext itself provides an AddObject method which can be used as

            context.AddObject("Employees", latestEmployee);

In this case we need to provide the Entityset name as the first parameter. This happens to be Employees in our case. There is also another method which is provided by the framework to add entities to the context.


In this case we make use of the AddToEmployees method to add anew employee entity to the context. All 3 methods described here ultimately have the same effect.

 Note : You might see that if you run the example again and again the same ID might begetting assigned to the new employee. This happens because I am using a file based database. This is set in the connectionstring using AttachDbFilename property. I am not connecting to an instance of SQL server database. Hence when the code is compiled, the database file gets copied to the output folder. You can check this behavior by monitoring the contents of Bin/Debug or Bin/Release folder depending on the type of build you perform.

Perform Update Operation Using Entity Framework 4

Lets use the same entity that we retrieved from the database and perform some updates to it. This is as simple as any DotNet related code. We already know how to get the details into the entity object. We can update the properties to the new values and call the SaveChanges method on the context to reflect the changes back to database.

            int employeeID = latestEmployee.EmployeeID;


            //fetch existing entity

            Employee fromDB = context.Employees.Single(employee => employee.EmployeeID == employeeID);




            //update entity details

            fromDB.City = "Bengaluru";

            fromDB.BirthDate = DateTime.Today.AddYears(-35);

            fromDB.HireDate = DateTime.Today.AddYears(-5);


            //update to DB


I have modified the City, BirthDate and HireDate for the employee which was fetched from the database and saved back the changes. To verify whether this was correctly saved or not I can request the same details again from the database and display the details to verify the updates as


            fromDB = context.Employees.Single(employee => employee.EmployeeID == employeeID);



As expected the results show that the fields are indeed updated.


Perform Delete Operation Using Entity Framework 4

Delete is similar to add in a sense that we need to pass a reference to an existing entity object that we wish to be deleted from the database. Here is an example.



            //update to DB


I have made use of the DeleteObject method of EntitySet to delete the object.

You can verify that the object was actually deleted from the database or not by using the same query that we used earlier. I leave that as an exercise to the reader.

If you are too lazy to try that out yourself, feel free to uncomment the last couple of lines of the attached code to see how it works. 

The framework provides another alternative to delete objects from the context. This has a similar naming convention to the add method but has different set of parameters.


Here we are bypassing the entityset (Employees) and calling the DeleteObject method on the ObjectContext directly. Since each object is unique this is able to delete the correct object when we save the changes.


So in this article we saw how to perform CRUD operations using EF 4. [Update 25th Sept 2010] There are alternate ways of adding and deleting entities using a different syntax. I’ll update this post sometime later with those updates as well. But this is the preferred approach so I would suggest you can use this one over the others.

I have updated the post with alternate syntaxes for the adding and deleting the entities from the context. I have also updated the code used for the demo. While adding and deleting the entities, I prefer the syntax which is based on strong type of EntitySets like context.Employees.AddObject(latestEmployee) and context.Employees.DeleteObject(fromDB). This is also the recommended approach.

In these examples I have demonstrated simple object persistence. In a real life scenario it hardly happens to be as simple as this. So I would definitely follow up this post with another one which demonstrates how to persist a complex object graph. Sometimes while writing one posts we generate ideas for the future ones and that is exactly what is happening with this series for me. I’ll finish this post here.

As usual the complete source code is uploaded to dropbox and is available for download here.

Until next time Happy programming :)

Further Reading

Here are some books I recommend related to the topics in this post.


Friday, September 17, 2010

Unit Test Application Configuration Settings


All the post I had written this month so far were related to the Entity Framework learning series. This time I thought of taking a diversion and share a quick method of unit testing setting which might be stored in the Application Configuration file (App.config) of a DotNet application.


Unit Test Application Configuration Settings

Lets fire up Visual Studio and create a simple console application. You can name it whatever you want. I have named the project as AppConfigTest. Once the project is created right click on Add and select Add New Item option in the solution explorer window. Select Application Configuration File option and click Add. This will automatically name the new file as App.config.

Add App.Config

Since this file used to configure settings at application level there is a possibility of someone changing these setting unknowingly. It can lead to severe problems later.

It might also happen that the settings are different in different environments like development machines, test systems and production systems. We would not like someone to checkin the source code with developer box settings which might result in breaking the functionality in test or production environments.

To overcome these problems, I thought of writing a test which can be executed as part of the automated build to ensure that the default or the predefined values are not changed by mistake during development.

Lets first write a piece of code which actually accesses some key from the application settings. Assume a hypothetical example wherein the application has some timer functionality. The interval of the timer is configurable and the default value should be 2 minutes. I’ll start by adding a key to AppSettings element.

<?xml version="1.0" encoding="utf-8" ?>
    <add key="DefaultTimerIntervalInMinutes" value="2"/>

I have created a key value pair and named the key as DefaultTimerIntervalInMinutes and assigned the value as 2. Lets access this value from our program. In order to access configuration related settings from our code, we need to add a reference to the System.Configuration assembly.

configuration reference

We can make use of the ConfigutationManager class to access various configurations. I’ll get hold of the AppSettings node using the AppSettings property. This is of the type NameValueCollection.

            NameValueCollection appSettings = ConfigurationManager.AppSettings;

            string defaultTimeIntervalInMinutes = appSettings["DefaultTimerIntervalInMinutes"];

            Console.WriteLine("Default timer interval in minutes : {0}", defaultTimeIntervalInMinutes);

Since this is a collection I can access individual items either using the index or the key name of the application setting. I prefer to use the key name based approach as it gives us the flexibility of adding more keys without worrying about its index. If we use the index to get reference to the key value pair, we’ll need to ensure that nobody changes the position of the key in the items collection. I don’t think its practical and try to stay away from that approach.

By mistake if somebody comments the line which defines the default timer value in the App.config the code still builds but when I run it I don’t see any value on the console output. Here the impact is minimal, but if I were to use that value for any calculation the application would have thrown a runtime exception when I try to assign the null value to any other object. To avoid such situation I want to write a simple test which ensures that the value always exists in the configuration file.

2. Create Test Project

I’ll add a unit test project to the current solution. Simplest way to do that is to right click on the Main method and select Create Unit Test option from the context menu.

creta unit test

Just to fool the VS IDE create a new project by giving any valid name. I choose to name the project as AppConfigTest.UnitTests. You can name it anything as per your preference. I said to fool VS because I just want to use the template provided by the IDE. I am going to delete the file which is created by VS and create a new one from scratch. This is just a shortcut which saves me lot of pain of adding references to VS quality tools dll etc.

unit test project

Let the wizard create the project for you. If you check the references it would have added most of the necessary references for us. I’ll go and delete the file ProgramTest.cs because I don’t want to test anything in the Program.cs file at this point.

Lets add a new class to the unit test project and name it as ApplicationConfigurationSettingsTest.cs. I want to make sure that the default value for the interval is assigned as 2. So lets write a test to verify that.

    public class ApplicationConfigurationTest
        public void DefaultTimeIntervale_Should_Equal_Two_Minutes()
            string expectedValue = "2";

            NameValueCollection appSettings = ConfigurationManager.AppSettings;

            Assert.AreEqual(expectedValue, appSettings["DefaultTimerIntervalInMinutes"]);

I have TestDriven.Net add inn installed so I can just right click on the test and say run test to execute the test. When I run the above test it fails saying expected 2 but was null. This happens because we are trying to access the AppSettings. AppSettings are specific to a project or the application. These values are taken form the startup project. When I try to execute the tests, the test assembly does not have the App.Config file defined for it. Hence it returns a null object.

There are various ways of solving this problem. The easiest is to add an App.Config file to the unit test project as well and copy the same entries that are there in the actual project that we are trying to test. But the problem with this approach is we always need to keep the two files in sync.

Another approach is to automatically copy the App.config file from the project to the unit test project using the post build events. I feel this again is a bit tedious process. If someone unknowingly deletes the post build event script then the unit tests will fail and debugging would be difficult.

I would like to use a different approach here. I can add a new file to unit test project. But instead of creating a new file, I can link it to an existing file. The advantage of using this approach is that the changes needs to be done in only one place. There is no additional scripting knowledge required to copy files using the post build events as well.

So lets start by right clicking on the unit test project and add existing item option. Navigate to the folder where App.Config file is stored on the disk. You might need to select All Files filter to see all files in the directory. Select App.Config and click on the arrow on Add button and select As Link.

Add link

You can see the difference in the way the file is represented in the solution explorer when it is linked to another file. now lets try running the test once again. Voila, this time the test runs successfully.

In some cases it might happen that even after undertaking these steps the test might fail. You can set the copy to output directory property as Always copy for the App.Config file in unit test project and that should solve the problem.

copy to output directory

I would like to run one more test to ensure that the changes in the original App.config files are really reflected to the linked one and ultimately in the tests. I go and comment the line which defines they key and run the test again. The test fails and confirms that the two files are always in sync.


As I mentioned above I have seen people use different approaches to copy App.config files to the unit test projects. I think this is one of the simples approach. Since these tests are not testing any logic as such purists might argue that they are not unit tests in real sense. I agree and may be these can be run as part of functional or customer tests.

One of the biggest advantage I see in testing configuration settings this way is that any mismatch in the release mode settings can be identified very early.

As usual I have uploaded the complete source code to dropbox which can be downloaded here.

Until next time Happy Programming :)

Thursday, September 16, 2010

Entity Framework : Part 5 - Sort Data using Entity Framework 4


This is the 5th part of the Entity Framework learning series. I’ll continue from where I left last time around with filtering data. In most cases the filtering and sorting operations go hand in hand. That was the reason initially I had thoughts of covering them up in a single post. Since I decide to show the queries using both syntaxes, I though the post would be too lengthy and decided to spilt it at the last minute. So lets get started with sorting.

Sort data using Entity Framework 4

We’ll follow the same steps like previous occasions to create the entity data model. If you are directly coming to this post and are not aware of how to create the entity data model, I suggest you read through the earlier posts to get better understanding.

1. Sort using single key

Lets try and sort the customers that we have in the Northwind database. As always I’ll create an context and make use of that to query the entity model as shown below

            NORTHWNDEntities context = new NORTHWNDEntities();


            var customers = context.Customers.Take(20).OrderBy(customer => customer.ContactName);


            foreach (var customer in customers)


                Console.WriteLine("Customer City : {0}", customer.City);

                Console.WriteLine("Customer Name : {0}", customer.ContactName);




I have used the filter method Take to restrict only top 20 customers. Once I get the 20 customers, I used the OrderBy method which takes a Lambda expression. I have sorted the customers based on the ConatctName.

The below query returns the same results using the query syntax

            var customers = (from customer in context.Customers

                             orderby customer.ContactName

                             select customer).Take(20);

The default order of sorting is the Ascending order. We can always change the sort order to descending using the following syntax

            var customers = context.Customers.OrderByDescending(customer => customer.ContactName).Take(20);

Same results can be achieved using query syntax as

            var customers = (from customer in context.Customers

                             orderby customer.ContactName descending

                             select customer).Take(20);

If you observe carefully the two queries I have used for query syntax actually make use of the mixed approach. They use the combination of query syntax to sort the result set as well as standard query operator in the form of Take method to restrict the results to 20 records.

2. Sort using multiple keys

On many occasions we need to sort the data based on composite keys. Lets assume I want to sort all the customers first by city and then by the customer name. Here is how to achieve this

            var customers = context.Customers

                            .OrderBy(customer => customer.City)

                            .ThenBy(customer => customer.ContactName)


The point to note above is the addition of ThenBy extension method. I can chain this method any number of time to build a multi level sorting mechanism. Same query would get transformed into the form as shown below when converted into query syntax

In this case the query looks more natural as compared to the extension method or the standard query syntax.

3. Sort using combination of sort

I can use a combination of the keys to sort the collection based on one key in ascending and the other one in descending order as shown below

            var customers = (from customer in context.Customers

                             orderby customer.City, customer.ContactName

                             select customer).Take(20);

I have sorted the cities in descending order and then the customers in ascending order. Same result can be obtained using the query syntax as below

            var customers = (from customer in context.Customers

                             orderby customer.City, customer.ContactName

                             select customer).Take(20);



Once again we can see that it is very easy to perform common operation like sorting using EF 4. I did not demonstrate the method of sorting based on Navigation Properties. But frankly speaking that is not a big deal. We can replace any normal property with a navigation property in the above queries and it would work the same way.You can look at the attached source code for one such example where I have sorted the customers based on the number of orders.

As usual I have uploaded the complete source code to Dropbox which can be downloaded here.

Until next time Happy Programming :)