Apress pro LINQ Language Integrated Query in C# 2008 phần 8 pps

■ ■ ■

PART 5

LINQ to SQL

Rattz_789-3.book Page 379 Tuesday, October 16, 2007 2:21 PM

Rattz_789-3.book Page 380 Tuesday, October 16, 2007 2:21 PM

381

■ ■ ■

CHAPTER 12

LINQ to SQL Introduction

Listing 12-1. A Simple Example Updating the ContactName of a Customer in the Northwind Database

// Create a DataContext.

Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");

// Retrieve customer LAZYK.

Customer cust = (from c in db.Customers

where c.CustomerID == "LAZYK"

select c).Single<Customer>();

// Update the contact name.

cust.ContactName = "Ned Plimpton";

try

{

// Save the changes.

db.SubmitChanges();

}

// Detect concurrency conflicts.

catch (ChangeConflictException)

{

// Resolve conflicts.

db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);

}

■Note This example requires generation of entity classes, which I will cover later in this chapter.

In Listing 12-1, I used LINQ to SQL to query the record whose CustomerID field is "LAZYK" from

the Northwind database Customers table and to return a Customer object representing that record.

I then updated the Customer object’s ContactName property and saved the change to the Northwind

database by calling the SubmitChanges method. That’s not much code considering it is also detecting

concurrency conflicts and resolving them if they occur.

Run Listing 12-1 by pressing Ctrl+F5. There is no console output, but if you check the database,

you should see that the ContactName for customer LAZYK is now "Ned Plimpton".

Rattz_789-3.book Page 381 Tuesday, October 16, 2007 2:21 PM

382 CHAPTER 12 ■ LINQ TO SQL INTRODUCTION

■Note This example makes a change to the data in the database without changing it back. The original value of

the ContactName for customer LAZYK is "John Steel". You should change this back so that no subsequent

examples behave improperly. You could change it manually, or you could just change the example code to set it

back, and run the example again.

This book uses an extended version of the Northwind database. Please read the section in this chapter titled

“Obtaining the Appropriate Version of the Northwind Database” for details.

Introducing LINQ to SQL

At this point, I have discussed using LINQ with in-memory data collections and arrays, XML, and

DataSets. Now, I will move on to what many seem to feel is the most compelling reason to use LINQ,

LINQ to SQL. I say that because when I look at the MSDN forum for LINQ, the majority of the posts

seem to focus on LINQ to SQL. I think many developers are overlooking the significance of LINQ as

a general purpose query language and the multitude of ways it can be utilized. Hopefully, I have

convinced you of this already through the previous chapters.

LINQ to SQL is an application programming interface (API) for working with SQL Server data￾bases. In the current world of object-oriented programming languages, there is a mismatch between

the programming language and the relational database. When writing an application, we model

classes to represent real-world objects such as customers, accounts, policies, and flights. We need a

way to persist these objects so that when the application is restarted, these objects and their data are

not lost. However, most production-caliber databases are still relational and store their data as records in

tables, not as objects. A customer class may contain multiple addresses and phone numbers stored

in collections that are child properties of that customer class; once persisted, this data will most likely be

stored in multiple tables, such as a customer table, an address table, and a phone table.

Additionally, the data types supported by the application language differ from the database data

types. Developers left to their own devices are required to write code that knows how to load a customer

object from all of the appropriate tables, as well as save the customer object back to the appropriate

tables, handling the data type conversion between the application language and the database. This

is a tedious, and often error-prone, process. Because of this object-relational mapping (ORM) problem,

often referred to as the object-relational impedance mismatch, a plethora of prewritten ORM software

solutions have been designed through the years. LINQ to SQL is Microsoft’s entry-level LINQ-enabled

ORM implementation for SQL Server.

Notice that I said “for SQL Server.” LINQ to SQL is exclusive to SQL Server. LINQ, however, is

not, and hopefully, other database vendors are or will be at work implementing their own LINQ APIs.

I personally would like to see a LINQ to DB2 API, and I am sure many others would like to see LINQ

to Oracle, LINQ to MySQL, LINQ to Sybase, and perhaps others.

