第 90 页

that is generated by LINQ to SQL for your LINQ statement. Figure 28-21 illustrates the default

data tip for the same LINQ to SQL statement in C# (VB is the same, except you don’t see the

generated SQL in the first line of the data tip).

fiGure 28-21

After adding the visualizer you will see the magnifying glass icon in the first line of the data tip.

Clicking this opens up the LINQ to SQL Debug Visualizer so that you can see the way your LINQ

to SQL statement is translated to SQL. Figure 28-22 illustrates this visualizer showing the way

that the query is parsed by the compiler in the top half of the screen, and the SQL statement that

is generated in the lower half of the screen. Clicking the Execute button displays the QueryResult

window (inset into Figure 28-22) with the output of the SQL statement. Note that you can modify

the SQL statement, allowing you to tweak it until you get the correct results set. This can quickly

help you correct any errors in your LINQ statement.

610 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

fiGure 28-22

inserts, Updates, and Deletes

You can see from the earlier code snippet that the DataContext acts as the conduit through which

LINQ to SQL queries are processed. To get a better appreciation of what the DataContext does

behind the scenes, let’s look at inserting a new product category into the AdventureWorksLT

database. Before you can do this you will need to add the ProductCategory table to your LINQ to

SQL design surface. In this case you don’t need to modify any of the properties, so just drag the

ProductCategory table onto the design surface. Then to add a new category to your database, all

you need is the following code:

c#

using(var aw = new AdventureLiteDataContext()){

var cat = new ProductCategory();

cat.Name = "Extreme Bike";

aw.ProductCategories.InsertOnSubmit(cat);

aw.SubmitChanges();

}

Code snippet MainForm.cs

Vb

Using aw As New AdventureLiteDataContext

Dim cat As New ProductCategory

cat.Name = "Extreme Bike"

aw.ProductCategories.InsertOnSubmit(cat)

aw.SubmitChanges()

End Using

Code snippet MainForm.vb

linQ to sQl .

611

This code inserts the new category into the collection of product categories held in memory by

the DataContext. When you then call SubmitChanges on the DataContext it is aware that you

have added a new product category so it will insert the appropriate records. A similar process is

used when making changes to existing items. In the following example you retrieve the product

category you just inserted using the Contains syntax. Because there is likely to be only one

match, you can use the FirstOrDefault extension method to give you just a single product

category to work with:

c#

using (var aw = new AdventureLiteDataContext()){

var cat = (from pc in aw.ProductCategories

where pc.Name.Contains("Extreme")

select pc).FirstOrDefault();

cat.Name = "Extreme Offroad Bike";

aw.SubmitChanges();

}

Code snippet MainForm.cs

Vb

Using aw As New AdventureLiteDataContext

Dim cat = (From pc In aw.ProductCategories

Where pc.Name.Contains("Extreme")).FirstOrDefault

cat.Name = "Extreme Offroad Bike"

aw.SubmitChanges()

End Using

Code snippet MainForm.cs

Once the change to the category name has been made, you just need to call SubmitChanges on the

DataContext in order for it to issue the update on the database. Without going into too much detail

the DataContext essentially tracks changes to each property on a LINQ to SQL object so that it

knows which objects need updating when SubmitChanges is called. If you want to delete an object,

you simply need to obtain an instance of the LINQ to SQL object, in the same way as for doing

an update, and then call DeleteOnSubmit on the appropriate collection. For example, to delete a

product category you would call aw.ProductCategories.DeleteOnSubmit(categoryToDelete),

followed by aw.SubmitChanges.

stored Procedures

One of the questions frequently asked about LINQ to SQL is whether you can use your own stored

procedures in place of the run time-generated SQL. The good news is that for inserts, updates,

and deletes you can easily specify the stored procedure that should be used. You can also use

existing stored procedures for creating instances of LINQ to SQL objects. Let’s start by adding

a simple stored procedure to the AdventureWorksLT database. To do this, right-click the Stored

