Apress pro LINQ Language Integrated Query in C# 2008 phần 9 ppt

436 CHAPTER 14 ■ LINQ TO SQL DATABASE OPERATIONS

Notice that in the join statement in Listing 14-14, I direct the join results into the temporary

sequence named temp. That temporary sequence name can be whatever you want, as long as it

doesn’t conflict with any other name or keyword. Then I perform a subsequent query on the results

of the temp sequence passed to the DefaultIfEmpty operator. Even though I haven’t covered it yet, the

DefaultIfEmpty operator called in Listing 14-14 is not the same operator that was discussed in

Chapter 4. As I will explain shortly, LINQ to SQL queries are translated into SQL statements, and

those SQL statements are executed by the database. SQL Server has no way to call the DefaultIfEmpty

standard query operator. Instead, that operator call will be translated into the appropriate SQL state￾ment. This is why I wanted the DataContext logging to be enabled.

Also, notice that I access the city name from the Suppliers table instead of the temp collection.

I did this because I know there will always be a record for the supplier, but for suppliers without a

matching customer, there will be no city in the joined results in the temp collection. This is different

than the previous example of the inner join where I obtained the city from the joined table. In that

example, it didn’t matter which of the tables I got the city from, because if a matching customer

record didn’t exist, there would be no record anyway since an inner join was performed.

Let’s look at the results of Listing 14-14.

SELECT [t0].[CompanyName], [t1].[CompanyName] AS [value], [t0].[City]

FROM [dbo].[Suppliers] AS [t0]

LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t0].[City] = [t1].[City]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

London: Exotic Liquids - Around the Horn

London: Exotic Liquids - B's Beverages

London: Exotic Liquids - Consolidated Holdings

London: Exotic Liquids - Eastern Connection

London: Exotic Liquids - North/South

London: Exotic Liquids - Seven Seas Imports

New Orleans: New Orleans Cajun Delights -

Ann Arbor: Grandma Kelly's Homestead -

Tokyo: Tokyo Traders -

Oviedo: Cooperativa de Quesos 'Las Cabras' -

Osaka: Mayumi's -

Melbourne: Pavlova, Ltd. -

Manchester: Specialty Biscuits, Ltd. -

Göteborg: PB Knäckebröd AB -

Sao Paulo: Refrescos Americanas LTDA - Comércio Mineiro

Sao Paulo: Refrescos Americanas LTDA - Familia Arquibaldo

Sao Paulo: Refrescos Americanas LTDA - Queen Cozinha

Sao Paulo: Refrescos Americanas LTDA - Tradiçao Hipermercados

Berlin: Heli Süßwaren GmbH & Co. KG - Alfreds Futterkiste

Frankfurt: Plutzer Lebensmittelgroßmärkte AG -

Cuxhaven: Nord-Ost-Fisch Handelsgesellschaft mbH -

Ravenna: Formaggi Fortini s.r.l. -

Sandvika: Norske Meierier -

Bend: Bigfoot Breweries -

Stockholm: Svensk Sjöföda AB -

Paris: Aux joyeux ecclésiastiques - Paris spécialités

Paris: Aux joyeux ecclésiastiques - Spécialités du monde

Boston: New England Seafood Cannery -

Singapore: Leka Trading -

Lyngby: Lyngbysild -

Rattz_789-3C14.fm Page 436 Tuesday, October 16, 2007 1:27 PM

CHAPTER 14 ■ LINQ TO SQL DATABASE OPERATIONS 437

Zaandam: Zaanse Snoepfabriek -

Lappeenranta: Karkki Oy -

Sydney: G'day, Mate -

Montréal: Ma Maison - Mère Paillarde

Salerno: Pasta Buttini s.r.l. -

Montceau: Escargots Nouveaux -

Annecy: Gai pâturage -

Ste-Hyacinthe: Forêts d'érables -

As you can see in the output of Listing 14-14, I got at least one record for every supplier, and you

can see that some suppliers do not have a matching customer, thereby proving the outer join was

performed. But, if there is any doubt, you can see the actual generated SQL statement and that clearly

is performing an outer join.

To Flatten or Not to Flatten

In the examples in Listing 14-13 and Listing 14-14, I projected my query results into a flat structure.

By this, I mean an object was created from an anonymous class where each field requested is a

member of that anonymous class. Contrast this with the fact that, instead of creating a single anon￾ymous class containing each field I wanted, I could have created an anonymous class composed of

a Supplier object and matching Customer object. In that case, there would be the topmost level of the

anonymous class, and a lower level containing a Supplier object and either a Customer object or the

default value provided by the DefaultIfEmpty operator, which would be null.

If I take the flat approach, as I did in the two previous examples, because the projected output

class is not an entity class, I will not be able to perform updates to the output objects by having the

DataContext object manage persisting the changes to the database for me. This is fine for data that

will not be changed. However, sometimes you may be planning on allowing updates to the retrieved

data. In this case, using the nonflat approach would allow you to make changes to the retrieved

objects and have the DataContext object manage the persistence. I will cover this in more depth in

Chapter 16. For now, let’s just take a look at Listing 14-15, which contains an example that isn’t flat.

Listing 14-15. Returning Nonflat Results so the DataContext Can Manage Persistence

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

var entities = from s in db.Suppliers

join c in db.Customers on s.City equals c.City into temp

from t in temp.DefaultIfEmpty()

select new { s, t };

foreach (var e in entities)

{

Console.WriteLine("{0}: {1} - {2}", e.s.City,

e.s.CompanyName,

e.t != null ? e.t.CompanyName : "");

}

In Listing 14-15, instead of returning the query results into a flat anonymous object with a

member for each desired field, I return the query results in an anonymous object composed of the

Supplier and potentially Customer entity objects. Also notice that in the Console.WriteLine method

call, I still have to be concerned that the temporary result can be a null if no matching Customer

object exists. Let’s take a look at the results of Listing 14-15.

Rattz_789-3C14.fm Page 437 Tuesday, October 16, 2007 1:27 PM

438 CHAPTER 14 ■ LINQ TO SQL DATABASE OPERATIONS

London: Exotic Liquids - Around the Horn

London: Exotic Liquids - B's Beverages

London: Exotic Liquids - Consolidated Holdings

London: Exotic Liquids - Eastern Connection

London: Exotic Liquids - North/South

London: Exotic Liquids - Seven Seas Imports

New Orleans: New Orleans Cajun Delights -

Ann Arbor: Grandma Kelly's Homestead -

Tokyo: Tokyo Traders -

Oviedo: Cooperativa de Quesos 'Las Cabras' -

Osaka: Mayumi's -

Melbourne: Pavlova, Ltd. -

Manchester: Specialty Biscuits, Ltd. -

Göteborg: PB Knäckebröd AB -

Sao Paulo: Refrescos Americanas LTDA - Comércio Mineiro

Sao Paulo: Refrescos Americanas LTDA - Familia Arquibaldo

Sao Paulo: Refrescos Americanas LTDA - Queen Cozinha

Sao Paulo: Refrescos Americanas LTDA - Tradiçao Hipermercados

Berlin: Heli Süßwaren GmbH & Co. KG - Alfreds Futterkiste

Frankfurt: Plutzer Lebensmittelgroßmärkte AG -

Cuxhaven: Nord-Ost-Fisch Handelsgesellschaft mbH -

Ravenna: Formaggi Fortini s.r.l. -

Sandvika: Norske Meierier -

Bend: Bigfoot Breweries -

Stockholm: Svensk Sjöföda AB -

Paris: Aux joyeux ecclésiastiques - Paris spécialités

Paris: Aux joyeux ecclésiastiques - Spécialités du monde

Boston: New England Seafood Cannery -

Singapore: Leka Trading -

Lyngby: Lyngbysild -

Zaandam: Zaanse Snoepfabriek -

Lappeenranta: Karkki Oy -

Sydney: G'day, Mate -

Montréal: Ma Maison - Mère Paillarde

Salerno: Pasta Buttini s.r.l. -

Montceau: Escargots Nouveaux -

Annecy: Gai pâturage -

Ste-Hyacinthe: Forêts d'érables –

In the output for Listing 14-15, you can see that some suppliers do not have customers in their

cities. Unlike the sequence of anonymous objects returned by the query in Listing 14-14, the anony￾mous objects returned by the query in Listing 14-15 contain entity objects of type Supplier and

Customer. Because these are entity objects, I can take advantage of the services provided by the

DataContext to manage the changes to them, and their persistence to the database.

Deferred Query Execution

I know by now you have probably read my explanation of deferred query execution a dozen times.

But, being neurotic, I am always paranoid that you may have skipped some pertinent part of a

previous chapter. In this case, I am concerned you might have missed the explanation of deferred

query execution.

Deferred query execution refers to the fact that a LINQ query of any type—be it a LINQ to SQL

query, a LINQ to XML query, or a LINQ to Objects query—may not actually be executed at the time

it is defined. Take the following query, for example:

Rattz_789-3C14.fm Page 438 Tuesday, October 16, 2007 1:27 PM

CHAPTER 14 ■ LINQ TO SQL DATABASE OPERATIONS 439

IQueryable<Customer> custs = from c in db.Customers

where c.Country == "UK"

select c;

The database query is not actually performed when this statement is executed; it is merely

defined and assigned to the variable custs. The query will not be performed until the custs sequence

is enumerated. This has several repercussions.

Repercussions of Deferred Query Execution

One repercussion of deferred query execution is that your query can contain errors that will cause

exceptions but only when the query is actually performed, not when defined. This can be very

misleading when you step over the query in the debugger and all is well, but then, farther down in

the code, an exception is thrown when enumerating the query sequence. Or, perhaps you call another

operator on the query sequence that results in the query sequence being enumerated.

Another repercussion is that, since the SQL query is performed when the query sequence is

enumerated, enumerating it multiple times results in the SQL query being performed multiple times.

This could certainly hamper performance. The way to prevent this is by calling one of the standard

query operator conversion operators, ToArray<T>, ToList<T>, ToDictionary<T, K>, or ToLookup<T, K>,

