Browse DevX
Sign up for e-mail newsletters from DevX


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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 for LINQ to SQL


Namespace for LINQ to XML


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