Language Integrated Query (LINQ) is a set of language extensions for querying data. LINQ was first introduced in .NET 3.5, where it attracted serious attention. However, developers had differing opinions about LINQ's practical value. Some thought it was little more than a cute frill, while others saw in it the entire future of data coding.
Part of the reason that developers were divided is because LINQ isn't just one feature. At its simplest, LINQ allows developers to search for objects in a collection, which is a neat trick but hardly earth-shattering. The real magic of LINQ is its potential to be used on different data sources, such as XML files and relational databases. After all, if searching and sorting through a collection of in-memory objects isn't that interesting, performing the same trick with a SQL Server database is far more impressive—especially when you don't need to involve the traditional ADO.NET objects.
In this chapter, you'll start with a quick introduction to LINQ. Once you've mastered the basics, you'll move on to one of the most exciting LINQ extensions: LINQ to Entities, which lets you use LINQ expression with the code-generation features of the ADO.NET Entity Framework. As you'll see, LINQ to Entities gives you the ability to query your databases, bind data, and commit updates, all without writing a lick of ordinary ADO.NET code. If you're building a web application that uses a huge, complex database and you don't mind sacrificing a little control (and learning a whole new data model), LINQ to Entities just might be the perfect solution.
LINQ defines keywords that you can use to select, filter, sort, group, and transform data. The minor miracle of LINQ is that different LINQ providers allow these keywords to work with different types of data. Here's a summary of the LINQ providers that are included with .NET 4:
- LINQ to Objects:
This is the simplest form of LINQ. It allows you to query collections of in-memory objects (such as an array, an ArrayList, a List, a Dictionary, and so on). You'll start by using this form of LINQ in the first part of this chapter.
- Parallel LINQ:
This is a variation of LINQ to objects that has built-in support for multithreaded execution. That means you can use it to speed up intensive searches on in-memory collections—if your web server has multiple CPU cores.
- LINQ to DataSet:
This form of LINQ resembles LINQ to objects, except it digs DataRow objects out of a DataTable.
- LINQ to XML:
This form of LINQ allows you to search the elements contained in an XElement or XDocument (two XML-processing classes you learned about in Chapter 18). In other words, it allows you to perform more powerful searches when dealing with in-memory XML data.
- LINQ to SQL:
This is the original LINQ provider for data access. It allows you to fetch data from a SQL Server database. Although LINQ to SQL still exists in .NET 4, it's been superseded by LINQ to Entities, which offers the same features with fewer limitations.
- LINQ to Entities:
Like LINQ to SQL, LINQ to Entities allows you to perform database queries with a LINQ expression. Unlike LINQ to SQL, it supports a range of database software—anything that has an ADO.NET provider—and it gives you more options for mapping tables to differently structured objects. You'll explore LINQ to Entities in the latter part of this chapter.
In addition to these providers, third-party developers have created many of their own. For example, there are LINQ providers for searching Amazon's product catalog, Active Directory, Excel documents, Google search results, Flickr images, and more.
Note
LINQ is a deeply integrated part of .NET and the C# language. However, it isn't an ASP.NET-specific feature, and it can be used equally well in any type of .NET application, from command-line tools to rich Windows clients.
The easiest way to approach LINQ is to consider how it works with in-memory collections. This is LINQ to Objects—the simplest form of LINQ.
First, imagine you have some sort of data class, like the Employee class shown here:
public class Employee
{
public int EmployeeID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string TitleOfCourtesy { get; set; }
public Employee(int employeeID, string firstName, string lastName,
string titleOfCourtesy)
{
EmployeeID = employeeID;
FirstName = firstName;
LastName = lastName;
TitleOfCourtesy = titleOfCourtesy;
}
}This exceedingly simple class includes just four properties and a basic constructor. You can easily create a collection that consists of Employee objects, like the strongly typed List shown here:
// Create the collection. List<Employee> employees = new List<Employee>(); // Fill the collection.
employees.Add(new Employee(1, "Nancy", "Davolio", "Ms.")); employees.Add(new Employee(2, "Andrew", "Fuller", "Dr.")); employees.Add(new Employee(3, "Janet", "Leverling", "Ms.")); ...
In this example, the data for each Employee object is hard-coded, but you could just as easily read it from an XML document, a database, or some other location. The key point is that when you're finished, you're left with some sort of collection that contains one or more objects. You can then use LINQ to Objects to get at the data in your collection.
Before you use a LINQ expression, it's worth considering the traditional approach for searching a collection.
For example, imagine you want to get a list of all employees who have a last name that starts with the letter D. The traditional approach is to use code to loop through the full collection of employees and add each matching employee to a second collection, as shown here:
// Create the source collection.
List<Employee> employees = new List<Employee>();
// (Code for filling the collection omitted to save space.)
// Find the matching employees.
List<Employee> matches = new List<Employee>();
foreach (Employee employee in employees)
{
if (employee.LastName.StartsWith("D"))
{
matches.Add(employee);
}
}You can then carry on to perform another task with the collection of matches or display it in a web page, as shown here:
gridEmployees.DataSource = matches; gridEmployees.DataBind();
Essentially, LINQ to Objects allows you to replace iterative logic (such as a foreach block) with a declarative expression. The following example shows how you can rewrite the earlier example, replacing the foreach block with a LINQ expression that queries the collection:
// Create the source collection. List<Employee> employees = new List<Employee>(); // (Code for filling the collection omitted to save space.)var matches = from employee in employeeswhere employee.LastName.StartsWith("D")select employee;gridEmployees.DataSource = matches; gridEmployees.DataBind();
The end result is essentially the same—you wind up with a collection named matches that's filled with employees who have last names starting with D, which is then displayed in a grid (see Figure 24-1).
The LINQ expression uses a set of new keywords, including from, in, where, and select. You shape your query using these keywords. (You'll see some of the rules of expression building starting in the next section.)
LINQ expressions return an usual type of object, called an iterator object. (In this example, the iterator object is named matches.) Although the iterator object looks like an ordinary collection to your code, it doesn't actually hold any information. Instead, it has the ability to fetch the data when you need it. So when you examine the contents of an iterator object with a foreach block or when you bind it to a control, LINQ evaluates your expression and quickly grabs the information you need. This trick is called deferred execution.
Note
There's no technical reason why LINQ needs to use deferred execution, but there are many reasons why it's a good approach. In many cases, it allows LINQ to use performance optimization techniques that wouldn't otherwise be possible. For example, when using database relationships with LINQ to Entities, you can avoid loading related data that you don't actually use.
In this example, the iterator object (named matches) is defined with the var keyword. This is a shortcut that tells the C# compiler to use the correct data type, without forcing you to specify it. Technically, the iterator object could be one of several different types of objects depending on the clauses you use in the LINQ expression. But all of these objects implement the strongly typed version of the IEnumerable interface. In this example, that means you can replace the var keyword with IEnumerable<Employee>, because the collection holds Employee objects, if you don't mind the additional complexity. However, either way the compiled code is exactly the same. If you use the var keyword, the compiler adds the data type information to your compiled page automatically (although you never see this detail).
Tip
You don't need to know the specific iterator class that your code uses because you interact with the results through the strongly typed IEnumerable interface. But if you're curious, you can determine the object type at runtime using the Visual Studio debugger (just hover over the variable while in break mode).
At this point, you might be wondering how LINQ actually does its filtering work. The answer depends on the type of data you're querying. For example, LINQ to Entities transforms LINQ expressions into database commands. As a result, the LINQ to Entities plumbing needs to open a connection and execute a database query to get the data you're requesting. But if you're using LINQ to Objects, as in the previous example, the process that LINQ performs is much simpler. In fact, in this case, LINQ simply uses a foreach loop to scan through your collections, traveling sequentially from start to finish. Although this isn't any different from the approach you used in the first place, it does open up many more possibilities as you use more complex expressions.
Before you can go much further with LINQ, you need to understand how a LINQ expression is composed. LINQ expressions have a superficial similarity to SQL queries, although the order of the clauses is rearranged.
All LINQ expressions must have a from clause that indicates the data source and a select clause that indicates the data you want to retrieve (or a group clause that defines a series of groups into which the data should be placed). The from clause is placed first:
var matches = from employee in employees
...;The from clause identifies two pieces of information. The word immediately after in identifies the data source—in this case, it's the collection object named employees that holds the EmployeeDetails instances. The word immediately after from assigns an alias that represents individual items in the data source. For the purpose of the current expression, each EmployeeDetails object is named employee. You can then use this alias later when you build other parts of the expression, such as the filtering and selection clauses.
Here's the simplest possible LINQ query. It simply retrieves the full set of data from the employees collection:
var matches = from employee in employees
select employee;The C# language includes many more LINQ operators that won't be considered in detail in this book. In the following sections, you'll tackle the most important operators, including select, where, and orderby. You can review all the LINQ operators in the Visual Studio Help. You can also find a wide range of expression examples on Microsoft's 101 LINQ Samples page at http://msdn.microsoft.com/vcsharp/aa336746.aspx.
You can change the select clause to get a subset of the data. For example, you could pull out a list of first name strings like this:
var matches = from employee in employees
select employee.FirstName;or a list of strings with both first and last names:
var matches = from employee in employees
select employee.FirstName + " " + employee.LastName;As shown here, you can use standard C# operators on numeric data or strings to modify the information as you're selecting it. This changes the type of collection that's returned—it's now an IEnumerable<string> collection of strings, rather than a collection of Employee objects. But because this code defines the matches variable with the var keyword, the code keeps working without a hitch.
Even more interestingly, you can dynamically define a new class that wraps just the information you want to return. For example, if you want to get both the first and last names but you want to store them in separate strings, you could create a stripped-down version of the EmployeeDetails class that includes just a FirstName and LastName property. To do so, you use a C# feature known as anonymous types. The basic technique is to add the new keyword to the select clause, followed by a pair of curly braces. Then, inside the braces, you assign each property you want to create in terms of the object you're selecting.
Here's an example:
var matches = from employee in employees
select new {First = employee.FirstName, Last = employee.LastName};This expression, when executed, returns a set of objects that use an implicitly created class. Each object has two properties: First and Last. You never see the class definition, because it's generated by the compiler and given a meaningless, automatically created name. (And for that reason, you can't pass instances of the automatically generated class to other parts of your code.) However, you can still use the class locally, access the First and Last properties, and even use it with data binding (in which case ASP.NET extracts the appropriate values by property name, using reflection). The ability to transform the data you're querying into results with a different structure is called projection.
Figure 24-2 shows the result of binding the matches collection to a GridView.
Of course, you don't need to use anonymous types when you perform a projection. You can define the type formally and then use it in your expression. For example, if you created the following EmployeeName class:
public class EmployeeName
{
public string FirstName { get; set; }
public string LastName { get; set; }
}you could change EmployeeDetails objects into EmployeeName objects in your query expression like this:
var matches = from employee in employees
select new EmployeeName {FirstName = employee.FirstName,
LastName = employee.LastName};This query expression works because the FirstName and LastName properties are publicly accessible and aren't read-only. After creating the EmployeeName object, LINQ sets these properties. Alternatively, you could add a set of parentheses after the EmployeeName class name and supply arguments for a parameterized constructor, like this:
var matches = from employee in employees
select new EmployeeName(FirstName, LastName);In the first LINQ example in this chapter, you saw how a where clause can filter the results to include only those that match a specific condition. For example, you can use this code to find employees who have a last name that starts with a specific letter:
var matches = from employee in employees
where employee.LastName.StartsWith("D")
select employee;The where clause takes a conditional expression that's evaluated for each item. If it's true, the item is included in the result. However, LINQ keeps the same deferred execution model, which means the where clause isn't evaluated until you actually attempt to iterate over the results.
As you probably already expect, you can combine multiple conditional expressions with the and (&&) and or (||) operators, and you can use relational operators (such as <, <=, >, and >=) in conjunction with hard-coded values or other variables. For example, you could create a query like this to filter out products greater than a certain price threshold:
var matches = from product in products
where product.UnitsInStock > 0 && product.UnitPrice > 3.00M
select product;One interesting feature of LINQ expressions is that you can easily call your own methods inline. For example, you could create a function named TestEmployee() that examines an employee and returns true or false based on whether you want to include it in the results:
private bool TestEmployee(Employee employee)
{
return employee.LastName.StartsWith("D");
}You could then use the TestEmployee() method like this:
var matches = from employee in employees
where TestEmployee(employee)
select employee;The orderby operator is equally straightforward. It's modeled after the syntax of the Select statement in SQL. You simply provide a list of one or more values to use for sorting, separated by commas.
Here's a basic sorting example:
var matches = from employee in employees
orderby employee.LastName, employee.FirstName
select employee;You can also add the word descending after a field name to sort in the reverse order:
var matches = from employee in employees
orderby employee.LastName descending, employee.FirstName descending
select employee;There's far more that you can learn about LINQ expressions. In fact, entire books have been written on LINQ alone, including the comprehensive Pro LINQ: Language Integrated Query in C# 2008 (Apress). But you now know the essentials of LINQ expressions, which is enough to let you use it with another remarkable .NET feature: the Entity Framework.
There's a lot to be said for do-it-yourself data access. It allows you to separate the data layer from the rest of your web application—and even hand off the data component to a completely different programmer. And because all the details are there in explicit ADO.NET code, you have the freedom to tweak them, all without disturbing the rest of your web page code. This approach allows you to change your data strategy as your applications evolves—whether you simply want to replace hard-coded queries with stored procedure calls or you have a more ambitious plan in mind, such as implementing a data logging system or using database cache invalidation (a feature described in Chapter 23).
But despite these benefits, the trend of the future is toward increasingly convenient ways to handle data. This shift began with basic data binding (Chapter 15) and gained steam with rich data-display controls such as the GridView (Chapter 16) and the no-code data source controls (Chapter 15). And now, ASP.NET developers have the most ambitious high-level data access tool that Microsoft has invented so far: the Entity Framework.
At its simplest, the Entity Framework gives you a way to generate code based on the structure of your database. For example, if you point Visual Studio to the Northwind database, it will use the Entity Framework to create classes that model the various types of records (Customers, Employees, Products, and so on). It will also generate data access code that you can use to manipulate this data—for example, to query it when you want to show it in a page and to update it when you need to commit a user's change. Although the Entity Framework is technically part of ADO.NET (and it uses the familiar ADO.NET classes behind the scenes), when you use the Entity Framework, you don't worry about these details. Instead of writing your own ADO.NET code, you let the Entity Framework take care of all the details for you.
This sort of automatic code-generation system is nothing new. In fact, hundreds of companies and independent developers have spent years inventing their own technologies that automatically build data code. Some of these technologies are quite good (and some even resemble the Entity Framework). However, many suffer from serous limitations. Although they make the programmer's life easier for basic database operations, they fail to deal with more sophisticated scenarios such as calling stored procedures, working with different types of relational database products, translating data into different representations, dealing with errors, and assembling data from multiple tables into a single set of objects. Although the Entity Framework isn't a complete replacement for traditional ADO.NET, it deals with all these issues, which makes it a practical option for developers who need to quickly create data-driven applications.
Note
In fact, the Entity Framework is a replacement for a similar Microsoft technology called LINQ to SQL, which was released with .NET 3.5. Although LINQ to SQL is still in use today and has many similarities to the Entity Framework, it also has disadvantages. Most obviously, it doesn't work with databases other than SQL Server, and it doesn't give developers the flexibility to use data objects that don't exactly match the underlying tables.
The Entity Framework isn't for everyone. If you're already comfortable using the traditional ADO.NET objects, you may find that the Entity Framework adds little benefit but introduces additional complexity, because it places a whole new layer between your web page code and your database. The Entity Framework also isn't the right choice if you need to squeeze every possible millisecond of speed out of your web application, because additional layers always add some extra overhead, even when they're designed as carefully and cleverly as possible.
To get a sense of how the Entity Framework works, you can take it for a test spin. In the following sections, you'll see how to create an entity data model and use it to retrieve and update data.
The first step to use the Entity Framework is adding an entity data model to your website. Right-click your website in the Solution Explorer and choose Add New Item (or pick Website
When you add a model to a projectless website (as opposed to a web project), Visual Studio informs you that you need to place the automatically generated code in the App_Code folder. Click Yes to allow it to do that automatically.
Next, Visual Studio starts the Entity Data Model Wizard. The first step asks you whether you want to generate your model from an existing data model (which is the quickest approach) or define all your entities by hand (choose Empty Model). In most cases, the best bet is to get Visual Studio to do the work for you, because you can always alter the entities it creates or remove ones you don't need. When you've chosen, click Next to move to the next step.
Note
An entity is just another term for a data object. Each entity stores information about a particular data item. (In the simplest case, each entity corresponds to a single record in a table.)
In the next step, you pick the connection for your database. If you've already defined the connection in the Server Explorer (as you learned to do in Chapter 14), it automatically appears in the drop-down connection list (Figure 24-4). If not, you need to click the New Connection button to create it before you continue. For more information about creating database connections in Visual Studio, refer to Chapter 14.
Visual Studio stores the connection string for your connection in the <connectionStrings> section of the web.config file, which allows you to change it quickly and easily later. By default, it takes the name of your connection and adds the word Entities to name your connection string (as in NorthwindEntities), but you can type in something else if you prefer. When you're finished, click Next to continue.
In the third step, Visual Studio connects to your database and retrieves a complete catalog of all its tables, views, and stored procedures. You can then choose which of these database items you want to include in your model and generate code for. Usually, you'll pick all the tables at once, so you have access to all your data if you need it (and so that all the relationships between your tables are preserved). However, you can also choose to select individual tables, by placing a check mark next to the ones you want to include. Figure 24-5 shows an example that includes all the tables but tells Visual Studio to generate code for just one of the stored procedures (Ten Most Expensive Products).
This step of the wizard also gives you two additional options:
- Pluralize or singularize object names:
This somewhat confusing option tells Visual Studio to use a remarkably intelligent name-generation algorithm when it creates your data classes. If you choose this option (which most people do), Visual Studio will create a Product item to represent each record in a Products table. However, it will add a Products property to represent a collection of products that's referenced by a ProductCategory record. In short, Visual Studio automatically uses the names that make the most logical sense. But Visual Studio's name generating is a lot more sophisticated than simply adding and removing the letter s. For example, Visual Studio can correctly pluralize words like Address, Territory, Person to Addresses, Territories, and People. That's because it relies on a hefty pluralization dictionary that lists the singular and plural forms of English-language nouns.
- Include foreign key columns:
This option determines whether Visual Studio will include foreign key columns in your data model. If you don't, each entity will be a completely distinct object with no links to any other entity. If you do (which is the most common approach), you'll be able to navigate the relationships in your data using properties. For example, you'll be able to find all the products in a specific category using an automatically generated navigation property like ProductCategory.Products.
Once you've finished this step, click Finish. Visual Studio generates the model. Once it's finished, you'll have two new files in the App_Code project folder: the model file you specified initially (for example, Northwind.edmx) and a file that contains the automatically generated C# code for the mode (for example, Northwind.Designer.cs). You'll learn about both files in the next section.
The .edmx model file is actually a lengthy XML document that details the structure of your database. For example, it lists all the tables you chose to include, their data types, the relationships, and so on. This is the essential information that your application needs to manipulate the model. The .edmx file doesn't contain any data—that's left stored safely in the database.
Although you can view the .edmx file in a text editor, the XML is too complex and detailed to be worth editing by hand. Instead, Visual Studio provides a visual designer that represents the content in the .edmx file using a sprawling diagram of database tables. To see this designer, simply double-click your .edmx file in the Solution Explorer. Figure 24-6 shows part of the entity data model for the Northwind database.
Here's a quick guide to understanding the data model diagram:
Each box in the data model is an entity, which corresponds to a table in the database.
Each entity includes two types of properties. The first are the data properties—they correspond to the fields in the actual table. The second are navigation properties—they are a coding convenience that allows you to step from one table to the related records in another table (a trick you'll use later in this chapter).
Tip
If your tables are stuffed full of properties, the data model diagram can quickly get cluttered. In this case, you can hide a table's property list by clicking the up-pointing arrow in the top-right corner of the table box. This collapses the table box so it shows the table name only. Click the top-right corner again to expand it.
The dotted lines between the tables indicate relationships. You'll learn more about the 0..1, 1, and * characters in the "Navigation Relationships" section later in this chapter.
Using the entity data model designer, you can refine your data model. Although you're able to change virtually every detail, some customizations are easier and more useful than others.
Here are some of the basic changes you can make:
- Rearrange the entities:
Most people start by dragging around the entities on their diagram. This doesn't change the way your model works, but it may make it easier for you to survey the overall structure of your database.
- Delete entities you don't need:
Just select the entities and press the Delete key. (Don't worry, you can always add them back later by updating your data model.) You can do the same thing to remove unnecessary fields, but this general isn't worth the trouble—and it risks removing some important information from the reach of your application.
Note
When you make changes to a data model, such as deleting and renaming items, these changes affect the model, not your database.
- Rename entities:
Just double-click the entity name and type in something better. This changes the name you use in code, but the Entity Framework is smart enough to keep querying the original table in the database. For example, if you have a table like pub_info (in the Pubs sample database), you can use this trick to give it a more code-friendly moniker, like PublicationInfo.
- Rename fields:
As every database designer knows, once a database is live and in use, you can't risk renaming its fields or objects. Sometimes, that means you'll need to accept cryptic or misrepresentative field names. However, you can rename them in your model and simplify your code—just right-click the field and choose Rename.
Tip
Some developers prefer to never rename so their data model closely matches the database reality. This can help avoid possible confusion (for example, you won't need to look up the real field names to write a stored procedure). But if you go wild with field renaming, it's important to realize that there is a quick way to review the original names. Just right-click a table and choose Table Mapping to pop open a window that lists the underlying field name for each property (Figure 24-7).
- Change field properties:
Although this is too specialized for most developers, there are a number of details you can tweak using the Properties window, including the data type of each property, its default value, whether it has a fixed length, whether it allows nulls, and so on. Usually, these details are drawn from the database, but to supplement them, just right-click a field and choose Properties.
At some point, you may need to add to your model or refresh it to take into account changes that have been made to the database. Fortunately, the Entity Framework makes this task easy. Just right-click anywhere in your model, and choose Update Model From Database. You'll see a dialog box named Update Wizard, even though it has just one step (Figure 24-8).
In the Update Wizard are three tabs:
- Add:
This tab allows you to add new database objects to your model. You choose which objects you want to add by checking the corresponding check box, just as you did when you first created the model.
- Refresh:
This tab simply lists the database objects that are currently in your model (and in the back-end database). As part of the update process, Visual Studio will check all these objects for changes and regenerate them. All of this happens without disturbing the customizations you've made (such as field name mapping).
- Delete:
This tab lists the database objects that are in your model but are no longer in the database. When you perform the update, Visual Studio will remove these from your model altogether.
When you've picked the new objects you want from the Add tab and reviewed the information in the Refresh and Delete tabs, click Finish to perform the update.
Tip
Visual Studio provides a nifty tool that lets you do the reverse trick and generate a database based on a data model. You could use this trick to re-create a database on another computer, or you could use the data model designer to create a new data model and then use that data model to build a new database. To try this feature, right-click anywhere on the data model designer and choose Generate Database from Model.
The data model designer is pretty slick, but to actually use your data model, you need the help of some behind-the-scenes code. Like the data model XML, this code is long and tedious, so you won't want to read it through from start to finish. However, you'll have a better understanding about how your application works if you review its basic design and organization.
All the data model code is contained in a .Designer.cs file in the App_Code folder. (In the current example, it's Northwind.Designer.cs.)
All the code is placed in a namespace that has the name of your data model, plus the word Model, as in NorthwindModel. If you look through the code, you'll make a few observations immediately.
First, you'll notice that there are attributes (bits of descriptive information in square brackets) scattered throughout. These attributes connect various pieces of code to the back-end database they represent. For example, consider this attribute:
[EdmEntityTypeAttribute(NamespaceName="NorthwindModel", Name="Employee")]
It connects the following Employee class to the Employee table in the data model. Although attributes like these are critically important to the functioning of the Entity Framework, you don't need to pay any attention to them yourself.
The next design detail you'll notice is that the code is split into two regions: entities and contexts. Regions are simply an organization feature that lets you temporarily collapse blocks of code out of sight. However, the regions in the data model underscore the two types of classes that the Entity Framework uses.
The entities are the data objects—classes that represent the records from your database. The Entity Framework uses a separate entity class for each table, and in the Northwind database example you'll have classes like these: Customer, Product, Order, Order_Detail, Employee, Shipper, Region, and so on. They all derive from a base class called EntityObject, which gives them support for change tracking and other Entity Framework features.
As you saw in the data model, each entity class has the same basic structure. It consists of data properties (also known as primitive properties) that correspond to fields and navigation properties that let you find related data. For example, the Employee class has data properties such as FirstName, LastName, and EmployeeID. It also navigation properties like Orders and Territories.
If you dig deeper into the property procedure code, you'll find quite a bit of boilerplate code for tracking changes, raising events, and communicating with the inner mechanics of the Entity Framework. Finally, each entity class also includes a static factory method that lets you create a new, completely and correctly initialized entity object. For example, you can call Employee.CreateEmployee() and pass in the appropriate parameters to create a new employee object, as you'll see shortly.
While the entities take care of representing the data, the context takes care of the data access—the code that fetches the records you want from the database and commits changes when you're ready. Each database requires a separate context class, which means the Northwind data model example gets just one context. It's called NorthwindEntities, and it derives from a base class called ObjectContext.
The context class includes a collection for every table in your database. For example, in the NorthwindEntities class you'll have an Employees property that returns a collection of Employee entity objects, a Products property that returns a collection of Product objects, and so on. These properties are the starting point for working with your data model. For example, if you retrieve and display a collection of Product objects through the Products property (as shown in the next section), the Entity Framework quietly contacts your database, queries the Products table, and creates the objects you need. It then keeps them cached in memory so you don't need to repeat the trip again (at least not until the next postback).
Note
Unlike the data model diagram, you should not change any of the data model code, because your changes will be wiped out when the database changes and you refresh the data model. It is possible to extend the data model by writing partial classes (in another file) that add on to the automatically generated classes that Visual Studio creates. However, this is an advanced technique that's not recommended unless you have plenty of Entity Framework experience and a specific goal in mind (for example, you want to add built-in property validation to your entities).
Now that you've taken a tour of the data model code, you're ready to use it in your application. In fact, it's remarkably easy.
Consider the simple data-bound grid of products shown in Figure 24-9. To create this page, you simply need to instantiate the NorthwindEntities class (from the NorthwindModel namespace), retrieve the collection or Product objects from Products property, and call DataBind():
NorthwindEntities entities = new NorthwindEntities(); GridView1.DataSource = entities.Products; GridView1.DataBind();
The NorthwindEntities class, like all context classes, has three constructors. If you use the no-argument constructor (as in this example), it uses the connection string that's defined in the web.config file. Alternatively, you can use a constructor that allows you to supply the connection string as a string or EntityConnection object.
Whenever you access a database, you need to use the proper error handling code, and the Entity Framework is no exception. However, the Entity Framework can easily mislead developers into placing their exception handling code in the wrong place.
As you know, the risky part of data access is the connected operations you perform—for example, opening a connection and executing SQL commands. Your attempt to contact the database or communicate it with can fail for a variety of reasons. The database server could be offline, the database server could be swamped by too many simultaneous connections, the table you want to use might not exist, and so on. Based on these considerations, you probably expect to place your exception handling code around the code that reads the Products collection. In the previous example, that means this statement here:
NorthwindEntities entities = new NorthwindEntities();
GridView1.DataSource = entities.Products;
GridView1.DataBind();This seems reasonable, but it isn't correct. Although this line of code appears to access the Products collection, it actually retrieves something different—a special iterator object that's able to get the data you need as soon as you attempt to use it. This sleight of hand is part of LINQ's deferred execution feature, which you learned about earlier.
Usually, database operations with the Entity Framework happen later than you expect. They don't happen when you create the context object or access its properties. Instead, they occur when you iterate over a collection of entities or when you access a single entity object. In the previous example, the database work actually takes place when you call GridView1.DataBind(), because this tells the GridView to iterate over the Products collection and then triggers the Entity Framework to fetch your data.
Thus, the correct error handling solution looks like this:
NorthwindEntities entities = new NorthwindEntities();
GridView1.DataSource = entities.Products;
try
{
GridView1.DataBind();
}
catch (Exception err)
{
// (Do something here, like displaying an error message.)
...
}Because the Entity Framework is based on ADO.NET, you'll see many of the familiar exception objects that you saw in Chapter 14, such as SqlException.
Note
As you'll see a bit later, you can force the Entity Framework to load your data earlier. However, doing so may compromise the performance or the flexibility of your code. Most of the time, you'll stick with deferred execution, despite the potential confusion it causes.
Unfortunately, error handling in an application that uses the Entity Framework is not always this straightforward. In more complex examples, database operations might take place in several places. For example, in the following section you'll see how you can use relationships to show related information from different tables. When taking this approach, a database operation occurs whenever you begin iterating over a new collection of records with a foreach block.
So, what's the solution? One approach is to use the ToList() method, which converts any entity collection into an ordinary in-memory collection, triggering the database query in the process:
NorthwindEntities entities = new NorthwindEntities();
GridView1.DataSource = entities.Products.ToList();
GridView1.DataBind();Although this makes the data access operations more predictable, it's a dangerous practice. As you'll see in the "Querying with LINQ to Entities" section, deferred execution allows LINQ and the Entity Framework to optimize database queries. If you get into the habit of calling ToList(), you effectively circumvent this mechanism, which can harm the performance of your application.
If you don't want to worry about the precise code location where an exception might occur, you can use a better solution—just wrap everything in a giant exception handling block that catches database-specific exceptions. The most straightforward way to do this is to move your data access code to another method. Then you can wrap your method call in an exception block, like this:
try
{
// Call the method that does all the data processing and display.
DataBindProductGrid();
}
catch (SqlException err)
{
...
}Finally, as a last resort you can handle the Application.Error event described in Chapter 5 to deal with any problems that slip through the cracks.
The Entity Framework has a particularly elegant way of dealing with relationships. Rather than forcing you to use specific methods or separate queries, you can simply move from one record to its related data using the navigation properties that are defined in the data model.
It all starts with the entity model diagram. There, you'll see dotted lines that indicate each relationship. For example, consider the portion of the diagram shown in Figure 24-10.
The 0..1, 1, and * characters at either end of the dotted line give you additional information about the relationship.
For example, a relationship line that goes from 1 to * (like the one between the Order and Order_Detail entities) indicates a one-to-many relationship. Each parent record in the first table (Order), where the 1 is shown, has one or more matches in the child table (Order_Detail), where the * is shown. (To actually figure out what fields are involved in a given relationship, you need to double-click the relationship line.)
A relationship line that goes from 0..1 to * indicates a one-to-many relationship that allows null values. In other words, the 0 represents that there may be no match. For example, in the data model shown in Figure 24-10, the Order.CustomerID property can be null. That means that for each Order record, there may be one matching Customer entity or none.
The relationships that appear in your entity model diagram are also represented through navigation properties. For example, if you look at the bottom of the Order box in Figure 24-10, you'll see four navigation properties. The Customer, Employee, and Shipper properties return a linked parent record (or null if none exists). In other words, Order.Customer returns the Customer who placed the order. The Order_Details property returns a collection of linked child records (as indicated by the plural property name). In other words, Order.Order_Details provides a collection of all the order items in a given order, as Order_Detail objects.
Here's an example that uses these navigation properties to display some information in a page, with no data binding. It begins by retrieving a list of customers and then retrieves some information about the orders made by each customer:
NorthwindEntities entities = new NorthwindEntities();
// Build a string full of HTML markup.
StringBuilder sb = new StringBuilder();
foreach (Customer customer in entities.Customers)
{
// Write out the customer information in bold.sb.Append("<b>");
sb.Append(customer.CompanyName);
sb.Append("</b><br />");
// List this customer's orders.
foreach (Order order in customer.Orders)
{
sb.Append(order.OrderID.ToString());
sb.Append(" - made on date: ");
sb.Append(order.OrderDate.Value.ToShortDateString());
sb.Append("<br />");
}
// Add a horizontal line.
sb.Append("<hr /><br />");
}
// Show the HTML in a Label control.
lblData.Text = sb.ToString();Figure 24-11 shows the result.
Tip
Writing exception handling logic for this sort of code can be tricky. The database operations occur whenever you start a foreach block (either to process the list of customers or to look at the orders for a given customer). To prevent your code from descending into a tangle of spaghetti-style exception handlers, it's best to put all this logic in a dedicated method and wrap your method call in an exception handling block, as described in the previous section.
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.
As you learned earlier in this chapter, 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, just as you queried in-memory collections at the beginning of this chapter with LINQ to Objects. 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;Note
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 24-12 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.
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" OnSelectedIndexChanged="lstCategories_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Text="(Select a Category)" Value="-1"></asp:ListItem> </asp:DropDownList>
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">
...
</asp:GridView>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 NorthwindEntities entities = new NorthwindEntities();
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
lstCategories.DataTextField = "CategoryName";
lstCategories.DataValueField = "CategoryID";
lstCategories.DataSource = entities.Categories;
lstCategories.DataBind();
}
}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 void lstCategories_SelectedIndexChanged(object sender, EventArgs e)
{
int selectedID = Int32.Parse(lstCategories.SelectedValue);
if (selectedID == −1)
{
// The "(Select a Category)" item was picked.
// Don't show anything.
gridProducts.DataSource = null;
}
else
{
// Query the products in the selected category.
gridProducts.DataSource = from product in entities.Products
where product.CategoryID == selectedID
select new
{
Name = product.ProductName,
Quantity = product.QuantityPerUnit,
Stock = product.UnitsInStock
};
}
gridProducts.DataBind();
}This example uses two LINQ features. First, the where clause applies filtering to find the products in a specific category. Second, the new 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, as you learned to do in Chapter 16. 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 where 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.
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. Consider the example shown earlier in Figure 24-11, where your code shows a list of orders grouped by customers. 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:
NorthwindEntities entities = new NorthwindEntities();
foreach (Customer customer in entities.Customers)
{
...
foreach (Order order in customer.Orders)
{
...
}
}to this:
NorthwindEntities entities = new NorthwindEntities();
foreach (Customer customer in entities.Customers.Include("Orders"))
{
...
foreach (Order order in customer.Orders)
{
...
}
}This works because the Customers table includes a property named Orders. When the first foreach 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 foreach 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:
NorthwindEntities entities = new NorthwindEntities(); foreach (Customer customer in entities.Customers.Include("Orders.Order_Details.Product")) { ... foreach (Order 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.
The Entity Framework isn't limited to querying data. You can also perform inserts, updates, and deletes. The best part is that you accomplish all this by manipulating the familiar entity objects. Once again, the Entity Framework translates your C# code into the appropriate SQL database commands. All you need to do is call the SaveChanges() method of the context object. In the current example, that means a quick call to NorthwindEntities.SaveChanges() will commit any recent changes to the database.
Figure 24-13 shows a web page that puts this into practice. It includes a grid of products and three buttons: one that changes the currently selected record, one that inserts a new record, and one that deletes the selected record.
To update a record, you need to first retrieve the right entity object. The technique you use depends on the exact task you're performing, but you might get your object by iterating through a collection, following a navigation property, or performing a LINQ expression, as shown here.
However, there's one minor trick. All LINQ expressions return an iterator that allows you to traverse a collection of objects. If you have an expression that you know matches just one record (for example, one that uses the where keyword on a unique key value, such as ProductID), you can use the Single() method. This executes the query and returns the one and only matching object.
The following code selects a specific product (using a LINQ expression), updates the stock numbers, and saves the change. The two code statements in bold trigger immediate database operations and so require exception handling:
protected void cmdUpdate_Click(object sender, EventArgs e)
{
// Only allow updating if a record is currently selected.
if (gridProducts.SelectedIndex != −1)
{
// Use a LINQ expression to find the selected product.
int selectedProductID = (int)gridProducts.SelectedDataKey.Value;
var matches = from p in entities.Products
where p.ProductID == selectedProductID
select p;
// Execute the query and return the entity object.
Product product = matches.Single();
// Change the entity object.
product.UnitsInStock -= 1;
// Commit the changes back to the database.
entities.SaveChanges();
}
}Tip
SaveChanges() also returns an integer that indicates how many records were updated. So if you get a result of 0, you know that no operations were performed.
Inserting a new record is nearly as easy. The best approach is to use the static factory method of the appropriate entity class. So if you want to create a new product, call Product.CreateProduct(). Then, add the new object to the NorthwindEntities.Products collection using the AddObject() method, and commit the update with SaveChanges():
protected void cmdInsert_Click(object sender, EventArgs e)
{
// The CreateProduct() method requires the three non-nullable Product fields
// as arguments: ProductID, ProductName, and Discontinued. However, the
// ProductName isn't actually used--when the update is finsihed, it's replaced
// by the automatically generated ID that the database creates.
Product newProduct = Product.CreateProduct(0, "Thick-As-Cement Milkshake",
false);
// You can now set additional properties that aren't required.
newProduct.CategoryID = 1;
newProduct.UnitsInStock = 10;
newProduct.UnitPrice = 15.99M;
// Finally, commit the changes and insert the record in the database.
entities.Products.AddObject(newProduct);
entities.SaveChanges();
}Finally, a delete operation is the simplest data operation of all. You simply need to call the DeleteObject() method of the appropriate collection, followed by SaveChanges(). The following code retrieves the currently selected product object and removes it from the database using this approach:
protected void cmdDelete_Click(object sender, EventArgs e)
{
// Check if a row is selected.
if (gridProducts.SelectedIndex != −1)
{
// Use a LINQ expression to find the selected product.
int selectedProductID = (int)gridProducts.SelectedDataKey.Value;
var matches = from p in entities.Products
where p.ProductID == selectedProductID
select p;
// Execute the query and return the entity object.
Product product = matches.Single();
// Delete the record from the database.
entities.Products.DeleteObject(product);
entities.SaveChanges();
// Clear the selection (which may now be pointing to a different row.)
gridProducts.SelectedIndex = −1;
}
}The Entity Framework uses a fairly sophisticated change tracking system to make these operations work.
When you call SaveChanges(), the context object attempts to commit all of the changes in the data that it's tracking, including updates, deletions, and insertions. If you have related records, the Entity Framework will apply the changes in an order that makes sense (for example, it won't attempt to insert a child record before inserting a linked parent). This ability is more interesting if you use the Entity Framework in a rich desktop application, where you might keep the entity objects in memory for a long period of time, and periodically commit a batch of updates. But in a web application, where your page lives for only a few seconds (or less), you'll almost always create a new context object, perform all your database operations immediately, and then discard it.
Note
When you call SaveChanges(), all the changes are performed as part of a single transaction. If an error occurs, the entire process is rolled back, but your objects remain in their current state, with any new and edited information. That means you can programmatically resolve the problem and call SaveChanges() again.
Update errors can be caused by all the usual factors—a timeout waiting for a connection, a network error, and so on—but the most common update error is a concurrency problem.
There are two overall strategies you can use to deal with concurrency issues: you can try to avoid concurrency problems before they happen, or you can try to resolve concurrency issues after they occur.
By default, the Entity Framework uses the "last-in-wins" updating strategy. This means that new updates always succeed, but they may wipe out another user's changes. However, this doesn't pose the same problem that it does with the SqlDataSource control (which you considered in the "Strict Concurrency Checking" section at the end of Chapter 15). That's because the Entity Framework doesn't apply old values. If you change a single field and call SaveChanges(), the Entity Framework uses an update command that modifies just that field.
The Entity Framework's updating strategy can still cause a problem if two users are updating the same record at once and they are editing the same fields (or, if the changes they're making to different fields aren't consistent). Depending on the way you're using the database and the frequency of edits, this may not be a significant concern for your application. (After all, the time taken to make and commit an edit in an ASP.NET application is very small, unlike a desktop application.) But if you need to catch potential problems that involve overlapping edits, you can configure the Entity Framework to use stricter concurrency checking.
Here's how it works. Using the data model designer, you choose the fields that must match in order for an update to succeed. For example, you might decide that a Customer record update should be allowed to continue only if the ContactName and CompanyName fields haven't been changed by another user since the record was retrieved. To do this, start by clicking the ContactName field in the Customer table box. Then, change the Concurrency Mode property in the Properties window from None to Fixed. Finally, repeat this process for the CompanyName field.
Now, if you attempt to commit a change and it overlaps with records that another user has changed, the edit will fail with an OptimisticConcurrencyException. At this point, you can try to resolve the problem or give the user the choice of reapplying the update.
The Entity Framework examples in this chapter so far have used pure code to retrieve, manipulate, and bind data. However, ASP.NET also includes an EntityDataSource control that you can use to perform many of these tasks automatically.
Much like the SqlDataSource control, when you use the EntityDataSource control, you don't need to write any code. But the EntityDataSource control goes one step further—not only can you avoid writing C# code, but you can avoid the messy details of writing SQL queries to select and update data. This makes it a perfect tool for small or medium-scale applications and applications that don't need to be carefully tuned to get every last ounce of performance. On the other hand, it's also sure to exasperate database purists who prefer to have complete control over every detail. If the EntityDataSource lacks the features, performance, or flexibility you require, you'll need to use custom data access code (possibly with the help of the ObjectDataSource), as described in Chapter 22.
To get a feel for the capabilities and overall goals of the EntityDataSource, it's worth building a simple example. In the following example you'll see how to build the web page shown in Figure 24-14, which allows you to insert, delete, and update records in the Employees table.
Assuming you've already created your data model, the next step is to add the control you want to use to display your data. In this example, two controls are used—a GridView that allows you to select an employee and a DetailsView that allows you to change it, remove it, or create a new one. You can add both controls straight from the Toolbox and use the AutoFormat feature to give them a pleasant color scheme.
The third ingredient is the data source that links the context object to your data controls. In this example, you'll need two data source controls—one that retrieves all the employee records (for the GridView) and one that retrieves a single employee record (for the DetailsView). The latter will also perform the editing, inserting, and deleting operations.
To create your first data source, drop an EntityDataSource control onto your web page. The quickest way to configure it is to use the wizard (select the data source control, click the arrow in the top-right corner, and choose Configure Data Source). The wizard has just two steps. The first step displays all the derived ObjectContext classes in your project (such as NorthwindEntities) and prompts you to choose one.
The second step asks you what columns you want to include. To include all, choose the Select All item at the top of the list (Figure 24-15).
When you've finished the wizard, you'll end up with a fairly straightforward control tag, like this:
<asp:EntityDataSource ID="sourceEmployees" runat="server" ConnectionString="name=NorthwindEntities" EnableFlattening="False" DefaultContainerName="NorthwindEntities" EntitySetName="Employees"> </asp:EntityDataSource>
The ConnectionString property indicates the name of the connection string that's stored in the web.config file. The DefaultContainerName indicates the name of the data model that you're using, and the EntitySetName indicates the collection you're binding from the context object. This data source assumes you're including all the fields (although you won't necessarily be displaying all these fields, depending on how you've configured your data display control).
If you selected a subset of columns in the second step of the wizard (Figure 13-12), you'll also see a Select property that defines a projection, like this:
<asp:EntityDataSource ID="sourceEmployees" runat="server"
ConnectionString="name=NorthwindEntities" EnableFlattening="False"
DefaultContainerName="NorthwindEntities"
EntitySetName="Employees"
Select="it.[LastName], it.[FirstName]">
</asp:EntityDataSource>If the syntax looks a little strange, just remember that the EntityDataSource uses the name it for the data object when it performs a projection. In other words, the EntityDataSource shown earlier uses an expression that would be written like this:
var matches = fromitin employees select new {LastName =it.LastName, FirstName =it.FirstName};
You can use the sourceEmployees data source to fill the grid shown in Figure 24-13. Simply set the GridView.DataSourceID property to sourceEmployees. Next, make sure that the GridView supports selection. The DataKeyNames property should be set to EmployeeID, and a Select column should be visible in the grid (to add it, check the Enable Selection option in the GridView smart tag or set the GridView.AutoGenerateSelectButton property by hand).
The DetailsView shows the currently selected employee in the grid. You learned how to create this design with the SqlDataSource, but the EntityDataSource works a bit differently because it doesn't allow you to define the Select command directly. To start, begin by creating a new EntityDataSource that has the same characteristics as the first one. Then, you need to build the where operator for the LINQ expression by setting the EntityDataSource.Where property. This will filter the query to include just the single matching employee entity.
The easiest way to build this part is to select the EntityDataSource object on the design surface of your page, find the Where property in the Properties window, and then click the ellipsis (...) button. This opens a window named Expression Editor. Make sure the "Automatically generate the Where expression" check box is selected at the top of this window. Then, click the Add Parameter button, and enter the name of the field you want to use for your filter condition (in this case, it's EmployeeID). In the Parameter Source drop-down list (to the bottom right), choose Control. In the ControlID list (just underneath), choose your GridView (in this case, that's GridView1). Figure 24-16 shows the completed dialog box, with all the information in place.
This data source attempts to find an employee with an EmployeeID that matches the selected value in the GridView. Here's the complete markup that's generated:
<asp:EntityDataSource ID="sourceSingleEmployee" runat="server" ConnectionString="name=NorthwindEntities" DefaultContainerName="NorthwindEntities" EnableFlattening="False" EntitySetName="Employees" Where="" AutoGenerateWhereClause="True"><WhereParameters><asp:ControlParameter ControlID="GridView1" Name="EmployeeID"PropertyName="SelectedValue" /></WhereParameters></asp:EntityDataSource>
Now, when you select an employee in the GridView, the full details will appear in the DetailsView.
The final step in this example is to configure the DetailsView and second EntityDataSource to support update, insert, and delete operations. To enable these, simply select the sourceSingleEmployee on the design surface of your page and set the EnableInsert, EnableDelete, and EnableUpdate properties to true using the Properties window. Alternatively, you can use the check boxes in the Configure Data Source Wizard, as shown in Figure 24-15. Either way, this simply sets a few similarly named properties in the EntityDataSource control tag:
<asp:EntityDataSource ID="sourceSingleEmployee" runat="server"
ConnectionString="name=NorthwindEntities"
DefaultContainerName="NorthwindEntities" EnableFlattening="False"
EntitySetName="Employees"
Where="" AutoGenerateWhereClause="True"
EnableInsert="True" EnableUpdate="True" EnableDelete="True">
<WhereParameters>
<asp:ControlParameter ControlID="GridView1" Name="EmployeeID"
PropertyName="SelectedValue" />
</WhereParameters>
</asp:EntityDataSource>Remarkably, this is all you need to complete the example. The EntityDataSource will now automatically use the NorthwindEntities to perform these record operations. When deleting a record, it uses the DeleteObject() method you considered earlier. When adding a record, it creates a new Employee entity object and calls AddObject() to insert it into the collection. When modifying a record, it simply sets the corresponding properties. No matter which operation you're using, it ends by calling SaveChanges() to apply the new data.
In this chapter, you learned about LINQ, a core feature of the .NET Framework, with deep support in the C# and Visual Basic languages. LINQ provides a declarative model for retrieving and processing data that allows you to use the same syntax with a wide range of different types of data.
LINQ really shines when you combine it with the Entity Framework, a new model for code-free database queries and updates. Although the Entity Framework introduces its own new complexities and performance considerations, such as deferred execution and lazy loading, it also saves developers from the real drudgery of data access programming, such as writing dozens of data objects and ADO.NET routines. Ultimately, it's up to you to decide which style you prefer—the up-front, straightforward ADO.NET approach, or the higher-level Entity Framework, which adds plenty of handy tools and a few occasional headaches.