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

Background

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.

Pre-requisite

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",

                };

 

            context.Employees.AddObject(newEmployee);

 

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

 

            newEmployee.Orders.Add(currentOrder);

 

            context.SaveChanges();

 

            DisplayEmployeeDetails(newEmployee);

 

            DisplayOrderDetails(currentOrder);

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());

 

            context.Employees.DeleteObject(existingEmployee);

 

            context.SaveChanges();

 

            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.

Conclusion

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 :)

Share: