ASP.NET 4 : Getting More Advanced with the Entity Framework (part 1) - Querying with LINQ to Entities

11/1/2011 11:24:21 AM
If the Entity Framework were simply another way to painless perform a database query, it wouldn't be that interesting. After all, developers already have several perfectly good alternatives, including writing a couple of lines of ADO.NET code to fill a DataSet and using a data source control such as SqlDataSource. However, the Entity Framework starts to become far more interesting when you consider its ability to perform targeted LINQ queries, to track changes and make updates, and to manage concurrency in different ways for different tables. In the following sections, you'll consider all these topics as you take the next step with the Entity Framework.

1. Querying with LINQ to Entities

LINQ is an all-purpose query syntax that works with a variety of data sources. And, as you probably expect, there's a flavor of LINQ that's tailored to the Entity Framework, called LINQ to Entities. Using LINQ to Entities, you can write queries that act on your database. In fact, it's easy—you simply need to query the collections that are exposed by the data context class. The behind-the-scenes plumbing that makes this work is completely different, but the syntax is identical. For example, this code snippet grabs the orders made by a specific customer:

gridProducts.DataSource = From order In entities.Orders
Where order.CustomerID = "ALFKI"
Select order


There isn't a clear dividing line between the Entity Framework and LINQ to Entities. As you've already learned, even if you simply use the full collections of the context object, your code still behaves like it's using a LINQ expression. (For example, you'll still get the special iterator objects and the deferred execution that optimizes performance and complicates error handling.)

Figure 1 shows a more complete web page example. Here, a DropDownList control shows a list of product categories (taken from the ProductCategories table). The user then picks a category, and the GridView underneath refreshes itself to show just the products in that category.

Figure 1. Browsing product categories

To build this application, you first need to define the markup. In this example, the markup has two interesting details. First, the DropDownList control adds a single list item with the text "(Select a Category)":

<asp:DropDownList ID="lstCategories" runat="server" AutoPostBack="True"
<asp:ListItem Text="(Select a Category)" Value="-1"></asp:ListItem>

When the page first appears, the "(Select a Category)" item is visible, and the GridView is left blank. Thanks to the AppendDataBoundItems property (which is set to True), this hard-coded item tops the list. (The data-bound product categories will be added below.)

The second control is the GridView, which is quite straightforward. It includes some basic style properties (set using the Auto Format feature) and is otherwise unremarkable:

<asp:GridView ID="gridProducts" runat="server" CellPadding="4" GridLines="None"
Font-Size="X-Small" ForeColor="#333333" AutoGenerateColumns="True">

In the code-behind file, the NorthwindEntities object is created as a member variable of the page. That way, the object is available to any event handler. When the page first loads, the code grabs the product category information from the NorthwindEntities.Categories collection and binds it to the drop-down list:

Private entities As New NorthwindEntities()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) _
Handles Me.Load
If Not Page.IsPostBack Then
lstCategories.DataTextField = "CategoryName"
lstCategories.DataValueField = "CategoryID"
lstCategories.DataSource = entities.Categories
End If
End Sub

The DropDownList sets the AutoPostBack property to True, so the page is posted back as soon as the user picks a category. At this point, the SelectedIndexChanged event fires. The event handler retrieves the matching product records from the NorthwindEntities.Products collection, using the LINQ query shown here:

Protected Sub lstCategories_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As EventArgs) Handles lstCategories.SelectedIndexChanged
Dim selectedID As Integer = Val(lstCategories.SelectedValue)
If selectedID = −1 Then
' The "(Select a Category)" item was picked.
' Don't show anything.
gridProducts.DataSource = Nothing
' Query the products in the selected category.
gridProducts.DataSource = From product In entities.Products
Where product.CategoryID = selectedID
Select New With {
.Name = product.QuantityPerUnit,
.Quantity = product.QuantityPerUnit,
.Stock = product.UnitsInStock}
End If
End Sub

