第 89 页

Code snippet MainForm.cs

Vb

Dim results = From cust In customerXml. < Customer >

Let nameBits = cust.@Name.Split(" "c)

Select New Customer() With {.FirstName = nameBits(0),

.LastName = nameBits(1)}

Code snippet MainForm.vb

As you can see, the VB XML language

support extends to enabling you to

query elements using . < elementName >

and attributes using .@attributeName.

fiGure 28-14

Figure 28-14 shows the IntelliSense for

the customerXml variable, which shows

three XML query options.

You have seen the second and third of these options in action in the previous query to extract

attribute and element information, respectively. The third option enables you to retrieve all sub-

elements that match the supplied element. For example, the following code retrieves all orders in the

XML document, irrespective of which customer element they belong to:

Dim allOrders = From cust In customerXml. < Order >

Select New Order With {.Product = cust.@Product,

.Quantity = CInt(cust.@Quantity)}

scheMa suPPort

Although VB enables you to query XML using elements and attributes, it doesn’t actually provide

any validation that you have entered the correct element and attribute names. To reduce the chance

of entering the wrong names, you can import an XML schema, which will extend the default

IntelliSense support to include the element and attribute names. You import an XML schema as you

would any other .NET namespace. First you need to add a reference to the XML schema to your

project, and then you need to add an Imports statement to the top of your code file.

Unlike other import statements, an XML schema import can’t be added in the

Project Properties Designer, which means you need to add it to the top of any

code file in which you want IntelliSense support.

604 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

If you are working with an existing XML file but don’t have a schema handy, manually creating an

XML schema just so you can have better IntelliSense support seems like overkill. Luckily, the VB

team has included the XML to Schema Inference Wizard in Visual Studio 2010. Once installed, this

wizard enables you to create a new XML schema based on an XML snippet or XML source file,

or from a URL that contains the XML source. In this example, you’re going to start with an XML

snippet that looks like the following:

<c:Customers xmlns:c="http://www.professionalvisualstudio.com/chapter28/customers">

<c:Customer Name="Bob Jones">

<c:Order Product="Milk" Quantity="2" />

<c:Order Product="Cereal" Quantity="10" />

</c:Customer>

<c:Customer Name="Alastair Kelly">

<c:Order Product="Milk" Quantity="9" />

<c:Order Product="Bread" Quantity="7" />

</c:Customer>

</c:Customers>

Code snippet customers.xml

Note that unlike the previous XML snippets, this one includes a namespace — this is necessary,

because the XML schema import is based on importing a namespace (rather than importing a

specific XSD file). To generate an XML schema based on this snippet, start by right-clicking your

project in the Solution Explorer and selecting Add New Item. With the XML to Schema Inference

Wizard installed, there should be an additional XML To Schema item template, as shown in

Figure 28-15.

fiGure 28-15

linQ to sQl .

605

Selecting this item and clicking OK prompts you to select the location of the XML from which

the schema should be generated. Select the Type or Paste XML button and paste the customers

XML snippet from earlier into the text area provided. Once you click OK, this generates the

CustomersSchema.xsd file containing a schema based on the XML resources you have specified.

The next step is to import this schema

into your code file by adding an Imports

statement to the XML namespace, as shown

in Figure 28-16. fiGure 28-16

Figure 28-16 also contains an alias, c, for the XML namespace, which will be used throughout the

code for referencing elements and attributes from this namespace. In your XLINQs you will now

see that when you press < or @, the IntelliSense list will contain the relevant elements and attributes

from the imported XML schema. In Figure 28-17, you can see these new additions when you

begin to query the customerXml variable. If you were in a nested XLINQ statement (for example,

querying orders for a particular customer), you would see only a subset of the schema elements (that

is, just the c:Order

element).

fiGure 28-17

It is important to note that importing an XML schema doesn’t validate the

elements or attributes you use. All it does is improve the level of IntelliSense

available to you when you are building your XLINQ.

linq to sql

You may be thinking that we are about to introduce you to yet another technology for doing data

access. In fact, what you will see is that everything covered in this chapter extends the existing

ADO.NET data access model. LINQ to SQL is much more than just the ability to write LINQ

statements to query information from a database. It provides an object to a relational mapping layer,

capable of tracking changes to existing objects and allowing you to add or remove objects as if they

were rows in a database.

Let’s get started and look at some of the features of LINQ to SQL and the associated designers on

