LINQ - Tutorial - LINQ to XML

Create a new Console Application project.

Add to the project LINQ DataContext for the Northwind database. It can be done by following steps of the 'LINQ to SQL - Generating DataContext' tutorial. It is also possible to copy files from the solution created for the 'LINQ to SQL - Generating DataContext' (northwind_data_context.src.zip):

copy Settings.setting and Settings.Designer.cs files into the Properties folder
copy app.config, Northwind.dbml, Northwind.dbml.layout, Northwind.Designer.cs into the main folder of the project
modify the namespace in the Northwind.Designer.cs to the same as used in the project

Add to the Program class a static method creating an XML file with data loaded from the Northwind database. Assume that only the following fields should be exported to the file:

Customer: CustomerID, CompanyName, ContactName
Orders: OrderID, OrderDate

static string fileName = "northwind.xml";

static void CreateXmlFile()
{
    NorthwindDataContext northwind = new NorthwindDataContext();
    northwind.Log = Console.Out;

    XDocument xml = new XDocument(
        new XComment("Data from the Northwind database"),
        new XElement("Customers",
            from customer in northwind.Customers
            orderby customer.CompanyName
            select
                new XElement("Customer",
                    new XElement("CustomerID", customer.CustomerID),
                    new XElement("CompanyName", customer.CompanyName),
                    new XElement("ContactName", customer.ContactName),
                    new XElement("Orders",
                        from order in customer.Orders
                        orderby order.OrderDate descending
                        select
                            new XElement("Order",
                                new XElement("OrderID", order.OrderID),
                                new XElement("OrderDate", order.OrderDate)
                            )
                    )

                )
        )
    );

    StreamWriter writer = File.CreateText(fileName);
    writer.Write(xml.ToString());
    writer.Close();
}

Call this method from the Main method.
Compile and run the application. Check the output XML file ('northwind.xml' located in the folder of the executable file).

According to the 'LINQ to SQL - Generating DataContext' tutorial, data is loaded from the Northwind database installed in SQL Server database. In case of problems with connecting to the database, check the connection string.
The SQL expression used in this case has 2 tasks: retrieve relational data (using LINQ operators) and create XML data (using XElement class).
The Log property of the NorthwindDataContext object presents some additional information about executed tasks, in this case - the SQL query used to retrieve data from the database:

SELECT [t0].[CustomerID] AS [content], [t0].[CompanyName] AS [content2], [t0].[ContactName] AS [content3], [t1].[OrderID] AS [content4], [t1].[OrderDate] AS [content5], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [value]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
ORDER BY [t0].[CompanyName], [t0].[CustomerID], [t1].[OrderDate] DESC, [t1].[OrderID]

As a second task, write code loading data from the XML file (use the 'northwind.xml' file - just generated). Display on the console a beginning of the loaded data:

static XDocument ReadXmlFile()
{
    XDocument xml = XDocument.Load(fileName);
    string beginning = xml.ToString().Substring(0, 1024);
    Console.WriteLine(beginning);
    return xml;
}

static void Main(string[] args)
{
CreateXmlFile();
XDocument xml = ReadXmlFile();
}

<Customers>
  <Customer>
    <CustomerID>ALFKI</CustomerID>
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <ContactName>Maria Anders</ContactName>
    <Orders>
      <Order>
        <OrderID>11011</OrderID>
        <OrderDate>1998-04-09T00:00:00</OrderDate>
      </Order>
      <Order>
        <OrderID>10952</OrderID>
        <OrderDate>1998-03-16T00:00:00</OrderDate>
      </Order>
      <Order>
        <OrderID>10835</OrderID>
        <OrderDate>1998-01-15T00:00:00</OrderDate>
      </Order>
      <Order>
        <OrderID>10702</OrderID>
        <OrderDate>1997-10-13T00:00:00</OrderDate>
      </Order>
      <Order>
        <OrderID>10692</OrderID>
        <OrderDate>1997-10-03T00:00:00</OrderDate>
      </Order>
      <Order>
        <OrderID>10643</OrderID>
        <OrderDate>1997-08-25T00:00:00</OrderDate>
      </Order>
    </Orders>
  </Customer>
  <Customer>
    <CustomerID>ANATR</CustomerID>
    <CompanyName>Ana Trujil

Find a customer with the biggest number of orders:

static void FindBestCustomer(XDocument xml)
{
    var bestCustomerElement =
        (from customer in xml.Element("Customers").Elements("Customer")
         let ordersCount = customer.Element("Orders").Elements("Order").Count()
         orderby ordersCount descending
         select new { CompanyName = customer.Element("CompanyName").Value,
             OrdersCount = ordersCount })
        .First();
    Console.WriteLine("Best customer: {0}, {1} orders",
        bestCustomerElement.CompanyName, bestCustomerElement.OrdersCount);
}

static void Main(string[] args)
{
    CreateXmlFile();
    XDocument xml = ReadXmlFile();
    FindBestCustomer(xml);
}

Best customer: Save-a-lot Markets, 31 orders

It is time to modify the XML data:

Add a new customer ('WUT')
Remove the 'Save-a-log Markerts' customer

static void ModifyXml(XDocument xml)
{
    xml.Element("Customers").Add(
        new XElement("Customer",
            new XElement("CustomerID", "WUT"),
            new XElement("CompanyName", "Warsaw University of Technology"),
            new XElement("ContactName", "Krzysztof Mossakowski"),
            new XElement("Orders")
        )
    );
    Console.WriteLine("WUT customer added");

    XElement toDeleteElement =
        xml.Element("Customers").Elements("Customer").Where(
            customer => customer.Element("CompanyName").Value == "Save-a-lot Markets")
        .FirstOrDefault();
    if (toDeleteElement == null)
    {
        Console.WriteLine("Save-a-lot Markets not found");
    }
    else
    {
        toDeleteElement.Remove();
        Console.WriteLine("Save-a-lot Markets removed");
    }
}

static void Main(string[] args)
{
    CreateXmlFile();
    XDocument xml = ReadXmlFile();
    FindBestCustomer(xml);
    ModifyXml(xml);
}

To verify if the customer was removed, repeat searching for a customer with the biggest number:

static void Main(string[] args)
{
    CreateXmlFile();
    XDocument xml = ReadXmlFile();
    FindBestCustomer(xml);
    ModifyXml(xml);
    FindBestCustomer(xml);
}

Best customer: Save-a-lot Markets, 31 orders
WUT customer added
Save-a-lot Markets removed
Best customer: Ernst Handel, 30 orders

As the last step, save the modified data into the source XML file:

static void SaveXml(XDocument xml)
{
    xml.Save(fileName);
}

static void Main(string[] args)
{
    CreateXmlFile();
    XDocument xml = ReadXmlFile();
    FindBestCustomer(xml);
    ModifyXml(xml);
    FindBestCustomer(xml);
    SaveXml(xml);
}