This example uses two LINQ features. First, the Where clause applies filtering to find the products in a specific category. Second, the New With keywords uses the projection feature, which creates a new object with a subset of the product data. This is the reason the GridView in Figure 24-12 shows just three fields. (Another approach is to configure the GridView.Columns collection. But this example shows LINQ's ability to reshape your data on the fly.)

At this point, you might be wondering what all the fuss is about. After all, the query shown here looks exactly like an ordinary LINQ to Objects query that acts on the NorthwindEntities.Products collection. But there's a significant difference in the way this code works.

In a LINQ to Objects query, all the information is held in memory. LINQ simply loops over it and picks out the objects you want. But in a LINQ to Entities query, the information is drawn straight from the database using deferred execution. It isn't until you bind the data or begin iterating over it that LINQ performs its query. As a result, LINQ is able to turn your expression into a fine-tuned database query.

This difference has important performance implications. If you simply use LINQ to Objects, you need to perform a query for all the product records, load all the data into memory, and then discard the records and fields you don't want. This creates extra work for the database server, slows down your web page, and uses more memory on the web server. But when you use LINQ to Entities, you fetch only the records and fields you need. So, instead of using a SQL command like this:

SELECT * FROM Products

LINQ to Entities creates a command that's more like this:

SELECT ProductName, QuantityPerUnit, UnitsInStock FROM Products WHERE CategoryID=1

The clause in the LINQ expression becomes the Where clause in your SQL Select statement. The three fields you're using to create the projection become the three fields you're retrieving in your query. The result is a faster and more efficient query.

Incidentally, you could force the Entity Framework to load all your information into memory using the strongly typed ToList() method and then use LINQ to Objects. Here's an example:

gridProducts.DataSource = From product In entities.Products.ToList()

Although this code looks similar to the previous example, it works very differently. Now, the entire table of products is placed into an in-memory collection, and the collection is searched with LINQ to Objects. In this example, the difference is minor, but in a Products table with thousands of records, the drawbacks are obvious.

2. Controlling When Data is Loaded

One of the underlying principles of the Entity Framework is that you use the objects it provides, and the Entity Framework decides when to perform database queries. This allows the Entity Framework to optimize queries, as you saw in the previous section. However, in some situations it introduces additional complications—and in others, it can even reduce performance.

The most obvious example is when accessing related data. Once again, the Entity Framework waits until the last possible second to perform its database query. When you begin moving through the Customers collection, the Entity Framework performs a query to select all the customer records. Then, when the inner loop follows the Customer.Orders collection, the Entity Framework performs a second query that selects the orders for that customer. On the next pass, when you access the Customer.Orders collection of the second customer, the Entity Framework performs a third query to select that customer's orders. And the process continues, with a separate order query for each customer.

This approach is called lazy loading, and it's perfect for situations where you need to get a small number of linked records (for example, the orders for one or two customers). However, if you plan to examine the orders for every customer (as in this example), it's far less convenient—in fact, it multiplies the work the database server needs to do.

In this situation, the best solution is to preload your data using the Include() method and specifying the table with the related data. For example, you could use it in the customer order example to change this:

Dim entities As New NorthwindEntities()

For Each customer As Customer In entities.Customers

For Each order As Order In customer.Orders

to this:

Dim entities As New NorthwindEntities()

For Each customer As Customer In entities.Customers.Include("Orders")
For Each order As Order In customer.Orders

This works because the Customers table includes a property named Orders. When the first For Each block is executed, the Entity Framework will immediately perform a query that retrieves the full contents of the Customers and Orders table. When the second For Each block is reached, no database operations are performed. Instead, the Entity Framework uses the order entities that are cached in memory.

If you are planning to use navigation levels to dig through several levels of interconnected tables, you can use a dot (.) syntax with the Include() method. For example, if you know you plan to retrieve the Order_Detail records for each order and the matching product name for each Order_Detail, you can use the Include() method like this:

Dim entities As New NorthwindEntities()

For Each customer As Customer In _
For Each order As Order In customer.Orders

Of course, this code makes sense only when you're performing an operation that traverses the entire contents of the Customers, Orders, Order_Details, and Products tables. In an ordinary application, you'll be retrieving smaller sets of records, and LINQ's lazy loading behavior will usually give better performance than the Include() method.

  •  IIS 7.0 : Implementing Access Control - Authentication (part 4)
  •  IIS 7.0 : Implementing Access Control - Authentication (part 3) - IIS Client Certificate Mapping Authentication
  •  IIS 7.0 : Implementing Access Control - Authentication (part 2) - Digest Authentication & Windows Authentication
  •  IIS 7.0 : Implementing Access Control - Authentication (part 1)
  •  IIS 7.0 : Implementing Access Control - NTFS ACL-based Authorization & URL Authorization
  •  IIS 7.0 : Implementing Access Control - Request Filtering
  •  IIS 7.0 : Implementing Access Control - IP and Domain Restrictions
  •  IIS 7.0 : Implementing Security Strategies - Configuring Applications for Least Privilege
  •  Security Changes in IIS 7.0 : Reducing the Application’s Surface Area
  •  Advanced ASP.NET : The Entity Framework (part 3) - Handling Errors & Navigating Relationships
    Top 10
    Review : Sigma 24mm f/1.4 DG HSM Art
    Review : Canon EF11-24mm f/4L USM
    Review : Creative Sound Blaster Roar 2
    Review : Philips Fidelio M2L
    Review : Alienware 17 - Dell's Alienware laptops
    Review Smartwatch : Wellograph
    Review : Xiaomi Redmi 2
    Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
    Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
    3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone