TRY IT OUT: Generating XML from Databases

Follow these steps to create the example in Visual Studio 2010:

How It Works

In Program.cs you added the reference to the System.Xml.Linq namespace in order to call the LINQ to XML constructor classes.

As described in the first part of the chapter, you created a data source for the Northwind sample database and then used Visual Studio 2010 to create a LINQ to Entities object model for the Northwind data. In the main program, you created an instance of the Northwind data context class to use the following mapping:

NORTHWNDEntities northWindEntities = new NORTHWNDEntities();

Your LINQ to Entities query uses the Northwind data context Customers member as a data source and drills down through the Customers, Orders, and Order Details tables to produce a list of all customer orders. However, because of deferred execution for LINQ to Entities, you convert the intermediate result to an in-memory LINQ to Objects enumerable type with the AsEnumerable() method on the Customer object. Finally, the query results are projected in the select clause of the query into a nested set of LINQ to XML elements and attributes:

XElement northwindCustomerOrders =
               new XElement("customers",
                from c in northWindDataContext.Customers.AsEnumerable()
                select new XElement("customer",
                   new XAttribute("ID", c.CustomerID),
                   new XAttribute("City", c.City),
                   new XAttribute("Company", c.CompanyName),
                   from o in c.Orders
                     select new XElement("order",
                        new XAttribute("orderID", o.OrderID),
                        new XAttribute("orderDay",
                                o.OrderDate.Value.Day),
                        new XAttribute("orderMonth",
                                o.OrderDate.Value.Month),
                        new XAttribute("orderYear",
                                o.OrderDate.Value.Year),
                        new XAttribute("orderTotal",
                            o.Order_Details.Sum(od => od.Quantity * od.UnitPrice))
                      ) //end order
                 ) // end customer
            ); // end customers

					  

To grab all the orders for a customer, you use a second LINQ query (from o in c.Orders. . .) nested inside the first one (from c in northWindDataContext.Customers. . .).

You divide the OrderDate field into its month, date, and year components to make the XML easier to query; you will see how this is used in the next example.

Finally, you save the generated XML to file as in the previous example:

string xmlFileName =
                  @"C:\BegVCSharp\Chapter24\Xml\NorthwindCustomerOrders.xml";
       northwindCustomerOrders.Save(xmlFileName);

       Console.WriteLine("Successfully saved Northwind customer orders to:");
       Console.WriteLine(xmlFileName);

					  

Now you will write a query against the XML file you just wrote to disk.