Procedures node under the database connection in the Server Explorer tool window and select Add

612 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

New Stored Procedure. This opens a code window with a new stored procedure template. In the

following code you have selected to return the five fields that are relevant to your Customer object:

CREATE PROCEDURE dbo.GetCustomers

AS

BEGIN

SET NOCOUNT ON

SELECT c.CustomerID, c.FirstName, c.LastName, c.EmailAddress, c.Phone

FROM SalesLT.Customer AS c

END;

Once you have saved this stored procedure it will appear under the Stored Procedures node. If you

now open up the AdventureLite LINQ to SQL designer, you can drag this stored procedure across

into the right-hand pane of the design surface. In Figure 28-23 you can see that the return type of

the GetCustomers method is set to Auto-generated Type. This means that you will only be able

to query information in the returned object. Ideally, you would want to be able to make changes to

these objects and be able to use the DataContext to persist those changes back to the database.

fiGure 28-23

The second method, GetTypedCustomers, actually has the Return Type set as the Customer class.

To create this method you can either drag the GetCustomers stored procedure to the right pane,

and then set the Return Type to Customer, or you can drag the stored procedure onto the Customer

class in the left pane of the design surface. The latter will still create the method in the right pane,

but it will automatically specify the return type as the Customer type.

Note that you don’t need to align properties with the stored procedure columns,

because this mapping is automatically handled by the DataContext. This is a

double-edged sword: clearly it works when the column names map to the source

columns of the LINQ to SQL class but it may cause a run time exception if

there are missing columns or columns that don’t match.

Once you have defined these stored procedures as methods on the design surface, calling them is as

easy as calling the appropriate method on the DataContext:

linQ to sQl .

613

c#

using (var aw = new AdventureLiteDataContext()){

var customers = aw.GetCustomers();

foreach (var c in customers){

MessageBox.Show(c.FirstName);

}

}

Code snippet MainForm.cs

Vb

Using aw As New AdventureLiteDataContext

Dim customers = aw.GetCustomers

For Each c In customers

MsgBox(c.FirstName)

Next

End Using

Code snippet MainForm.vb

Here you have seen how you can use a stored procedure to create instances of the LINQ to SQL

classes. If you instead want to update, insert, or delete objects using stored procedures, you follow

a similar process except you need to define the appropriate behavior on the LINQ to SQL class. To

begin with, create an insert stored procedure for a new product category:

CREATE PROCEDURE dbo.InsertProductCategory

(

@categoryName nvarchar(50),

@categoryId int OUTPUT

)

AS

BEGIN

INSERT INTO SalesLT.ProductCategory (Name) VALUES (@categoryName)

SELECT @categoryId=@@identity

END;

Following the same process as before, you

need to drag this newly created stored

procedure from the Server Explorer across

into the right pane of the LINQ to SQL

design surface. Then in the Properties tool

window for the ProductCategory class,

modify the Insert property. This will open

the dialog shown in Figure 28-24. Here you

can select whether you want to use the

run time-generated code or customize

the method that is used. In Figure 28-24 the

InsertProductCategory method has been

fiGure 28-24

614 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

selected. Initially, the Class Properties will be unspecified, because Visual Studio 2010 wasn’t able

to guess at which properties mapped to the method arguments. It’s easy enough to align these to

the id and name properties. Now when the DataContext goes to insert a ProductCategory it will

use the stored procedure instead of the run time-generated SQL statement.

binding linq to sql objects

The important thing to remember when using DataBinding with LINQ to SQL objects is that they

are in fact normal .NET objects. This means that you can create a new object data source via the

Data Sources tool window. In the case of the examples you have seen so far, you would go through

the Add New Data Source Wizard, selecting just the Customer object. Because the Order and

OrderItem objects are accessible via the navigation properties Orders and then OrderItems, you

don’t need to explicitly add them to the Data Source window.

Once you have created the object data source (see the left side of Figure 28-25), you can then

