第 94 页

invalid model will not stop the project from successfully compiling.

updating an entity Model with database changes

The structure of databases tends to be updated frequently throughout the development of projects, so

you need a way to update your model based on the changes in the database. To do so, right-click the

designer surface and select the Update Model

from Database menu item. This opens the

Update Wizard (as shown in Figure 29-13)

that will obtain the schema from the database,

compare it to the current storage model, and

extract the differences. These differences

are displayed in the tabs in the wizard —

the Add tab contains database objects that

aren’t in your storage model, the Refresh tab

contains database objects that are different in

the database from their corresponding storage

model objects, and the Delete tab contains

database objects that are in the storage model

but no longer in the database.

Select the items from these three tabs that

you want to add, refresh, or delete, and click

the Finish button to have your entity model

updated accordingly. fiGure 29-13

636 .

chaPter 29 The Ado.neT enTiTy FrAmeWork

queryinG the entity Model

Now that you’ve created your entity model you will no doubt want to put it to the test by querying

it, working with and modifying the data returned, and saving changes back to the database. The

Entity Framework provides a number of ways to query your entity model, including LINQ to Entities,

Entity SQL, and query builder methods. However, this chapter focuses specifically on querying the

model with LINQ to Entities.

linq to entities overview

LINQ was covered in the previous chapter, specifically focusing on the use of LINQ to Objects, LINQ

to SQL, and LINQ to XML; however, the Entity Framework has extended LINQ with its own

implementation called LINQ to Entities. LINQ to Entities enables you to write strongly typed LINQ

queries against your entity model, and have it return the data as objects (entities). LINQ to Entities

handles the mapping of your LINQ query against the conceptual entity model to a SQL query against

the underlying database schema. This is an extraordinarily powerful feature of the Entity Framework,

abstracting away the need to write SQL to work with data in a database.

Getting an object context

To connect to your entity model you need to create an instance of the object context in your entity

model. So that the object context is disposed of once you’re finished you’ll use a using block to

maintain the lifetime of the variable:

Vb

Using context As New AdventureWorksLTEntities()

'Queries go here

End Using

c#

using (AdventureWorksLTEntities context = new AdventureWorksLTEntities())

{

// Queries go here

}

Note that any queries placed within the scope of the using block for the object

context aren’t necessarily executed while the object context is in scope. As

detailed in the “Debugging and Execution” section of Chapter 28, the execution

of LINQ queries is deferred until the results are iterated (i.e., the query is not run

against the database until the code needs to use its results). This means that if the

variable containing the context has gone out of scope before you are actually

using the results, the query will fail. Therefore, ensure that you have requested

the results of the query before letting the context variable go out of scope.

Querying the entity Model .

637

If you need to specify the connection to the database (such as if you need to pass in user credentials or

use a custom connection string rather than what’s in the App.config file) you can do so by passing the

crud operations

It would be hard to argue against the most important database queries being the CRUD (Create/Read/

Update/Delete) operations. Read operations return data from the database, whereas the Create/Update/

Delete operations make changes to the database. Create some LINQ to Entities queries to demonstrate

retrieving some data from the database (as entities), modify these entities, and then save the changes

back to the database.