the way. For this section you’re going to use the AdventureWorksLT sample database (downloadable

from http://professionalvisualstudio.com/link/1029A). You’re going to end up performing

a similar query to what you’ve seen earlier in the chapter, which was researching customers with a

first name greater than or equal to five characters and the average order size for a particular

product. Earlier, the product was Milk, but because you are dealing with a bike company you will

use the “HL Touring Seat/Saddle” product instead.

606 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

creating the object Model

For the purpose of this chapter you will be using a normal Visual Basic Windows Forms application

from the New Project dialog. You will also need to create a Data Connection to the AdventureWorksLT

database (covered in Chapter 27). The next step is to add new LINQ to SQL Classes item, named

AdventureLite.dbml, from the Add New Item dialog. This will create three files which will be added

to your project. These are AdventureLite.dbml, which is the mapping file; AdventureLite.dbml

.layout, which like the class designer is used to lay out the mapping information to make it easier to

work with; and finally, AdventureLite.designer.vb, which contains the classes into which data is

loaded as part of LINQ to SQL.

These items may appear as a single item, AdventureLite.dbml, if you don’t

have the Show All Files option enabled. Select the project and click the

appropriate button at the top of the Solution Explorer tool window.

Unfortunately, unlike some of the other visual designers in Visual Studio 2010 that have a helpful

wizard to get you started, the LINQ to SQL designer initially appears as a blank design surface, as

you can see in the center of Figure 28-18.

fiGure 28-18

On the right side of Figure 28-18, you can see the properties associated with the main design area,

which actually represents a DataContext. If you were to compare LINQ with ADO.NET, a LINQ

statement equates approximately to a command, whereas a DataContext roughly equates to the

connection. It is only roughly because the DataContext actually wraps a database connection in

order to provide object life cycle services. For example, when you execute a LINQ to SQL statement

it is the DataContext that ends up executing the request to the database, creating the objects based

on the return data and then tracking those objects as they are changed or deleted.

If you have worked with the class designer you will be at home with the LINQ to SQL designer. As

the instructions in the center of Figure 28-18 indicate, you can start to build your data mappings by

linQ to sQl .

607

dragging items from the Server Explorer (or manually creating them by dragging the item from the

Toolbox). In your case you want to expand the Tables node, select the Customer, SalesOrderHeader,

SalesOrderDetail, and Product tables, and drag them onto the design surface. You will notice from

Figure 28-19 that a number of the classes and properties have been renamed to make the object

model easier to read when you are writing

LINQ statements. This is a good example

of the benefits of separating the object

model (for example, Order or OrderItem)

from the underlying data (in this case, the

SalesOrderHeader and SalesOrderDetail

tables). Because you don’t need all the

properties that are automatically created,

it is recommended that you select them in

the designer and delete them. The end result

should look like Figure 28-19.

It is also worth noting that you can modify the details

of the association between objects. Figure 28-20

shows the Properties tool window for the association

between Product and OrderItem. Here we have set the

generation of the Child Property to False because

we won’t need to track back from a Product to all

the OrderItems. We have also renamed the Parent

Property to Product to make the association more

intuitive (although note that the name in the drop-down

at the top of the Properties window uses the original

SQL Server table names).

As you can see, you can control whether properties are created that can be used to navigate

between instances of the classes. Though this might seem quite trivial, if you think about

what happens if you attempt to navigate from an Order to its associated OrderItems, you can

quickly see that there will be issues if the full object hierarchy hasn’t been loaded into memory.

For example, in this case if the OrderItems aren’t already loaded into memory, LINQ to SQL

intercepts the navigation, goes to the database, and retrieves the appropriate data in order to

populate the OrderItems.

The other property of interest in Figure 28-20 is the Participating Properties. Editing this

property launches an Association Editor window where you can customize the relationship between

two LINQ to SQL classes. You can also reach this dialog by right-clicking the association on

the design surface and selecting Edit Association. If you drag items from Server Explorer onto the

design surface, you are unlikely to need the Association Editor. However, it is particularly useful

if you are manually creating a LINQ to SQL mapping, because you can control how the object

associations align to the underlying data relationships.

fiGure 28-19

fiGure 28-20

608 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

querying with linq to sql

In the previous chapters you have seen enough LINQ statements to understand how to put together

a statement that filters, sorts, aggregates, and projects the relevant data. With this in mind, examine

the following LINQ to SQL snippet:

c#

public void SampleLinqToSql(){

using (var aw = new AdventureLiteDataContext()){

var custs = from c in aw.Customers

from o in c.Orders

from oi in o.OrderItems

where c.FirstName.Length>=5 &&

oi.Product.Name == "HL Touring Seat/Saddle"

group oi by c into avg

let name = avg.Key.FirstName + " " + avg.Key.LastName

orderby name

select new { Name = name,

AverageOrder = avg.Average(oi => oi.Quantity) };

foreach (var c in custs){

MessageBox.Show(c.Name + " = " + c.AverageOrder);

}

}

}

Code snippet MainForm.cs

Vb

Using aw As New AdventureLiteDataContext

Dim custs = From c In aw.Customers, o In c.Orders, oi In o.OrderItems

Where c.FirstName.Length >= 5 And

oi.Product.Name = "HL Touring Seat/Saddle"

Group By c Into avg = Average(oi.Quantity)

Let Name = c.FirstName & " " & c.LastName

Order By Name

Select New With {Name, .AverageOrder = avg}

For Each c In custs

MessageBox.Show(c.Name & " = " & c.AverageOrder)

Next

End Using

Code snippet MainForm.vb

The biggest difference here is that instead of the Customer and Order objects existing in memory

before the creation and execution of the LINQ statement, now all the data objects are loaded at

the point of execution of the LINQ statement. The AdventureLiteDataContext is the conduit for

linQ to sQl .

609

opening the connection to the database, forming and executing the relevant SQL statement against

the database, and loading the return data into appropriate objects.

You will also note that the LINQ statement has to navigate through the Customers, Orders,

OrderItems, and Product tables in order to execute the LINQ statement. Clearly, if this were to be

done as a series of SQL statements, it would be horrendously slow. Luckily, the translation of the

LINQ statement to SQL commands is done as a single unit.

There are some exceptions to this; for example, if you call ToList in the middle

of your LINQ statement this may result in the separation into multiple SQL

statements. Though LINQ to SQL does abstract you away from having to

explicitly write SQL commands, you still need to be aware of the way your

query will be translated and how it might affect your application performance.

To view the actual SQL that is generated, you can use the QueryVisualizer sample that ships with

Visual Studio 2010 (located in the LinqSamples folder of the CSharpSamples.zip file found at

C:\Program Files\Microsoft Visual Studio 10.0\Samples\1033). Open and build this

sample and drop the generated file, LinqToSqlQueryVisualizer.dll, into your visualizers folder

(typically c:\Users\ < username > \Documents\Visual Studio 2010\Visualizers). When you

restart Visual Studio 2010 you will be able to make use of this visualizer to view the actual SQL