Background
This is the fourth part of the Entity Framework 4 learning series. There seems to be little bit of increase in the page hits to my blog since starting this series. In this post I am planning to cover the common functions like filtering, sorting using EF 4.
I have been using LINQ standard query operators in most of my earlier examples. In this post I’ll try to use both the standard query operators using Lambda expressions as well as the query syntax.
Pre-requisite
Before we start writing the code, we’ll need to generate the entity model. I would suggest you follow the steps mentioned in the earlier post to build the entity data model. Once the entity data model is generated we can start with the remainder of this post. I’ll be making use of the same entities that I added to the entity model in the last post.
Filter Data using Entity Framework 4
In most of the cases I’ll be making use of the object context which I have created using
NORTHWNDEntities context = new NORTHWNDEntities();
Here is a small helper function which I have created to display the Customer details
private static void DisplayCustomerDetails(Customer customer)
{
Console.WriteLine("Customer ID : {0}", customer.CustomerID);
Console.WriteLine("Customer name : {0}", customer.ContactName);
Console.WriteLine("Country : {0}", customer.Country);
Console.WriteLine("City : {0}", customer.City);
Console.WriteLine("Total Orders : {0}", customer.Orders.Count);
}
1. Select single entity
Lets start with the simplest of the filtering mechanism where we want to filter the entity based on its Primary Key. I have chose Customer table to query against. Here is how I would do it using the Single standard query method and display the details using DisplayCustomerDetails method which is defined as above
Customer customer = context.Customers.Single(cust => cust.CustomerID == "ALFKI");
DisplayCustomerDetails(customer);
The same query can be written using the query syntax as
var customer = (from c in context.Customers where c.CustomerID == "ALFKIB" select c).Single();
DisplayCustomerDetails(customer);
I have used the type inference feature of LINQ to automatically infer the type of the entity which happens to be a Customer on this occasion. Both the above queries return the same results :)
I can also make use of Where standard query operator and since I know that there is only one record I can take the first record as shown below
Customer customer = context.Customers.Where(cust => cust.CustomerID == "ALFKI").First();
DisplayCustomerDetails(customer);
The same applies to the Distinct query operator as well. I could modify the above code to make use of Distinct operator
Customer customer = context.Customers.Where(cust => cust.CustomerID == "ALFKI").Distinct().First();
DisplayCustomerDetails(customer);
2. Select Multiple Entities
Most of the above queries actually return an IEnumerable or IQuerryable results which indicates that they return a sequence. We can use the sequence to get multiple results.
Lets start with a query expression to get all the customers living in London city
var customers = from customer in context.Customers
where customer.City == "London"
select customer;
I can rewrite the same query using the standard Where operator as
var customers = context.Customers.Where(customer => customer.City == "London");
If you compare the above queries with the previous ones, they look almost similar. The advantage of using a Where filter is that we can apply multiple conditions in the same query. Lets take an example.
3. Select Multiple Entities based on multiple conditions
I’ll extend the previous query. We have 6 where customers living in London. Assume we want to filter the customers who have placed more than 8 orders. I can use the following query to do so
var customers = context.Customers.Where(customer => customer.City == "London" && customer.Orders.Count > 8);
The same query when converted to query syntax would look like
var customers = from customer in context.Customers
where customer.City == "London" && customer.Orders.Count > 8
select customer;
So in the above query I have demonstrated how we can make use of Navigation Property to query for additional information using the Orders collection and use it to filter the necessary details.
Conclusion
I personally prefer the Lambda syntax because it makes queries quite concise and easy to understand. But I have seen many people using the SQL like query syntax as it gives a more natural look and feel over what we are trying to query. In the nutshell it still uses the extension methods and doesn’t have any performance benefits.
As can be seen from the above samples, it is very easy to query data using standard LINQ operators which are mainly implemented as extension methods as well as the query expressions. People who have prior experience of working with LINQ to SQL will find it very similar.
Initially I had thought of covering sorting as well in this post itself, but I feel its better to cover it up in a separate post. I’ll do it in the next post. As always I have uploaded the complete source code to Dropbox which can be downloaded here.
Until next time Happy Programming :)
Good stuff!
ReplyDelete