Thursday, September 16, 2010

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

Background

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

 

                Console.WriteLine();

            }


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)

                            .Take(20);

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

 

Conclusion

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

1 comment:

  1. wow it's work
    thank u for great tutorial.

    ReplyDelete

Submit Apache Spark job from Command Line to HDInsight cluster

Background This is the 3rd part of the Step by Step guide to run Apache Spark on HDInsight cluster. The first part was about provisioning t...