on a sequence. Each of these operators will convert the sequence on which it is called to a data struc￾ture of the type specified, which in effect, caches the results for you. You can then enumerate that

new data structure repeatedly without causing the SQL query to be performed again and the results

potentially changing.

Taking Advantage of Deferred Query Execution

One advantage of deferred query execution is that performance can be improved while at the same

time allowing you to reuse previously defined queries. Since the query is executed every time the

query sequence is enumerated, you can define it once and enumerate it over and over, whenever the

situation warrants. And, if the code flow takes some path that doesn’t need to actually examine the

query results by enumerating them, performance is improved because the query is never actually

executed.

Another of the benefits of deferred query execution is that, since the query isn’t actually performed

by merely defining it, we can append additional operators programmatically as needed. Imagine an

application that allows the user to query customers. Also imagine that the user can filter the queried

customers. Picture one of those filter-type interfaces that have a drop-down list for each column in

the customer table. There is a drop-down list for the City column and another for the Country column.

Each drop-down list has every city and country from all Customer records in the database. At the top

of each drop-down list is an [ALL] option, which is the default for its respective database column. If

the user hasn’t changed the setting of either of those drop-down lists, no additional where clause is

appended to the query for the respective column. Listing 14-16 contains an example programmati￾cally building a query for such an interface.

Listing 14-16. Programmatically Building a Query

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

// Turn on the logging.

db.Log = Console.Out;

// Pretend the values below are not hardcoded, but instead, obtained by accessing

// a dropdown list's selected value.

string dropdownListCityValue = "Cowes";

Rattz_789-3C14.fm Page 439 Tuesday, October 16, 2007 1:27 PM

440 CHAPTER 14 ■ LINQ TO SQL DATABASE OPERATIONS

string dropdownListCountryValue = "UK";

IQueryable<Customer> custs = (from c in db.Customers

select c);

if (!dropdownListCityValue.Equals("[ALL]"))

{

custs = from c in custs

where c.City == dropdownListCityValue

select c;

}

if (!dropdownListCountryValue.Equals("[ALL]"))

{

custs = from c in custs

where c.Country == dropdownListCountryValue

select c;

}

foreach (Customer cust in custs)

{

Console.WriteLine("{0} - {1} - {2}", cust.CompanyName, cust.City, cust.Country);

}

In Listing 14-16, I simulate obtaining the user selected city and country from their drop-down

lists, and only if they are not set to "[ALL]", I append an additional where operator to the query. Because

the query is not actually performed until the sequence is enumerated, I can programmatically build

it, a portion at a time.

Let’s take a look at the results of Listing 14-16.

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],

[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],

[t0].[Country], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK]

-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [Cowes]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

Island Trading - Cowes - UK

Notice that since I specified that the selected city was Cowes and the selected country was UK,

I got the records for the customers in Cowes in the United Kingdom. Also notice that there is a single

SQL statement that was performed. Because the query’s execution is deferred until it is actually needed,

I can continue to append to the query to further restrict it, or perhaps order it, without the expense

of multiple SQL queries taking place.

You can see that both of the filter criteria, the city and country, do appear in the where clause of

the executed SQL statement.

For another test, in Listing 14-17, I’ll change the value of the dropdownListCityValue variable to

"[ALL]" and see what the executed SQL statement looks like then and what the results are. Since the

default city of "[ALL]" is specified, the SQL query shouldn’t even restrict the results set by the city.

Rattz_789-3C14.fm Page 440 Tuesday, October 16, 2007 1:27 PM

CHAPTER 14 ■ LINQ TO SQL DATABASE OPERATIONS 441

Listing 14-17. Programmatically Building Another Query

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

// Turn on the logging.

db.Log = Console.Out;

// Pretend the values below are not hardcoded, but instead, obtained by accessing

// a dropdown list's selected value.

string dropdownListCityValue = "[ALL]";

string dropdownListCountryValue = "UK";

IQueryable<Customer> custs = (from c in db.Customers

select c);

if (!dropdownListCityValue.Equals("[ALL]"))

{

custs = from c in custs

where c.City == dropdownListCityValue

select c;

}

if (!dropdownListCountryValue.Equals("[ALL]"))

{

custs = from c in custs

where c.Country == dropdownListCountryValue

select c;

}

foreach (Customer cust in custs)

{

Console.WriteLine("{0} - {1} - {2}", cust.CompanyName, cust.City, cust.Country);

}

Let’s examine the output of Listing 14-17.

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],

[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],

[t0].[Country], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE [t0].[Country] = @p0

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

Around the Horn - London - UK

B's Beverages - London - UK

Consolidated Holdings - London - UK

Eastern Connection - London - UK

Island Trading - Cowes - UK

North/South - London - UK

Seven Seas Imports - London - UK

You can see that the where clause of the SQL statement no longer specifies the city, which is

exactly what I wanted. You can also see in the output results that there are now customers from

different cities in the United Kingdom.

Rattz_789-3C14.fm Page 441 Tuesday, October 16, 2007 1:27 PM