■Note LINQ to SQL only works with SQL Server or SQL Express. To use LINQ with other databases, additional

LINQ APIs will need to be written by the appropriate database vendors. Until then, or perhaps as an alternative,

consider using LINQ to DataSet.

You may have also noticed that I said LINQ to SQL is an entry-level ORM implementation. If you

find it is not powerful or flexible enough to meet your requirements, you may want to investigate

LINQ to Entities. While I do not cover LINQ to Entities in this book, it is alleged to be more powerful

and flexible than LINQ to SQL. Be aware, though, that the increase in power comes coupled with

additional complexity. Also, LINQ to Entities is not as mature as LINQ to SQL.

Rattz_789-3.book Page 382 Tuesday, October 16, 2007 2:21 PM

CHAPTER 12 ■ LINQ TO SQL INTRODUCTION 383

Most ORM tools attempt to abstract the physical database into business objects. With that

abstraction, we typically lose the ability to perform SQL queries, which is a large part of the attraction

to relational databases. This is what separates LINQ to SQL from many of its contemporaries. Not

only do we get the convenience of business objects that are mapped to the database, we get a full￾blown query language, similar to the already familiar SQL, thrown in to boot.

■Tip LINQ to SQL is an entry-level ORM tool that permits powerful SQL queries.

In addition to providing LINQ query capabilities, as long as your query returns LINQ to SQL

entity objects, as opposed to returning single fields, named nonentity classes, or anonymous classes,

LINQ to SQL also provides change tracking and database updates, complete with optimistic concur￾rency conflict detection and resolution, and transactional integrity.

In Listing 12-1, I first had to instantiate an instance of the Northwind class. That class is derived

from the DataContext class, and I will cover this class in-depth in Chapter 16. For now, consider it a

supercharged database connection. It also handles updating the database for us, as you can see when I

later call the SubmitChanges method on it. Next, I retrieved a single customer from the Northwind

database into a Customer object. That Customer object is an instantiation of the Customer class, which

is an entity class that either had to be written or generated. In this case, the Customer class was generated

for me by the SQLMetal utility, as was the Northwind class for that matter. After retrieving the customer,

I updated one of the Customer object’s properties, ContactName, and called the SubmitChanges method

to persist the modified contact name to the database. Please notice that I wrapped the call to the

SubmitChanges method in a try/catch block and specifically caught the ChangeConflictException

exception. This is for handling concurrency conflicts, which I will cover in detail in Chapter 17.

Before you can run this example or any of the others in this chapter, you will need to create entity

classes for the Northwind database. Please read the section in this chapter titled “Prerequisites for

Running the Examples” to guide you through creation of the necessary entity classes.

LINQ to SQL is a complex subject, and to provide any example requires involving many LINQ to

SQL elements. In the first example at the beginning of this chapter, I am utilizing a derived DataContext

class, which is the Northwind class; an entity class, which is the Customer class; concurrency conflict

detection and resolution; and database updates via the SubmitChanges method. I can’t possibly explain

all these concepts simultaneously. So, I need to give you some background on each of these compo￾nents before I begin so that you will have a basic understanding of the foundation of LINQ to SQL.

Rest assured that I will cover each of these concepts in agonizing detail later in the subsequent LINQ

to SQL chapters.

The DataContext

The DataContext is the class that establishes a connection to a database. It also provides several

services that provide identity tracking, change tracking, and change processing. I’ll cover each of

these services in more detail in Chapter 16. For now, just know that it is the DataContext class that is

connecting us to the database, monitoring what we have changed, and updating the database when

we call its SubmitChanges method.

It is typical with LINQ to SQL to use a class derived from the DataContext class. The name of the

derived class typically is the same as the database it is mapped to. I will often refer to that derived

class in the LINQ to SQL chapters as [Your]DataContext, because its name is dependent on the data￾base for which it is being created.

In my examples, my derived DataContext class will be named Northwind, because it was gener￾ated by the SQLMetal command-line tool, and SQLMetal names the generated, derived DataContext

class after the database for which it is generated.

Rattz_789-3.book Page 383 Tuesday, October 16, 2007 2:21 PM