dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Baker's Dozen: A 13-Step Crash Course for Using LINQ : Page 2

Start becoming familiar with the different areas of LINQ with the Microsoft Visual Studio "Orcas" Beta.


advertisement
Tip 1: Getting Started
You can download Orcas Beta 1 from MSDN.

I recommend that you install Orcas Beta 1 on a test machine (or on a Virtual PC). I recommend against installing Orcas Beta 1 on a machine where you are doing any type of production work with Visual Studio 2005 (or any prior version of .NET, or any other development tool). Some developers are running both Orcas Beta 1 as well as Visual Studio 2005 on the same machine. While it can and does work, doing so increases the odds that you may need to reapply patches or service packs, or perform other maintenance tasks later when new beta versions/service packs become available.

Tip 2: Creating a DataContext for LINQ to SQL
The first example will use LINQ to SQL so that you can build a database query from Orcas Beta 1. I'll show you how to write a very simple query against the AdventureWorks database in SQL Server 2005. In particular, the query will return a list of all Purchase Orders greater than $25,000, with a link into the Vendor table to show the vendor name.

Before you can do that, you need to address some housekeeping details. First, you need to provide the Orcas Beta 1 environment with some information so that you can write the database query in a strongly-typed fashion and take advantage of IntelliSense. In LINQ terms, you need to create a strongly-typed DataContext against the database. You have two options to do this: one involves the new utility SqlMetal that comes with the SDK for Orcas Beta 1, and the second is to use the new LINQ to SQL design tool.

SqlMetal automatically generates entity classes for all tables from a given database. You can use these entity classes in Orcas Beta 1 to create a DataContext for writing LINQ queries.

You can generate the entity classes for the AdventureWorks database by typing the following at the Command Prompt (you'll need to specify the full path for the location of SqlMetal):

   SqlMetal.exe 
   /server:localhost 
   /database:AdventureWorks
   /code:AdventureWorks.cs
 
Figure 1. Using SqlMetal: For convenience, you can call sqlmetal.exe in the .NET project pre-build event command line.
Because the database will surely change during the development project, you can call SqlMetal every time you build your application in the Pre-Build Event Command Line project option (see Figure 1).

The entity class that SqlMetal generates may be very large, and unnecessary if you simply need to write a few queries. So the second option is to create your own LINQ to SQL data context for only those tables you need, as follows:

  • Add a new item to the project.
  • Select LINQ to SQL File in the list of Installed Templates.
Orcas Beta 1 will then display the Object-Relational Design area where you can drag the necessary tables from Server Explorer for the database (see Figure 2).

 
Figure 2. Creating a LINQ to SQL DataContext: The figure shows some tables dragged to the Object-Relational Design area from Server Explorer.
The next tip covers the actual code for the query.

Tip 3: Writing LINQ to SQL Queries
Now that you've created the DataContext using SqlMetal, you can create your first test LINQ project. You'll want to add the AdventureWorks.cs entity class file (that SqlMetal generated) to your project (by selecting "Add Existing Item" in Solution Explorer). You'll also need to add a .NET reference to System.Data.Linq. Table 1 lists the core LINQ DLLs.

Table 1: Main namespaces for LINQ.

Namespace

Description

System.Data.Linq

Namespace for LINQ to SQL

System.XML.Linq

Namespace for LINQ to XML

System.Data.Entity

Namespace for LINQ to DataSets

The query below retrieves all of the purchase orders with a subtotal greater than 25,000.

   using System;
   using System.Data;
   using System.Collections.Generic;
   using System.Data.SqlClient;
   using System.Drawing;
   using System.Linq;
   using System.Text;
   SqlConnectionStringBuilder oStringBuilder = new
     SqlConnectionStringBuilder();
   oStringBuilder.UserID = "sa";
   oStringBuilder.Password = "kevin";
   oStringBuilder.InitialCatalog = "AdventureWorks";
   oStringBuilder.DataSource = "localhost";
   
   // Use the AdventureWorks data context 
   // generated from SQLMetal
   AdventureWorks db = new AdventureWorks(
      oStringBuilder.ConnectionString);
   
   // Result is an anonymous type
   var Orders = 
      from PO in db.Purchasing_PurchaseOrderHeader
      join Vendor in db.Purchasing_Vendor 
      on PO.VendorID equals Vendor.VendorID
      where PO.SubTotal > 25000
      orderby PO.SubTotal
      select 
      new { Vendor.Name, PO.PurchaseOrderID, PO.OrderDate, 
            PO.Freight, PO.SubTotal };
The query forms a SQL-like join between the Vendor and PurchaseOrderHeader tables in the AdventureWorks database using a basic WHERE clause. The query in Listing 1 performs a sum of Freight and Subtotal for each Vendor.

Note that the second query does not contain a JOIN statement. Because a database parent/child relationship exists between the Vendor and PurchaseOrderHeader tables, you can use parent-child syntax (Vendor.Purchasing_PurchaseOrderHeader) to reference child aggregations.

Of course, LINQ doesn't limit you to just retrieving data. Here are two examples that insert and update data using LINQ.

   // Example 1: Code to retrieve PO Order ID 1, 
   // and update the freight by 10%
   var PO1 = db.Purchasing_PurchaseOrderHeader.First(
      p => p.PurchaseOrderID == 1);
   PO1.Freight = PO1.Freight * 1.1M;
   bool ErrFlag = false;
   try 
   { 
      db.SubmitChanges(); 
   }
   catch (Exception)
   {
      ErrFlag = true;
   }
    
   // Example 2: Code to retrieve all orders with freight 
   // greater than $1,000, and give a 10% discount
   var POFreight = 
      from POList in db.Purchasing_PurchaseOrderHeader
      where POList.Freight > 1000
      select POList;
   
   // Iterate through the results and update each freight record
   foreach (var PORec in POFreight)  
      PORec.Freight = PORec.Freight * .90M;
   try
   {
      db.SubmitChanges();
   }
   catch (Exception)
   {
      ErrFlag = true;
   }
You can use the result object (an anonymous type, which I'll cover in more detail later) to set values, and then post back to the database context object using SubmitChanges.



Thanks for your registration, follow us on our social networks to keep up-to-date