Examples in this section use the SQL Server 2005 Adventure Works LT sample database. To set up this database, download it from:

If you are using SQL Server Express included in Visual Studio 2008, you will need to enable the Named Pipes protocol:

To begin, open Visual Studio, and create a new application named “Simple Linq to SQL” as a console application. Once the IDE is open, click on View, and open the Server Explorer and make a connection to the AdventureWorksLT database, and test that connection.

With that in place, you are ready to create a program that uses LINQ to connect your SQL database. You’ll need to include the System.Data.Linq namespace in the references for your project as shown in Figure 15-4 so that the last two using statements will compile.

Figure 15-4. Adding a reference to System.Data.Linq

This will also create the mapping between each class property and the corresponding database column:

public class Customer
 {[Column] public string FirstName      { get; set; }
    [Column] public string LastName       { get; set; }
    [Column] public string EmailAddress   { get; set; }

Complete analysis follows Example 15-1.

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;

namespace Simple_Linq_to_SQL
{
    // customer class
    [Table(Name="SalesLT.Customer")]
    public class Customer
    {
        [Column] public string FirstName    { get; set; }
        [Column] public string LastName     { get; set; }
        [Column] public string EmailAddress { get; set; }

        // Overrides the Object.ToString(  ) to provide a
        // string representation of the object properties.
        public override string ToString(  )
        {
            return string.Format("{0} {1}
Email:   {2}",
                        FirstName, LastName, EmailAddress);
        }
    }


    public class Tester
    {
        static void Main(  )
        {
            DataContext db = new DataContext(
                @"Data Source=.SqlExpress;
                    Initial Catalog=AdventureWorksLT;
                    Integrated Security=True");

            Table<Customer> customers = db.GetTable<Customer>(  );
            var query =
                from customer in customers
                where customer.FirstName == "Donna"
                select customer;

            foreach(var c in query)
                Console.WriteLine(c.ToString(  ));

            Console.ReadKey(  );
        }
    }
}

Output:
Donna Carreras
Email:   [email protected]

The key to this program is in the first line of Main( ), where you define db to be of type DataContext. A DataContext object is the entry point for the LINQ to SQL framework, providing a bridge between the application code and database-specific commands. Its job is to translate high-level C# LINQ to SQL code to corresponding database commands and execute them behind the scenes. It maintains a connection to the underlying database, fetches data from the database when requested, tracks changes made to every entity retrieved from the database, and updates the database as needed. It maintains an “identity cache” to guarantee that if you retrieve an entity more than once, all duplicate retrievals will be represented by the same object instance (thereby preventing database corruption; for more information, see the "Database Corruption" sidebar).

Once the DataContext is instantiated, you can access the objects contained in the underlying database. This example uses the Customer table in the AdventureWorksLT database using the DataContext’s GetTable( ) function:

            Table<Customer> customers = db.GetTable<Customer>(  );

This function is a generic function so that you can specify that the table should be mapped to a collection of Customer objects.

DataContext has a great many methods and properties, one of which is a Log. This property allows you to specify the destination where it logs the SQL queries and commands executed. By redirecting it to where you can access it, you can see how LINQ does its magic. For instance, you can redirect the Log to Console.Out so that you can see the output on the system console:

Output:
SELECT [t0].[FirstName], [t0].[LastName], [t0].[EmailAddress]
FROM [SalesLT].[Customer] AS [t0]
WHERE [t0].[FirstName] = @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [Donna]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

Rather than working out the data relationships in the underlying database and mapping them in the DataContext manually, you can use the designer built into Visual Studio. This is a very powerful mechanism that makes working with LINQ painfully simple. To see how this works, first open the AdventureWorksLT database in SQL Server Management Studio Express and examine the Customer, CustomerAddress, and Address tables so that you feel comfortable you understand their relationship, as illustrated by the Entity-Relationship diagram shown in Figure 15-5.

Figure 15-5. AdventureWorksLT database diagram

Create a new Visual Studio console application called AdventureWorksDBML. Make sure the Server Explorer is visible and you have a connection to AdventureWorksLT, as shown in Figure 15-6. If the connection is not available, follow the instructions mentioned earlier to create it.

Figure 15-6. Server Explorer window

To create your LINQ to SQL classes, right-click on the project, and choose Add → New Item, as shown in Figure 15-7.

Figure 15-7. Selecting Add→New Item

When the New Item dialog opens, choose LINQ to SQL Classes. You can use the default name (probably DataClasses1), or replace it with a more meaningful name. In this case, replace it with AdventureWorksAddress, and click Add. The name you select will become the name of your DataContext object with the word DataContext appended. Therefore, the data context name in this case will be AdventureWorksAddressDataContext.

The center window shows changes to the Object Relational Designer. You can now drag tables from Server Explorer or Toolbox to the designer. Drag the Address, Customer, and CustomerAddress tables from the Server Explorer onto this space, as shown in Figure 15-8.

Figure 15-8. Dragging tables onto the work surface

In the image, two tables have been dragged on, and the third is about to be dropped. Once your tables are dropped, Visual Studio 2008 automatically retrieves and displays the relationship between the tables. You can arrange them to ensure that the relationships between the tables are displayed clearly.

Once you’ve done that, two new files have been created: AdventureWorksAddress.dbml.layout and AdventureWorksAddress.designer.cs. The former has the XML representation of the tables you’ve put on the surface, a short segment of which is shown here:

<?xml version="1.0" encoding="utf-8"?>
<ordesignerObjectsDiagram dslVersion="1.0.0.0" absoluteBounds="0, 0, 11, 8.5"
name="AdventureWorksAddress">
  <DataContextMoniker Name="/AdventureWorksAddressDataContext" />
  <nestedChildShapes>
    <classShape Id="4a893188-c5cd-44db-a114-0444cced4057" absoluteBounds="1.125,
1.375, 2, 2.5401025390625">
      <DataClassMoniker Name="/AdventureWorksAddressDataContext/Address" />
      <nestedChildShapes>
        <elementListCompartment Id="d59f1bc4-752e-41db-a940-4a9938014ca7"
absoluteBounds="1.1400000000000001, 1.835, 1.9700000000000002, 1.9801025390625"
name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
      <
estedChildShapes>
    </classShape>
    <classShape Id="c432968b-f644-4ca3-b26b-61dfe4292884" absoluteBounds="5.875, 1,
2, 3.6939111328124996">
      <DataClassMoniker Name="/AdventureWorksAddressDataContext/Customer" />
      <nestedChildShapes>
        <elementListCompartment Id="c240ad98-f162-4921-927a-c87781db6ac4"
absoluteBounds="5.8900000000000006, 1.46, 1.9700000000000002, 3.1339111328125"
name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
      <
estedChildShapes>
    </classShape>

The .cs file has the code to handle all the LINQ to SQL calls that you otherwise would have to write by hand. Like all machine-generated code, it is terribly verbose; here is a very brief excerpt:

public Address(  )
{
    OnCreated(  );
    this._CustomerAddresses = new EntitySet<CustomerAddress>(new
      Action<CustomerAddress>(this.attach_CustomerAddresses),
      new Action<CustomerAddress>(this.detach_CustomerAddresses));
}

[Column(Storage="_AddressID", AutoSync=AutoSync.OnInsert,
DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int AddressID
{
    get
    {
        return this._AddressID;
    }
    set
    {
        if ((this._AddressID != value))
        {
            this.OnAddressIDChanging(value);
            this.SendPropertyChanging(  );
            this._AddressID = value;
            this.SendPropertyChanged("AddressID");
            this.OnAddressIDChanged(  );
        }
    }
}

The classes that are generated are strongly typed, and a class is generated for each table you place in the designer.

The DataContext class exposes each table as a property, and the relationships between the tables are represented by properties of the classes representing data records. For example, the CustomerAddress table is mapped to the CustomerAddresses property, which is a strongly typed collection (LINQ table) of CustomerAddress objects. You can access the parent Customer and Address objects of a CustomerAddress object through its Customer and Address properties, respectively. This makes it quite easy to write the code to retrieve data.

public partial class Customer
{
    public override string ToString(  )
    {
        StringBuilder sb = new StringBuilder(  );
        sb.AppendFormat("{0} {1} {2}",
                FirstName, LastName, EmailAddress);
        foreach (CustomerAddress ca in CustomerAddresses)
        {
            sb.AppendFormat("
	{0}, {1}",
                ca.Address.AddressLine1,
                ca.Address.City);
        }
        sb.AppendLine(  );
        return sb.ToString(  );
    }
}

Replace the contents of Program.cs with the code shown in Example 15-2 to use the generated LINQ to SQL code to retrieve data from the three tables you’ve mapped using the designer.

using System;
using System.Linq;
using System.Text;

namespace AdventureWorksDBML
{
    // Main program
    public class Tester
    {
        static void Main(  )
        {
            AdventureWorksAddressDataContext dc = new
                AdventureWorksAddressDataContext(  );
            // Uncomment the statement below to show the
            // SQL statement generated by LINQ to SQL.
            // dc.Log = Console.Out;

            // Find one customer record.
            Customer donna = dc.Customers.Single(c => c.FirstName == "Donna");");
            Console.WriteLine(donna);

            // Find a list of customer records.
            var customerDs =
                from customer in dc.Customers
                where customer.FirstName.StartsWith("D")
                orderby customer.FirstName, customer.LastName
                select customer;

            foreach (Customer customer in customerDs)
            {
                Console.WriteLine(customer);
            }
        }
    }

    // Add a method to the generated Customer class to
    // show formatted customer properties.
    public partial class Customer
    {
        public override string ToString(  )
        {
            StringBuilder sb = new StringBuilder(  );
            sb.AppendFormat("{0} {1} {2}",
                    FirstName, LastName, EmailAddress);
            foreach (CustomerAddress ca in CustomerAddresses)
            {
                sb.AppendFormat("
	{0}, {1}",
                    ca.Address.AddressLine1,
                    ca.Address.City);
            }
            sb.AppendLine(  );
            return sb.ToString(  );
        }
    }
}


Output:
Donna Carreras [email protected]
        12345 Sterling Avenue, Irving

(only showing the first 5 customers):
Daniel Blanco [email protected]
        Suite 800 2530 Slater Street, Ottawa
Daniel Thompson [email protected]
        755 Nw Grandstand, Issaquah
Danielle Johnson [email protected]
        955 Green Valley Crescent, Ottawa
Darrell Banks [email protected]
        Norwalk Square, Norwalk
Darren Gehring [email protected]
        509 Nafta Boulevard, Laredo

AdventureWorksAddressDataContext dc = new AdventureWorksAddressDataContext(  );

dc.Customers

Customer donna = dc.Customers.Single(c => c.FirstName == "Donna");

dc.Customers

dc.Customers.Single(condition);

Customer donna = dc.Customers.Single(condition);

c => c.FirstName == "Donna"

Customer donna = dc.Customers.Single(c => c.FirstName == "Donna");

Console.WriteLine(donna);

Donna Carreras [email protected]
        12345 Sterling Avenue, Irving,

 var customerDs =
     from customer in dc.Customers
     where customer.FirstName.StartsWith("D")
     orderby customer.FirstName, customer.LastName
     select customer;

foreach (Customer customer in customerDs)
{
    Console.WriteLine(customer);
}

Delia Toone [email protected]
        755 Columbia Ctr Blvd, Kennewick

Della Demott Jr [email protected]
        25575 The Queensway, Etobicoke

Denean Ison [email protected]
        586 Fulham Road,, London

Denise Maccietto [email protected]
        Port Huron, Port Huron

Derek Graham [email protected]
        655-4th Ave S.W., Calgary

Derik Stenerson [email protected]
        Factory Merchants, Branson

Diane Glimp [email protected]
        4400 March Road, Kanata

To add or modify data to the database using LINQ, you interact with objects in C#, make your changes, and then tell the DataContext to SubmitChanges, allowing LINQ to take care of the details. This is an extremely object-oriented way to approach data storage. Your code remains strongly typed and yet decoupled from the underlying persistence mechanism.

If you want to add new data to the database, you instantiate a new object and then save it. If you want to modify data already persisted (stored) in the database, you retrieve the object, modify it, and then store it. The key to Example 15-3 is that from a C# perspective, you are interacting with objects and letting LINQ worry about the details of interacting with SQL Server.

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;

namespace Modifying_Data_Using_Linq_To_SQL
{
    // Main program
    public class Tester
    {
        static void Main(  )
        {
            AddCustomer(  );
            UpdateCustomer(  );
            Console.ReadKey(  );
        }

        private static void AddCustomer(  )
        {
            Console.WriteLine("Adding a new customer...");
            AdventureWorksDataContext dc = new AdventureWorksDataContext(  );
            // Uncomment the statement below to show the
            // SQL statement generated by LINQ to SQL.
            // dc.Log = Console.Out;

            // Add a new customer with address
            Customer douglas = new Customer(  );
            douglas.FirstName = "Douglas";
            douglas.LastName = "Adams";
            douglas.EmailAddress = "[email protected]";
            douglas.PasswordHash = "fake";
            douglas.PasswordSalt = "fake";
            douglas.ModifiedDate = DateTime.Today;
            douglas.rowguid = Guid.NewGuid(  );

            Address addr = new Address(  );
            addr.AddressLine1 = "1c Sharp Way";
            addr.City = "Seattle";
            addr.PostalCode = "98011";
            addr.StateProvince = "Washington";
            addr.CountryRegion = "United States";
            addr.ModifiedDate = DateTime.Today;
            addr.rowguid = Guid.NewGuid(  );

            CustomerAddress ca = new CustomerAddress(  );
            ca.AddressType = "Main Office";
            ca.Address = addr;
            ca.Customer = douglas;
            ca.ModifiedDate = DateTime.Today;
            ca.rowguid = Guid.NewGuid(  );

            ?dc.Customers.InsertOnSubmit(douglas);
            dc.SubmitChanges(  );

            ShowCustomersByFirstName("Douglas");
        }

        // Update a customer record
        private static void UpdateCustomer(  )
        {
            Console.WriteLine("Updating a customer...");
            AdventureWorksDataContext dc = new AdventureWorksDataContext(  );
            // Uncomment the statement below to show the
            // SQL statement generated by LINQ to SQL.
            //dc.Log = Console.Out;

            Customer dAdams = dc.Customers.Single(
                c => (c.FirstName == "Douglas" && c.LastName == "Adams"));
            Console.WriteLine("Before:
{0}", dAdams);

            dAdams.Title = "Mr.";

            // Add a new shipping address
            Address addr = new Address(  );
            addr.AddressLine1 = "1 Warehouse Place";
            addr.City = "Los Angeles";
            addr.PostalCode = "30210";
            addr.StateProvince = "California";
            addr.CountryRegion = "United States";
            addr.ModifiedDate = DateTime.Today;
            addr.rowguid = Guid.NewGuid(  );

            CustomerAddress ca = new CustomerAddress(  );
            ca.AddressType = "Shipping";
            ca.Address = addr;
            ca.Customer = dAdams;
            ca.ModifiedDate = DateTime.Today;
            ca.rowguid = Guid.NewGuid(  );

            dc.SubmitChanges(  );

            Customer dAdams1 = dc.Customers.Single(
                c => (c.FirstName == "Douglas" && c.LastName == "Adams"));
            Console.WriteLine("After:
{0}", dAdams);
        }

        // Find a list of customer records with a specific first name.
        private static void ShowCustomersByFirstName(string firstName)
        {
            AdventureWorksDataContext dc = new AdventureWorksDataContext(  );
            var customers =
                from customer in dc.Customers
                where customer.FirstName == "Douglas"
                orderby customer.FirstName, customer.LastName
                select customer;

            Console.WriteLine("Customers whose first name is {0}:", firstName);
            foreach (Customer customer in customers)
                Console.WriteLine(customer);
        }
    }

    // Add a method to the generated Customer class to
    // show formatted customer properties.
    public partial class Customer
    {
        public override string ToString(  )
        {
            StringBuilder sb = new StringBuilder(  );
            sb.AppendFormat("{0} {1} {2} {3}",
                    Title, FirstName, LastName, EmailAddress);
            foreach (CustomerAddress ca in CustomerAddresses)
            {
                sb.AppendFormat("
	{0}: {1}, {2}",
                    ca.AddressType,
                    ca.Address.AddressLine1,
                    ca.Address.City);
            }
            sb.AppendLine(  );
            return sb.ToString(  );
        }
    }
}

The test program takes two actions: AddCustomer and then UpdateCustomer, each of which is encapsulated in a method call.

Customer douglas = new Customer(  );
douglas.FirstName = "Douglas";
douglas.LastName = "Adams";
douglas.EmailAddress = "[email protected]";
douglas.PasswordHash = "fake";
douglas.PasswordSalt = "fake";
douglas.ModifiedDate = DateTime.Today;
douglas.rowguid = Guid.NewGuid(  );

Address addr = new Address(  );
addr.AddressLine1 = "1c Sharp Way";
addr.City = "Seattle";
addr.PostalCode = "98011";
addr.StateProvince = "Washington";
addr.CountryRegion = "United States";
addr.ModifiedDate = DateTime.Today;
addr.rowguid = Guid.NewGuid(  );

CustomerAddress ca = new CustomerAddress(  );
ca.AddressType = "Main Office";ca.Address = addr;
ca.Customer = douglas;
ca.ModifiedDate = DateTime.Today;
ca.rowguid = Guid.NewGuid(  );

dc.Customers.InsertOnSubmit(douglas);
dc.SubmitChanges(  );

private static void ShowCustomersByFirstName(string firstName)
{
    AdventureWorksDataContext dc = new AdventureWorksDataContext(  );
    var customers =
        from customer in dc.Customers
        where customer.FirstName == "Douglas"
        orderby customer.FirstName, customer.LastName
        select customer;

    Console.WriteLine("Customers whose first name is {0}:", firstName);
    foreach (Customer customer in customers)
        Console.WriteLine(customer);
}

Douglas Adams [email protected]
  Main Office: 1c Sharp Way, Seattle

Modifying a Customer Record

Modifying the customer involves finding the record you want to modify, retrieving it, modifying it as an object, and then storing it back in the database.

You retrieve Douglas Adams’ record much as you saw earlier:

Customer dAdams = dc.Customers.Single(
    c => (c.FirstName == "Douglas" && c.LastName == "Adams"));

With the record in hand, you add a new shipping address, which requires creating an address record and a CustomerAddress record (to tie the new Address record to the existing Customer record):

Address addr = new Address(  );
addr.AddressLine1 = "1 Warehouse Place";
addr.City = "Los Angeles";
addr.PostalCode = "30210";
addr.StateProvince = "California";
addr.CountryRegion = "United States";
addr.ModifiedDate = DateTime.Today;
addr.rowguid = Guid.NewGuid(  );

CustomerAddress ca = new CustomerAddress(  );
ca.AddressType = "Shipping";
ca.Address = addr;
ca.Customer = dAdams;
ca.ModifiedDate = DateTime.Today;
ca.rowguid = Guid.NewGuid(  );

For a bit of sport, you also change his title, with all due respect, from blank to “Mr.”:

dAdams.Title = "Mr.";

By using Console.WriteLine statements before and after the modification, you can see the changes:

Updating a customer...
Before:
 Douglas Adams [email protected]
        Main Office: 1c Sharp Way, Seattle

After:
Mr. Douglas Adams [email protected]
        Main Office: 1c Sharp Way, Seattle
        Shipping: 1 Warehouse Place, Los Angeles

You should also now see one record in the Customer table, and two each in the CustomerAddress and Address tables, as shown in Figure 15-9.

Figure 15-9. Modified data in the database

Deleting a customer is a bit trickier than adding or modifying one, because the relational database is going to enforce referential integrity. That is, to avoid data inconsistency, the relational database (e.g., SQL Server) is going to ensure that the rows in CustomerAddress be deleted before the rows in Address or Customer are deleted. Ideally, in fact, you’d like the entire deletion of all the related rows, in all the related tables, to be within a transaction so that if any of the deletions fail, the entire set is “rolled back” to its initial state. That way, you don’t end up with orphaned address records, or customers who have somehow lost data. (See the sidebar "Data Consistency" later in this chapter.)

The easiest way to do this is to ask the database to help you. After all, this is what databases do for a living. One solution they offer is stored procedures (they offer others, such as cascading deletes, etc.). To create a stored procedure, begin by right-clicking on the Stored Procedures file folder of your data connection and choose Add New Stored Procedure, as shown in Figure 15-10.

Figure 15-10. Adding a new stored procedure

Replace the code in the prototype stored procedure with that shown in Example 15-4. You don’t need to fully understand the SQL at this point; you can trust that it will properly delete customers with the given first and last names and all related address records. With that said, I’ve added some comments (after a double dash) to help you along with what it is we’re doing in the SPROC (geek speak for Stored PROCedure).

Create PROCEDURE [SalesLT].[DeleteCustomer]
    @firstName Name, -- parameters with their type
    @lastName  Name
AS
BEGIN
    SET NOCOUNT ON;     --- administrative stuff
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    declare @customerId   int;  -- local variable
    declare @addressId    int;
    declare addressCursor cursor for  -- keep track of where we're up to
            select  CustomerId, AddressId  -- find this record
              from  CustomerAddress  -- fromt this table
             where  CustomerId in  -- where this column is found in these results:
                    (
                        select  CustomerId - find this column
                          from  Customer  -- in this table
                         where  FirstName = @firstName  -- where this is true
                           and  LastName  = @lastName -- and this is also true
                    );

    begin transaction; -- start a transaction
    open addressCursor;  -- go get that cursor
      -- get the next record and put results into our variables
    fetch next from addressCursor into @customerId, @addressId;
       -- start a while loop
    while @@fetch_status = 0 begin
        -- delete the matching records
        delete CustomerAddress where customerId = @customerId
          and addressId =  @addressId
               --delete these matching records too
        delete Address where addressId = @addressId;
    loop
        fetch next from addressCursor into @customerId, @addressId;
    end;  -- end the while
    close addressCursor;  -- close the cursor
    deallocate addressCursor;  -- clean up the resource

    delete  Customer  -- delete where you have a match
     where  FirstName = @firstName
       and  LastName  = @lastName;

    commit;  -- once everything worked, commit it all
           -- (implicit - if anything fails, roll back to your starting point)
END

Open the Stored Procedures folder and locate your new stored procedure (DeleteCustomer). Next, double-click on Adventureworks.dbml in the Solution Explorer, which will reopen the designer.

Drag the new stored procedure onto the designer. It is now registered with the designer and will appear in the righthand window of the designer, as shown in Figure 15-11.

Figure 15-11. Stored procedure in the designer

You now have access to that stored procedure from within your DataContext, as shown in Example 15-5.

       private static void DeleteCustomer(  )
        {
            Console.WriteLine("Deleting a customer...");
            Console.Write("Before: ");
            ShowCustomersByFirstName("Douglas");

            AdventureWorksDataContext dc = new AdventureWorksDataContext(  );
            // Uncomment the statement below to show the
            // SQL statement generated by LINQ to SQL.
            // dc.Log = Console.Out;

            dc.DeleteCustomer("Douglas", "Adams");
            Console.Write("After: ");
            ShowCustomersByFirstName("Douglas");
        }

Output:

Deleting a customer...
Before: Customers whose first name is Douglas:
Mr. Douglas Adams [email protected]
        Main Office: 1c Sharp Way, Seattle
        Shipping: 1 Warehouse Place, Los Angeles

Mr. Douglas Baldwin [email protected]
        Main Office: Horizon Outlet Center, Holland

Mr. Douglas Groncki [email protected]
        Main Office: 70259 West Sunnyview Ave, Visalia

After: Customers whose first name is Douglas:
Mr. Douglas Baldwin [email protected]
        Main Office: Horizon Outlet Center, Holland

Mr. Douglas Groncki [email protected]
        Main Office: 70259 West Sunnyview Ave, Visalia

The code is unchanged from the previous example, except for a new method (which you must call from Main( )), named DeleteCustomer( ). This method gets the AdventureWorksDataContext, but then just calls DeleteCustomer, passing in the two parameters of the first and last names. That’s it!

Because DeleteCustomer is a stored procedure registered with the DataContext, the DataContext knows just what to do: it calls the stored procedure, and the Sproc does all the work. Wicked cool.

If you would like the output of your work to go to an XML document rather than to a SQL database, you have only to create a new XML element for each object in the Customers table, and a new XML attribute for each property representing a column in the table. To do this, you use the LINQ to XML API, as illustrated in Example 15-6.

Note carefully that this code takes advantage of the new LINQ to XML classes, such as XElement, XAttribute, and XDocument. Working with XAttributes, for example, is very similar to working with standard XML elements. However, note carefully that, for example, XAttributes are not nodes in an XML tree, but instead are name/value pairs associated with an actual XML element. This is also quite different from what you are used to in working with the DOM.

The XElement object represents an actual XML element and can be used to create elements. It interoperates cleanly with System.XML, and makes for a terrific transition class between LINQ to XML and XML itself.

Finally, the XDocument class derives from XContainer and has exactly one child node (you guessed it: an XElement). It can also have an XDeclaration, zero or more XProcessingInstructions, XComments, and one XDocumentType (for the DTD), but that is more detail than we need.

In the next example, we’re going to create some XElements and assign some XAttributes. This should be very familiar to anyone comfortable with XML and a relatively easy first glimpse for those who are totally new to raw XML (see Chapter 14).

using System;
using System.Data.Linq;
using System.Linq;
using System.Xml.Linq;

namespace LinqToXML
{
    // Main program
    public class Tester
    {
        static void Main(  )
        {
            XElement customerXml = CreateCustomerListXml(  );
            Console.WriteLine(customerXml);
        }

        /// <summary>
        /// Create an XML document containing a list of customers.
        /// </summary>
        /// <returns>XML document containing a list of customers.</returns>
        private static XElement CreateCustomerListXml(  )
        {
            AdventureWorksDataContext dc = new AdventureWorksDataContext(  );
            // Uncomment the statement below to show the
            // SQL statement generated by LINQ to SQL.
            // dc.Log = Console.Out;

            // Find a list of customer records.
            var customerDs =
                from customer in dc.Customers
                where customer.FirstName.StartsWith("D")
                orderby customer.FirstName, customer.LastName
                select customer;

            XElement customerXml = new XElement("Customers");
            foreach (Customer customer in customerDs)
            {
                customerXml.Add(new XElement("Customer",
                    new XAttribute("FirstName", customer.FirstName),
                    new XAttribute("LastName", customer.LastName),
                    new XElement("EmailAddress", customer.EmailAddress)));
            }
            return customerXml;
        }
    }
}

In this example, rather than simply writing out the values of the CustomerDS that we’ve retrieved from the database, we convert the object to an XML file by using the LINQ to XML API. It is so straightforward as to be almost startling.

Let’s take this example apart. We begin by calling CreateCustomerListXml and assigning the results to an XElement named customerXml. CreateCustomerListXml begins by creating a LINQ statement (those of us who grew up with SQL will take a few years to get used to having the select statement come at the end!):

var customerDs =
     from customer in dc.Customers
     where customer.FirstName.StartsWith("D")
     orderby customer.FirstName, customer.LastName
     select customer;

Let me remind you that even though we use the keyword var here, which in JavaScript is not type-safe, in C#, this is entirely type-safe; the compiler imputes the type based on the query.

Next, we create an XElement named customerXml:

XElement customerXml = new XElement("Customers");

Here’s another potentially confusing aspect. We’ve given the C# XElement an identifier, customerXml, so that we can refer to it in C# code, but when we instantiated the XElement, we passed a name to the constructor (Customers). It is that name (Customers) that will appear in the XML file.

Next, we iterate through the CustomerDS collection that we retrieved earlier, pulling out each Customer object in turn, and create a new XElement based on the Customer object, adding an XAttribute for the FirstName, LastName, and EmailAddress “columns”:

foreach (Customer customer in customerDs)
{
    XElement cust = new XElement("Customer",
         new XAttribute("FirstName", customer.FirstName),
         new XAttribute("LastName", customer.LastName),
         new XElement("EmailAddress", customer.EmailAddress));

As we iterate through each customer, we also iterate through each customer’s associated CustomerAddress collection (customer.Addresses). These return an object of type Customer.Address, and we add to the XElement cust the Attributes for the Address, beginning with a new XElement named Address. This gives our Customer element a subelement of Addresses, with attributes for AddressLine1, AddressLine2, City, and so on.

Thus, a single Address object in the XML will look like this:

  <Customer FirstName="Dora" LastName="Verdad">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="Suite 2502 410 Albert Street" AddressLine2=""
      City="Waterloo" StateProvince="Ontario" PostalCode="N2V" />
  </Customer>

Finally, we want each of these Customer elements (with their child Address elements) to be child elements of the Customers (plural) element that we created earlier. We accomplish this by opening the C# object and adding the new customer to the element after each iteration of the loop:

customerXml.Add(cust);

Notice that because we’re doing this in the C#, we access the Element through its C# identifier, not through its XML identifier. In the resulting XML document, the name of the outer element will be Customers and within Customers will be a series of Customer elements, each of which will contain Address elements:

<Customers>
  <Customer ...
    <Address ....    </Address>
    <EmailAddress ...  /EmailAddress/>
  </Customer>
  <Customer ...
    <Address ....    </Address>
    <EmailAddress ...  /EmailAddress/>
  </Customer>
</Customers>

Once we’ve iterated through the lot, we return the customerXml (the Customers element) which contains all the Customer elements, which in turn contain all the address elements; that is, the entire tree:

return customerXml;

Piece of pie; easy as cake.

Here is an excerpt from the complete output (slightly reformatted to fit the page):

<Customers>
  <Customer FirstName="Daniel" LastName="Blanco">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="Suite 800 2530 Slater Street"
       AddressLine2="" City="Ottawa"
    StateProvince="Ontario" PostalCode="K4B 1T7" />
  </Customer>
  <Customer FirstName="Daniel" LastName="Thompson">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="755 Nw Grandstand" AddressLine2="" City="Issaquah"
      StateProvince="Washington" PostalCode="98027" />
  </Customer>
  <Customer FirstName="Danielle" LastName="Johnson">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="955 Green Valley Crescent" AddressLine2=""
      City="Ottawa" StateProvince="Ontario" PostalCode="K4B 1S1" />
  </Customer>
  <Customer FirstName="Darrell" LastName="Banks">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="Norwalk Square" AddressLine2=""
      City="Norwalk" StateProvince="California" PostalCode="90650" />
  </Customer>
  <Customer FirstName="Darren" LastName="Gehring">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="509 Nafta Boulevard" AddressLine2=""
        City="Laredo" StateProvince="Texas" PostalCode="78040" />
  </Customer>
  <Customer FirstName="David" LastName="Givens">
    <EmailAddress>[email protected]</EmailAddress>
    <Address AddressLine1="#500-75 O'Connor Street" AddressLine2=""
         City="Ottawa" StateProvince="Ontario" PostalCode="K4B 1S2" />
  </Customer>
</Customers>