proceed to drag the nodes onto your form to create the appropriate data components. Starting with

the Customer node, use the drop-down to specify that you want a DataGridView, then drag it onto

your form. Next, you need to specify that you want the Orders (a child node under Customer) to

appear as details and then drag this to the form as well. You will notice that you don’t get a binding

navigator for this binding source, so from the Toolbox add a BindingNavigator to your form and

set its BindingSource property to be the OrdersBindingSource that was created when you dragged

over the Orders node. Lastly, you want to display all the OrderItems in a DataGridView, so use the

drop-down to set this and then drag the node onto the form. After doing all this you should end up

with something similar to Figure 28-25. Note that we have also included a button that you will use

to load the data and we have laid the Order information out in a panel to improve the layout.

fiGure 28-25

linQ to sQl .

615

One of the things you will have noticed is that the columns on your OrderItems data grid don’t

match those in Figure 28-25. By default, you will get Quantity, Order, and Product columns. Clearly,

the last two columns are not going to display anything of interest, but you don’t really have an

easy way to display the Name of the product in the order with the current LINQ to SQL objects.

Luckily, there is an easy way to effectively hide the navigation from OrderItem to Product so that

the name of the product will appear as a property of OrderItem.

You do this by adding your own property to the OrderItem class. Each LINQ to SQL class is

generated as a partial class, which means that extending the class is as easy as right-clicking the

class in the LINQ to SQL designer and selecting View Code. This generates a custom code file,

in this case AdventureLite.vb (or AdventureLite.cs), and includes the partial class definition.

You can then proceed to add your own code. In the following snippet we have added the Product

property that will simplify access to the name of the product being ordered:

c#

partial class OrderItem{

public string ProductName{

get{

return this.Product.Name;

}

}

}

Code snippet AdventureLite.cs

Vb

Partial Class OrderItem

Public ReadOnly Property ProductName() As String

Get

Return Me.Product.Name

End Get

End Property

End Class

Code snippet AdventureLite.vb

For some reason this property, perhaps because it is added to a second code file, will not be detected

by the Data Sources tool window. However, you can still bind the Product column to this property

by manually setting the DataPropertyName field in the Edit Columns dialog for the data grid.

The last thing to do is to actually load the data when the user clicks the button. To do this you can

use the following code:

c#

private void btnLoadData_Click(object sender, EventArgs e){

using (var aw = new AdventureLiteDataContext()){

616 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

var cust = aw.Customers;

this.customerBindingSource.DataSource = cust;

}

}

Vb

Private Sub btnLoad_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles btnLoad.Click

Using aw As New AdventureLiteDataContext

Dim custs = From c In aw.Customers

Me.CustomerBindingSource.DataSource = custs

End Using

End Sub

You will notice that your application will now run, and when the user clicks the button the customer

information will be populated in the top data grid. However, no matter which customer you select,

no information will appear in the Order information area. The reason for this is that LINQ to

SQL uses lazy loading to retrieve information as it is required. Using the data visualizer you were

introduced to earlier, if you inspect the query in this code you will see that it contains only the

customer information:

SELECT [t0].[CustomerID], [t0].[FirstName], [t0].[LastName], [t0].[EmailAddress],

[t0].[Phone]

FROM [SalesLT].[Customer] AS [t0]

You have two ways to resolve this issue. The first is to force LINQ to SQL to bring back all the

Order, OrderItem, and Product data as part of the initial query. To do this, modify the button click

code to the following:

c#

private void btnLoadData_Click(object sender, EventArgs e){

using (var aw = new AdventureLiteDataContext()){

var loadOptions =new System.Data.Linq.DataLoadOptions();

loadOptions.LoadWith<Customer>(c=>c.Orders);

loadOptions.LoadWith<Order>(o=>o.OrderItems);

loadOptions.LoadWith<OrderItem>(o=>o.Product);

aw.LoadOptions = loadOptions;

var cust = aw.Customers;