.)AdventureWorksLTEntitiesconnection string to the constructor of the object context (in this case

While you are getting up to speed on writing LINQ to Entities queries, you may

find LINQPad to be a useful tool, providing a “scratchpad” where you can write

queries against an entity model and have them executed immediately so you can

test your query. You can get LINQPad from http://www.linqpad.net.

Data retrieval

Just like SQL, LINQ to Entity queries consist of selects, where clauses, order by clauses, and group

by clauses. Take a look at some examples of these. The results of the queries can be assigned to

the ItemsSource property of the DataGrid control created earlier in the MainWindow.xaml file,

enabling you to visualize the results:

Vb

dgEntityFrameworkData.ItemsSource = qry

c#

dgEntityFrameworkData.ItemsSource = qry;

There are actually a number of ways to query the entity model within LINQ to Entities, but

we’ll just focus on one method here. We’ll also assume that the query is between the using block

demonstrated previously, with the variable containing the instance of the object context simply

called context.

To return the entire collection of customers in the database you can write a select query like so:

Vb

Dim qry = From c In context.Customers

Select c

c#

var qry = from c in context.Customers

select c;

638 .

chaPter 29 The Ado.neT enTiTy FrAmeWork

You can filter the results with a where clause, which can even include functions/properties such as

StartsWith, Length, and so on. This example returns all the customers whose last name starts

with A:

Vb

Dim qry = From c In context.Customers

Where c.Name.LastName.StartsWith("A")

Select c

c#

var qry = from c in context.Customers

where c.Name.LastName.StartsWith("A")

select c;

You can order the results with an order by clause — in this example you are ordering the results by

the customer’s last name:

Vb

Dim qry = From c In context.Customers

Order By c.Name.LastName Ascending

Select c

c#

var qry = from c in context.Customers

orderby c.Name.LastName ascending

select c;

You can group and aggregate the results with a group by clause — in this example you are grouping

the results by the salesperson, returning the number of sales per salesperson. Note that instead of

returning a Customer entity you are requesting that LINQ to Entities returns an implicitly typed

variable containing the salesperson and his sales count:

Vb

Dim qry = From c In context.Customers

Group c By salesperson = c.SalesPerson Into grouping = Group

Select New With

{

.SalesPerson = salesperson,

.SalesCount = grouping.Count()

}

c#

var qry = from c in context.Customers

group c by c.SalesPerson into grouping

select new

{

SalesPerson = grouping.Key,

SalesCount = grouping.Count()

};

Querying the entity Model .

639

It can be very useful to monitor the SQL queries generated and executed by the

Entity Framework to ensure that the interaction between the entity model and

the database is what you’d expect. For example, you may find that because an

association is being lazy loaded, when traversing the entity hierarchy across this

association in a loop that you are actually making repeated and excessive trips to

the database. Therefore, if you have SQL Server Standard or higher you can use

the SQL Profiler to monitor the queries being made to the database and adjust

your LINQ queries if necessary. If you are using SQL Server Express you can

download a free open source SQL Server profiler called SQL Express Profiler

from http://code.google.com/p/sqlexpressprofiler/downloads/list.

saving Data

The Entity Framework employs change tracking — where you make changes to data in the model,

it will track the data that has changed, and when you request that the changes are saved back

to the database it will commit the changes to the database as a batch. This commit is via the

SaveChanges() method on the object context:

Vb

context.SaveChanges()

c#

context.SaveChanges();

A number of ways to update data exists (for different scenarios), but for purposes of simplicity this

example takes the simple straightforward approaches.

Update Operations

Assume you want to modify the name of a customer (with an ID of 1), which you’ve retrieved

like so:

Vb

Dim qry = From c In context.Customers

Where c.CustomerID = 1

Select c

Dim customer As Customer = qry.FirstOrDefault()

c#

var qry = from c in context.Customers

where c.CustomerID == 1

select c;

Customer customer = qry.FirstOrDefault();

640 .

chaPter 29 The Ado.neT enTiTy FrAmeWork

All you need to do is modify the name properties on the customer entity you’ve retrieved, the Entity

Framework will automatically track that this customer has changed, and then call the SaveChanges()

method on the object context:

Vb

customer.Name.FirstName = "Chris"

customer.Name.LastName = "Anderson"

context.SaveChanges()

c#

customer.Name.FirstName = "Chris";

customer.Name.LastName = "Anderson";

context.SaveChanges();

Create Operations

To add a new entity to an entity set, simply create an instance of the entity, assign values to its

properties, and then save the changes:

Vb

Customer customer = new Customer()

customer.Name.FirstName = "Chris"

customer.Name.LastName = "Anderson"

customer.Name.Title = "Mr."

customer.PasswordHash = "*****"

customer.PasswordSalt = "*****"

customer.ModifiedDate = DateTime.Now

context.Customers.AddObject(customer)

context.SaveChanges()

c#

Customer customer = new Customer();

customer.Name.FirstName = "Chris";

customer.Name.LastName = "Anderson";

customer.Name.Title = "Mr.";

customer.PasswordHash = "*****";

customer.PasswordSalt = "*****";

customer.ModifiedDate = DateTime.Now;

context.Customers.AddObject(customer);

context.SaveChanges();

After the changes are saved back to the database your entity will now have the primary key that was

automatically generated for the row by the database assigned to its CustomerID property.

Delete Operations

To delete an entity, simply use the DeleteObject() method on its containing entity set:

Querying the entity Model .

641

Vb

context.Customers.DeleteObject(customer)

c#

context.Customers.DeleteObject(customer);

navigating entity associations

Of course, working with data rarely involves the use of a single table/entity, which is where the

navigation properties used by associations are very useful indeed. A customer can have one or more

addresses, which is modeled in your entity model by the Customer entity having an association with

the CustomerAddress entity (a one-to-many relationship), which then has an association with the

Address entity (a many-to-one relationship). The navigation properties for these associations make it

very easy to obtain the addresses for a customer.

Start by using the query from earlier to return a customer entity:

Vb

Dim qry = From c In context.Customers

Where c.CustomerID = 1

Select c

Dim customer As Customer = qry.FirstOrDefault()

c#

var qry = from c in context.Customers

where c.CustomerID == 1

select c;

Customer customer = qry.FirstOrDefault();

You can enumerate and work with the addresses for the entity via the navigation properties like so:

Vb

For Each customerAddress As CustomerAddress In customer.CustomerAddresses

Dim address As Address = customerAddress.Address

'Do something with the address entity

Next customerAddress

c#

foreach (CustomerAddress customerAddress in customer.CustomerAddresses)

{

Address address = customerAddress.Address;

// Do something with the address entity

}

Note how you navigate through the CustomerAddress entity to get to the Address entity for the

customer. Because of these associations there’s no need for joins in the Entity Framework.

642 .

chaPter 29 The Ado.neT enTiTy FrAmeWork

However, there is an issue here with what you’re doing. What is happening here is as you navigate

through the CustomerAddress entity to the Address entity is that it’s doing another database query

to get the collection of CustomerAddress entities for the customer, and then in the for loop doing yet

another database query for each CustomerAddress entity to get the corresponding Address entity!

This is known as lazy loading — where the entity model only requests data from the database when

it actually needs it. This can have some advantages in certain situations, however, in this scenario

it results in a lot of calls to the database, increasing the load on the database server, reducing the

performance of your application, and reducing your application’s scalability. If you then did this

for a number of customer entities in a loop, that would add even more strain to the system. So it’s

definitely not an ideal scenario as is.

Instead, you can request from the entity model when querying for the customer entity that it eagerly

loads its associated CustomerAddress entities and their Address entities. This will request all the data

in one database query, thus removing all the aforementioned issues, because when navigating through

these associations the entity model will now have the entities in memory and not have to go back

to the database to retrieve them. The way to request that the model does this is to use the Include

method, specifying the path (as a string) of the navigation properties (dot notation) to the associated

entities whose data you also want to retrieve from the database at the same time as the actual entities