Chapter 11. Common LINQ to XML scenarios
- Building objects from XML
- Creating XML from objects
- Creating XML from data in a database
- Creating XML from a CSV file
Now that we’ve learned about the LINQ to XML API and seen how to query and transform XML using LINQ to XML, it’s time to explore some common scenarios that you may come across when working with LINQ to XML. These include building objects from XML, creating XML from a set of objects, creating XML with data from a database, filtering and mixing data from a database with XML data, updating a database with data read from an XML file, and transforming text files into XML using LINQ to XML. As we explore these common scenarios, you’ll witness the powerful capabilities offered by LINQ to XML.
In addition to using LINQ to XML, we’ll also make extensive use of the capabilities offered by LINQ to Objects and LINQ to SQL in this chapter. At times, you might forget that we’re even talking about LINQ to XML because of the strong focus on LINQ to Objects and LINQ to SQL. Our ability to intertwine discussions of LINQ to XML along with LINQ to Objects and LINQ to SQL shows how well the LINQ technologies work together and how important it is to understand each one. In the end, our goal is to help you understand LINQ to XML and how it can be used to solve the common scenarios we’ve outlined, so we’ll try to stay on course.
Within each section of this chapter, we’ll start by defining the goal that we’re looking to achieve. Once our goal is defined, we’ll look at the techniques that we’ll use to accomplish our goal. Finally, we’ll look at the implementation and examine the code necessary for meeting our goal.
As you’ll see throughout this chapter, LINQ to XML provides an intuitive XML programming API that, combined with the LINQ framework, provides a lot of powerful capabilities for building applications.
Let’s get started by looking at how we can build objects from an XML file.
11.1. Building objects from XML
As we already mentioned, the first scenario that we’re going to cover is building objects from XML. Before we look at the specifics of how we go about building objects from XML with LINQ to XML, let’s talk briefly about why we would want to do it in the first place. When we build applications today, we do so using objects. Objects allow us to encapsulate the logic and data that exist in our software. Since XML is a data format and not a programming language, we need to read the data out of the XML files if we want to use it in our applications. To ensure our application doesn’t get too tightly coupled to the format of an XML file, we’ll convert the data within the XML file into a set of objects using the powerful capabilities offered by LINQ to XML.
As we’ll see, LINQ to XML allows us to convert XML into objects using the same transformation techniques we learned about in the previous chapter. No matter what format we need to transform our XML into, LINQ to XML is up to the challenge. With that said, let’s get started by examining our goal for this scenario.
11.1.1. Goal
Our goal in the section is to create a collection of objects that contain the data within the XML document shown in listing 11.1 using the capabilities offered by LINQ to XML.
Listing 11.1. Book data in XML format
<?xml version="1.0" encoding="utf-8" ?>
<books>
<book>
<title>LINQ in Action</title>
<authors>
<author>
<firstName>Fabrice</firstName>
<lastName>Marguerie</lastName>
<website>http://linqinaction.net/</website>
</author>
<author>
<firstName>Steve</firstName>
<lastName>Eichert</lastName>
<webSite>http://iqueryable.com</webSite>
</author>
<author>
<firstName>Jim</firstName>
<lastName>Wooley</lastName>
<webSite> http://devauthority.com/blogs/jwooley/</webSite>
</author>
</authors>
<subject>
<name>LINQ</name>
<description>LINQ shall rule the world</description>
</subject>
<publisher>Manning</publisher>
<publicationDate>January 15, 2008</publicationDate>
<price>44.99</price>
<isbn>1933988169</isbn>
<notes>Great book!</notes>
<summary>LINQ in Action is great!</summary>
<reviews>
<review>
<user>Steve Eichert</user>
<rating>5</rating>
<comments>What can I say, I'm biased!</comments>
</review>
</reviews>
</book>
<book>
<title>Patterns of Enterprise Application Architecture</title>
...
</book>
</books>
As we can see, the XML contains a set of information about books. If we move our attention to the classes shown in figure 11.1, we can see that we have a series of objects that correlate to the data contained in the XML file in listing 11.1.
Figure 11.1. Class diagram
Our goal is to take the data contained in the XML file and create a list of Book objects. In addition to populating the Book object, we’ll also populate the Subject, Publisher, Authors, and Reviews properties of the Book with the corresponding information in the XML file.
With our goal of creating book objects from the XML out of the way, let’s a look at the technique we’ll use to accomplish our goal.
11.1.2. Implementation
As we saw in the previous chapter, LINQ to XML provides powerful transformation capabilities. In addition to being able to transform XML to alternate XML formats, LINQ to XML also allows us to transform XML into other data structures, such as in-memory objects.
To build our objects from XML, we’ll write several queries that leverage the axis methods provided by LINQ to XML. The queries will retrieve the baseline book information out of the XML document, as well as the publishers, authors, and reviews. We’ll build our objects using the new object initializer syntax available in C# 3.0. We’ll also explore how we can nest query expressions to read data that is in repeating elements, such as the authors and reviews. Before diving into the details of how we can construct objects from XML, let’s figure out how to load our XML document into an XElement.
To load the XML document, we can use the static Load method on the XElement class.
XElement booksXml = XElement.Load("books.xml");
Once the XML is loaded, we need to figure out how we can get the data within the XML into our Book objects. Since the book details are contained within the <book> element, our first step is to retrieve all the <book> elements within the XML document. As we learned in the previous chapter, the Elements query axis method can be used to select all elements with a particular name, so we’ll select every book element with the following code:
IEnumerable<XElement> bookElements = booksXml.Elements("book");
Our call to the Elements query axis method will return an IEnumerable<XElement> that represents the books within our XML document. In order to build our Book object from the XElement objects, we’ll need to read data out of the relevant child elements. The easiest way to construct our book object with the data in our XML is to alter our code to use the object initializer syntax available in C# 3.0. Rather than simply selecting the XElement objects as we did earlier, we’ll update our query to create new book instances by adding a select clause to our query.
We’ll assign the values contained in the child elements of the <book> element to the corresponding property on the Book object, as shown in listing 11.2.
Listing 11.2. Create Book objects from the XML using object initializers
var books =
from bookElement in booksXml.Elements("book")
select new Book {
Title = (string)bookElement.Element("title"),
PublicationDate = (DateTime)bookElement.Element("publicationDate"),
Price = (decimal)bookElement.Element("price"),
Isbn = (string)bookElement.Element("isbn"),
Notes = (string)bookElement.Element("notes"),
Summary = (string)bookElement.Element("summary")
};
To read the details of the book contained in the child elements, we select the relevant element with the Elementaxis method and use the explicit cast operators defined on XElement to convert the value to the proper data types.
While this query gives us the basic details for our books, it doesn’t include the data that is nested within child nodes, such as the list of authors and reviews. To include the authors, we can update our query to include a nested query that returns a list of Author objects that matches the author’s details contained in the XML. Since the <author> elements are not directly below the <book> element, we can use the Descendants query axis method and once again use the object initializer syntax to build our author object with the data contained within the XML.
Since our query expression returns an IEnumerable<Author>, we can assign the results of the query expression directly to the Authors property on the book instance. To include the reviews, we can take the same approach. We write a query expression that reads the reviews out of the XML and into a list of Review objects:
If we wrap everything together, we end up with the program in listing 11.3, which creates a list of books from our XML document and prints them to the console using ObjectDumper
Listing 11.3. Creating objects from XML
With LINQ to XML we can transform our XML data into alternate data formats quickly and easily. By combining query expressions with the new object initializer syntax offered by C# 3.0, we can build objects from XML using familiar LINQ language constructs. As you’ll see throughout the remainder of this chapter, LINQ to XML provides a simple and consistent API for transforming data to and from XML.
Now that we’ve covered how to create objects from XML, let’s look at how we can handle our next common scenario, creating XML from objects using VB9’s XML literals.
11.2. Creating XML from object graphs
As we discussed in chapter 9, XML literals is a Visual Basic feature that allows XML to be created using familiar XML syntax. Our XML will be created from a set of in-memory objects that contain details about books within our LinqBooks catalog, as well as reviews for those books. Let’s dig into more specifics regarding our goal for this scenario.
11.2.1. Goal
No application is complete without a little RSS sprinkled in. In this section, our goal is to create an RSS feed that contains all the book reviews within our LinqBooks catalog. To keep things simple, we’re going to assume that we already have a set of objects loaded with the book and review data, and that the RSS feed that we create will be constructed directly from those objects.
Since we often deal with in-memory objects when developing applications, this section will provide a good overview of how in-memory objects can be transformed into XML using XML literals.
Before getting started with the code, let’s look at a sample of the XML document that we’ll produce.
<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
<channel>
<title>Book Reviews</title>
<description>LINQBooks Book Reviews</description>
<item>
<title>Review of LINQ in Action by Steve Eichert</title>
<description>This is an amazing book!...and I'm not biased!
</description>
</item>
</channel>
</rss>
The XML that we produce will be a standard RSS 2.0 feed that contains the reviews for our books. The final XML will contain one <item> element for each review within the data used to produce the feed.
Now that we’ve determined our goal for this scenario, let’s look at how we’re going to use XML literals to achieve it.
11.2.2. Implementation
By allowing XML to be directly embedded in code, XML literals reduce the ambiguity that often exists when creating XML. Rather than focusing on the programming API required to create XML, XML literals allow developers to focus on the XML that they want to produce.
In this scenario, we’re going to use XML literals to create an RSS feed that contains the reviews within a set of in-memory objects. We’ll construct a query to return the book and review data and use the support for embedding expressions inside XML literals to produce our final XML.
To create our RSS feed, we’ll query our in-memory objects for all the books that have at least one review, and create an RSS feed containing the reviews for the books using XML literals. We’re going to use the objects that we reviewed in section 11.1 (see figure 11.1) as well as the SampleData class introduced in chapter 4.
As a reminder, listing 11.4 shows how the SampleData class is defined.
Listing 11.4. The SampleData class introduced in chapter 4
using System;
using System.Collections.Generic;
using System.Text;
namespace LinqInAction.LinqBooks.Common {
static public class SampleData {
static public User[] Users ={
new User { Name="Steve Eichert"},
new User { Name="Fabrice Marguerie"},
new User { Name="Jim Wooley"}
};
static public Publisher[] Publishers = {
new Publisher {Name="FunBooks"},
new Publisher {Name="Joe Publishing"},
new Publisher {Name="I Publisher"}
};
static public Author[] Authors = {
new Author {FirstName="Johnny", LastName="Good"},
new Author {FirstName="Graziella", LastName="Simplegame"},
new Author {FirstName="Octavio", LastName="Prince"},
new Author {FirstName="Jeremy", LastName="Legrand"}
};
static public Book[] Books = {
new Book {
Title="Funny Stories",
Publisher=Publishers[0],
Authors=new[]{Authors[0], Authors[1]},
Price=25.55M,
Isbn="0-000-77777-2",
Reviews=new Review[] {
new Review {
User=Users[0],
Rating=5,
Comments="It was very funny indeed!"
},
new Review {
User=Users[1],
Rating=4,
Comments="It was Fabulous."}
}
},
new Book {
Title="LINQ rules",
Publisher=Publishers[1],
Authors=new[]{Authors[2]},
Price=12M,
Isbn="0-111-77777-2"
},
new Book {
Title="C# on Rails",
Publisher=Publishers[1],
Authors=new[]{Authors[2]},
Price=35.5M,
Isbn="0-222-77777-2",
Reviews=new Review[] {
new Review {
User=Users[0],
Rating=5,
Comments="Say goodnight to the Rails Party,
Microsoft is here!"},
new Review {
User=Users[1],
Rating=5,
Comments="Don Box said he likes Ruby, little
did we know he'd turn C# into Ruby.NET!"}
}
},
new Book {
Title="All your base are belong to us",
Publisher=Publishers[1],
Authors=new[]{Authors[3]},
Price=35.5M,
Isbn="0-333-77777-2"
},
new Book {
Title="Bonjour mon Amour",
Publisher=Publishers[0],
Authors=new[]{Authors[1], Authors[0]},
Price=29M,
Isbn="2-444-77777-2"
}
};
}
}
To build our RSS feed; we want to select the books from the SampleData.Books array shown in listing 11.4. Since the RSS will have details of the reviews, we want to limit the list of books to those that have at least one review. To limit the set of books returned to only those with reviews, we’ll check that the Reviews property on the book is not null and that at least one review exists using listing 11.5.
Listing 11.5. Limit the set of books returned to only those with reviews
Dim reviews =
From book In SampleData.Books _
Where Not IsNothing(book.Reviews) AndAlso book.Reviews.Count > 0 _
From review In book.Reviews _
Select book, review
Since we can have multiple reviews per book, we’ve created a second-level query that selects each review from the book.Reviews property. To create our XML feed, we’ll need to embed this query into our XML literals code.
Now that we’ve defined a query for retrieving the necessary data, we need to create the XML literal code that will produce the desired XML. With XML literals, VB allows us to create XML using familiar XML syntax. Now we can focus on the desired XML output rather than on the details of the XML programming API. As mentioned in chapter 9, under the covers, XML literals get turned into functional construction statements, which allow them to be interoperable with other languages that don’t support XML literals.
Creating the XML literals is a matter of copying and pasting the desired XML into our class that will be responsible for the XML creation. We can, of course, hand-code the XML as well, but since we’ve already done that once to create the sample document, we might as well leverage all that typing here. We’ll use listing 11.6 as a template for building our desired XML.
Listing 11.6. XML literals template for building RSS
Dim rss = _
<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0">
<channel>
<title>Book Reviews</title>
<description>LINQBooks Book Reviews</description>
<item>
<title>Review of LINQ in Action by Steve Eichert</title>
<description>
This is an amazing book!...and I'm not biased!
</description>
</item>
</channel>
</rss>
With the XML literals code in place, we’re ready to integrate the query that selects the book and review data from the SampleData.Books array, as well as place “holes” within the XML literals that we can use to plug in the proper values from our query. As mentioned in chapter 9, we can place expression holes in our XML literals using the <%= Expression %> and <% Statement %> syntax.
To create our full RSS feed, we’re going to replace the single <item> element in listing 11.6 with our query for retrieving the book and review data. Once we have our queries and XML literal code in place, we replace the hard-coded book name, reviewer name, and review with embedded expressions that place the proper values from the book and review objects into the resulting XML. The complete code for creating our RSS feed is shown in listing 11.7.
Listing 11.7. Creating XML from an object graph using XML literals
Imports LinqInAction.LinqBooks.Common
Module XmlFromObjectsUsingXmlLiterals
Sub Main()
Dim rss = _
<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0">
<channel>
<title>Book Reviews</title>
<description>LINQBooks Book Reviews</description>
<%= From book In SampleData.Books _
Where Not IsNothing(book.Reviews) _
AndAlso book.Reviews.Count > 0 _
Select _
From review In book.Reviews _
Select _
<item>
<title>
Review of <%= book.Title %> by <%= review.User.Name %>
</title>
<description><%= review.Comments %></description>
</item> %>
</channel>
</rss>
Console.WriteLine(rss)
End Sub
End Module
When we run this code, we end up with the following XML printed to the console:
<rss version="2.0">
<channel>
<title>Book Reviews</title>
<description>LINQBooks Book Reviews</description>
<item>
<title>Review of Funny Stories by Steve Eichert</title>
<description>It was very funny indeed!</description>
</item>
<item>
<title>Review of Funny Stories by Fabrice Marguerie</title>
<description>It was Fabulous.</description>
</item>
<item>
<title>Review of C# on Rails by Steve Eichert</title>
<description>Say goodnight to the Rails Party, Microsoft is here!
</description>
</item>
<item>
<title>Review of C# on Rails by Fabrice Marguerie</title>
<description>Don Box said he likes Ruby, little did we know he'd turn
C# into Ruby.NET!</description>
</item>
</channel>
</rss>
Now that we have our RSS feed created, we can deploy it to our web host and start sharing our reviews with the world. See figure 11.2.
Figure 11.2. The resulting Reviews RSS feed
In this section, we created an RSS feed from our in-memory book objects. By utilizing XML literals, we were able to stay focused on the end goal and plug in data from our objects as necessary. The result is a more productive programming environment for developers. While it was a fun exercise to convert our in-memory objects to XML, it’s more likely that we’ll need to pull information from our database. In the next section, we explore what LINQ to XML offers for such a case.
11.3. Creating XML with data from a database
The relational database is at the heart of many of the applications we build. It provides a reliable means for storing, aggregating, and processing our data. Often the data within our applications need to be shared with others, and more often than not the format chosen for the interchange is XML. In this scenario, we’ll explore how we can take data within our relational database and create an XML representation of it that can be shared with others. As we learned in chapters 6, 7, and 8, LINQ to SQL provides powerful capabilities for working with our relational data via LINQ. In this scenario, we’ll get a chance to see how nicely LINQ to XML can be used in conjunction with LINQ to SQL to combine relational and XML data within a single query. Let’s get started by looking at our goal for this scenario.
11.3.1. Goal
Our goal is to export XML of the books within our book catalog database (see figure 11.2). This will allow us to share our catalog with other LinqBooks users. We’re going to start by creating the desired XML with the Paste XML as LINQ Visual Studio Add-In that is shipped as a sample with Visual Studio 2008, and then define our queries to retrieve the data from the database. Once we have the stub code for our XML and the queries for retrieving the data, we’ll combine the two to create our desired XML.
Listing 11.8 shows the XML that we’ll create.
Listing 11.8. The XML that will be created from our database
<books>
<book>
<title>LINQ in Action</title>
<authors>
<author>
<firstName>Steve</firstName>
<lastName>Eichert</lastName>
<webSite>http://iqueryable.com</webSite>
</author>
<author>
<firstName>Fabrice</firstName>
<lastName>Marguerie</lastName>
<webSite>http://linqinaction.net/</website>
</author>
<author>
<firstName>Jim</firstName>
<lastName>Wooley</lastName>
<webSite>http://devauthority.com/blogs/jwooley</website>
</author>
</authors>
<subject>
<name>LINQ</name>
<description>LINQ shall rule the world</description>
</subject>
<publisher>Manning</publisher>
<publicationDate>January, 2008</publicationDate>
<price>43.99</price>
<isbn>1933988169</isbn>
<notes>Great book!</notes>
<summary>LINQ in Action is great!</summary>
<reviews>
<review>
<user>Steve Eichert</user>
<rating>5</rating>
<comments>What can I say, I'm biased!</comments>
<review>
<reviews>
</book>
</books>
To create this XML we’ll need to query data from the database tables shown in figure 11.3.
Figure 11.3. Database schema
Now that we’ve seen the structure of the XML we’re trying to produce as well as the schema for the database tables we’ll be pulling our data from, let’s look at the technique we’ll use to accomplish our goal.
11.3.2. Implementation
Functional construction provides a powerful means by which we can create XML. By embedding query expressions within functional construction statements, XML trees can be built from a database or any other data source. Since LINQ to SQL provides an implementation of the standard query operators for querying relational databases, we can use familiar LINQ query expressions to retrieve the data for creating the desired XML.
The XML tree that we’re going to produce requires information from every table within the database schema shown in figure 11.3. To retrieve the data, we’ll generate a set of objects from our database using LINQ to SQL. As we discussed in chapter 6, LINQ to SQL creates a class for every table in the database. In addition to generating properties that map to each column in the database, LINQ to SQL also generates properties to represent the relationships within the database. This allows all the relevant information required for creating our XML to be retrieved via the Book class.
Before diving into the code necessary for creating the XML from the database, we need to generate our LINQ to SQL objects. There are several options available for generating our objects. To keep things simple, we’ll use the SqlMetal.exe command-line executable that ships with LINQ to SQL.
CMD>Sqlmetal /server:localhost /database:LinqInAction /pluralize /namespace:LinqInAction.LinqToSql /code:LinqInAction.cs
While the details of SqlMetal are outside the scope of this chapter, you can find more information about how to leverage it to create LINQ to SQL objects by checking out chapter 7 or by browsing to Program FilesMicrosoft SDKsWindowsv6.0ABin and executing SqlMetal.exe without any command-line switches.
usage: sqlmetal [options] [<input file>] options: /server:<name> database server name /database:<name> database catalog on server /user:<name> login user id /password:<name> login password /views extract database views /functions extract database functions /sprocs extract stored procedures /xml[:file] output as xml /code[:file] output as source code /map[:file] generate xml mapping file instead of attributes /language:xxx language for source code (vb,csharp) /namespace:<name> namespace used for source code /pluralize auto-pluralize table names /dataAttributes auto-generate DataObjectField and Precision attributes /timeout:<seconds> timeout value in seconds to use for database commands
Once we have our LINQ to SQL objects generated, we can write the code for creating our XML tree. To get started, we can copy the XML in our sample XML document to the clipboard and use the Paste XML as LINQ Visual Studio add-in to give us the C# code shown in listing 11.9.
Listing 11.9. Stub code via copy and paste XElement
In the listing, we’ve identified four sections of XML that will need to be replaced with LINQ expressions. The author element
will need to be replaced with a query expression that returns an IEnumerable<XElement> containing the author elements. We’ll need to replace the hard-coded name and description on the Subject 
with the name and description from the Subject table that the Book table links to. Next we’ll need to replace the publisher name 
with the proper publisher name from the Publisher table. Finally, the hard-coded lists of reviews 
will need to be replaced with a query expression that returns an IEnumerable <XElement> built from the Reviews table.
Now that we have the stub code in listing 11.9, we need to plug in query expressions for retrieving information about the book, as well as its publisher, subject, authors, and reviews. To express our queries, we can use query expressions or explicit dot notation. Since query expressions are more readable and compact and work better when embedding queries within functional construction statements, we’ll use them to create our XML.
The first query expression that we need returns all the books within our database. Although the sort order of the books doesn’t matter, we’ll sort them by title to make our query more interesting.
LinqInActionContext ctx = new LinqInActionContext();
var books = from book in ctx.Books
orderby book.Title
select book;
To retrieve the other details necessary for building the XML, we can traverse the relationships that SqlMetal creates on the Book class. Figure 11.4 shows a class diagram of the classes created by SqlMetal to see what relationships are available.
Figure 11.4. A class diagram showing the classes and relationships created by SqlMetal for the LinqBooks database
As we can see in figure 11.4, the Book class has relationships that allow us to retrieve all the related data using properties such as book.BookAuthors, book.Subject, book.Reviews, and book.Publisher. Utilizing these properties, we can create queries for returning the data necessary for building our complete XML document, as in listing 11.10.
Listing 11.10. Retrieve the data necessary for building our XML document using LINQ to XML queries
Once we have these queries, we can plug them into our original statement that creates our XML. In listing 11.11, we update the hard-coded element values with the appropriate value from the book instance and replace the repeating elements—such as the authors and reviews—with query expressions that return an IEnumerable<XElement> that represents the items.
Listing 11.11. Code to create our full XML tree
With the powerful creation capabilities offered by functional construction, as well as its tight integration with other LINQ-enabled technologies such as LINQ to SQL, LINQ to XML is primed to become the runaway winner when it comes to selecting an XML stack to build applications on top of. Not only does it offer all the query advantages that we’ve seen time and time again from the LINQ framework, it also provides the most powerful and user friendly XML API available!
Now that we’ve seen how to create XML from a database, it’s time to move on to our next scenario.
11.4. Filtering and mixing data from a database with XML data
Now that we’ve seen how we can create XML from a database, it’s time to further explore how we can integrate XML and relational data within a single LINQ query. In this scenario, we’re going to look at how we can mix the data within our database with XML data retrieved from Amazon’s e-commerce web services. As we’ll see in this example, LINQ to XML makes it easy to join XML data with data from a relational database. Before we jump into how we accomplish this, let’s review our goal.
11.4.1. Goal
The Amazon e-commerce web services provide developers with a set of APIs for retrieving data from the Amazon catalog. In this scenario, we’ll query the ItemLookup service for the reviews that have been submitted for a set of books within our LinqBooks catalog. With our XML data in hand, we’ll then query our relational database for additional book details. Finally, we’ll integrate the two data sources with a single query and display the results on screen.
The following is a sample of the output:
Book: CLR via C#, Second Edition -------------------------- Rating: 5 -------------------------- Jeffrey Richter is my hero, he really is. This guy is simply amazing. I just cant imagine how he pulls it off - the toughest topics explained in the clearest manner.Moreover, he has achieved this feat over and over again. Any book he has written is testimony for this. <br />In his books, you would find information where you wouldnt find in any other place. You would also find information you can find elsewhere, but not as clear as his. He has the advantage of working closely with Microsoft and consulting with the .NET team, but I would say he would be a great author and teacher even without that advantage. <br />As about this book, it should not be your first C# book. I suggest you get beginner's C# book first (if you dont know any C#), I suggest Jesse Liberty's book, and then come to this book. You would get a tremendous advantage over people who havent read this book and your understanding of the building blocks of .NET platform would be in depth. His chapters on Threading alone is worth the price of the book. This book is an absolute pleasure to read, just like any other book from Richter. Grab your copy today! If there really is a 5 star book,this one is it. <br />Nobody writes like Richter, nobody. -------------------------- Rating: 5 -------------------------- I echo the positive reviews below. If you already know the .Net platform fairly well and want to understand the internals of the CLR, this is the best place to start. This edition is as good or better than his 1.0/1.1 version, Applied .Net Framework Programming.
By allowing us to easily load XML data from a web service and mix that data with data from a LINQ to SQL query, LINQ to XML provides a powerful method for creating XML content. Let’s look at the technique we’ll use for creating our desired result.
11.4.2. Implementation
LINQ provides several mechanisms for mixing XML and relational data. To allow users to see the reviews on Amazon for a given book, we’ll be joining the data available within our relational database with XML data returned by a call to the Amazon ItemLookup service. Although the data is in completely different formats and from completely different sources, LINQ allows us to easily mix the data by joining the two sources on the common data points, in this case the ISBN, then selecting the data we’re interested in from each source within our select clause. In order to get the data that we want to display to users, we’ll need to perform two queries. The first query will retrieve XML data from Amazon and the second query will retrieve information from the database.
Let’s start by loading the XML data from Amazon. Amazon provides several different ways to access its web services. Developers can request data from Amazon using either the REST or SOAP APIs offered by Amazon. Since LINQ to XML is ideally suited to work as a client for REST web services, we’ll leverage that strength here and use the REST web service API.
As we briefly mentioned in chapter 10, to access the Amazon web services, you’re required to register with the Amazon Web Services program.[1] After registering with Amazon, you’ll be assigned a key, which grants you access to the Amazon web services. Once you have the registration complete, you can start using the web services to retrieve information from Amazon.
1http://www.amazon.com/gp/aws/registration/registration-form.html
Now that we have that disclaimer out of the way, let’s figure out how to retrieve data from the Amazon ItemLookup service. To retrieve data from the ItemLookup service, we create a request URL that includes all the request parameters that detail the information that we’re interested in retrieving from Amazon. To retrieve the reviews, we use the following request URL:
http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&
AWSAccessKeyId={AccessKey}&
Operation=ItemLookup&
ItemId={ItemID}&
ResponseGroup=Reviews
Table 11.1 shows the common request parameters that are included in Amazon web services calls. For more detailed information on the parameters available, refer to the Amazon Web Service documentation at http://aws.amazon.com/.
Table 11.1. Common Amazon e-commerce web service operation request parameters
|
Parameter |
Value |
Description |
|---|---|---|
| Service | AWSECommerceService | Specifies the ECS service. |
| AWSAccessKeyId | Your Amazon-assigned Access Key ID | You can register for an access key ID from the Amazon web site if you do not have one. Every ECS 4.0 request must contain either an access key ID or a subscription ID, but not both. |
| SubscriptionId | Your Amazon-assigned subscription ID | Every ECS 4.0 request must contain either an access key ID or a subscription ID, but not both. Starting with version 2006-06-28, ECS stopped distributing subscription IDs. If you already have one, it will continue to work. |
| Operation | Operation you wish to perform | One of the ECS operation types.[a] |
| ResponseGroup | Reviews | Specifies what data is to be returned by the current operation; allows you to control the volume and content of returned data. |
| ItemId | The list of ASINs for the products to return data for | Product(s) you would like information about. You may specify up to 10 IDs using a comma-separated list (REST) or multiple elements (SOAP). By default the item IDs are assumed to be ASINs, unless you specify the IdType parameter. |
ahttp://docs.amazonwebservices.com/AWSEcommerceService/2006-06-28/PgOverviewArticle.html#Summary
Now that our request URL is created, we can load the XML response using the Load method of XElement, as shown in listing 11.12.
Listing 11.12. Load XML from Amazon’s e-commerce web service
string requestUrl =
"http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&" +
"AWSAccessKeyId={AccessKey}&" +
"Operation=ItemLookup&" +
"ItemId=0735621632&" +
"ResponseGroup=Reviews";
XElement amazonReviews = XElement.Load(requestUrl);
Before moving on, let’s look at the XML that is returned for our request, shown in figure 11.5.
Figure 11.5. Amazon.com reviews XML
As you can see in the figure, the reviews are returned within the <CustomerReviews> element. We’re particularly interested in the <Rating> and <Content> elements, as they contain the information that we’re going to display on screen.
Now that we’ve covered some of the basics regarding how we’ll retrieve the Amazon reviews, let’s look at how to retrieve the books from our relational database. As we saw in section 11.3, LINQ to SQL makes it easy to retrieve data from our database.
As we did in section 11.3, we’ll retrieve the details of our book with the LinqInActionDataContext.Books property.
LinqInActionDataContext ctx = new LinqInActionDataContext(); var books = ctx.Books;
With the details of our queries out of the way, it’s time we move on to joining them together and displaying the details onscreen. To join the XML data returned by Amazon and the book details within our database, we use the Join operator provided by LINQ. As discussed in chapter 3, the Join and GroupJoin operators allow us to join two sequences on matching keys extracted from each sequence. In our case, the two sequences are the sequence of book elements returned by amazonReviews.Elements(ns + "Items").Elements(ns + "Item") and the sequence of books within the database that we access through the Books property on the LinqInActionDataContext class. We’ll extract the ASIN element out of our XML and the ISBN out of our Book, and use them to join our two sequences together.
When we put everything together we end up with the program in listing 11.13.
Listing 11.13. Mixing XML and relational data within a single query
using System;
using System.Linq;
using System.Xml.Linq;
using System.Data.Linq;
using LinqInAction.LinqToSql;
namespace Chapter11.MixXmlAndRelationalData {
class Program {
public const string AmazonAccessID = "15QN7X0P65HR0X975T02";
static void Main(string[] args) {
string requestUrl = "http://webservices.amazon.com/onca/xml?Service=AWSECommerceService" +
"&AWSAccessKeyId=" + AmazonAccessID +
"&Operation=ItemLookup&" +
"ItemId=0735621632&" +
"ResponseGroup=Reviews";
XNamespace ns =
"http://webservices.amazon.com/AWSECommerceService/2005-10-05";
LinqInActionDataContext ctx = new LinqInActionDataContext();
XElement amazonReviews = XElement.Load(requestUrl);
var results =
from bookElement in amazonReviews.Element(ns + "Items")
.Elements(ns + "Item")
join book in ctx.Books on
(string)bookElement.Element(ns + "ASIN") equals book.Isbn.Trim()
select new {
Title = book.Title,
Reviews =
from reviewElement in bookElement.Descendants(ns + "Review")
orderby (int)reviewElement.Element(ns + "Rating") descending
select new Review {
Rating = (int)reviewElement.Element(ns + "Rating"),
Comments = (string)reviewElement.Element(ns + "Content")
}
};
string seperator = "--------------------------";
foreach (var item in results) {
Console.WriteLine("Book: " + item.Title);
foreach (var review in item.Reviews) {
Console.WriteLine(seperator + "
Rating: " +
review.Rating + "
" +
seperator + "
" + review.Comments);
}
}
}
}
}
By leveraging the built-in join support offered by LINQ, we can build queries that select from multiple data sources and create new objects from subsets of the data within each data source. It should be noted that in order to do the join between our XML and database, LINQ enumerates each element returned by ctx.Books. This results in a query being run that returns every book within our database. In our particular scenario, we’re not concerned with the performance impact of that operation, but it’s something you should keep in mind when designing queries that join XML and relational data.
At this point, we’ve covered several common scenarios involving XML and our database. In the next section, we cover the most obvious remaining scenario, updating our database with information contained within XML.
11.5. Reading XML and updating a database
In the previous two sections, we’ve looked at how we can use LINQ to XML to create XML from a database as well as how we can join XML and relational data within a single LINQ query. In this section, we go a step further and investigate how we can use LINQ to XML and LINQ to SQL to update our database with information contained within XML. Before getting started with our goal, we should mention that this scenario is a little more involved than those that we’ve covered thus far. As such, this scenario is a bit longer, but also a bit more interesting. The real power of LINQ is shown when all the different LINQ-enabled technologies are used together. In this scenario, we use LINQ to XML, LINQ to Objects, and LINQ to SQL. Additionally, we query a web service, a database, and a UI for data, and learn about how we can update our relational data with information returned from a web service!
Now that we’ve whetted your appetite with what’s to come, let’s get started by identifying our goal for this scenario.
11.5.1. Goal
In this section, we’re going to add an exciting new feature to our sample application. We’re going to allow users to add new books to their LinqBooks catalog directly from Amazon. To enable this, we’ll build a simple GUI screen that can be used to search Amazon for books. See figure 11.6.
Figure 11.6. Amazon search UI
As shown in the figure, a search box at the top of the UI will allow users to enter the keywords to search on. After entering keywords, users will be able to click the Search button. When the Search button is clicked, a web service call will be made to Amazon for all books that match the entered keywords. The results provided by Amazon will be displayed in the grid, where users will be able to select the book(s) to be imported by checking the Import check box. Before importing the selected books, they need to be classified. The Subject combo box, at the bottom of the form, lists all the subjects currently in the database. Once the appropriate subject is selected and the Import button is clicked, the books will be inserted into the LinqBooks database.
11.5.2. Implementation
In order to accomplish our goal, we’ll need to leverage a lot of what we’ve learned throughout the last several chapters. We’re going to query the Amazon web service using LINQ to XML, query our relational database using LINQ to SQL, build objects from XML, and finally create new records in our database with information within the XML.
As we saw in the previous section, LINQ provides a Join operator that allows XML and relational data to be included in a single LINQ query. In this section, we’re going to go a step further and join XML, relational data, and in-memory objects. When we’re through, we’ll have a simple Windows Forms application that allows users to search Amazon.com for books and import them into their LinqBooks database.
After creating our basic UI, which we’ll skip here since we have a lot of ground to cover, we need to start creating our LINQ queries for retrieving information from Amazon, as well as our database. We’ll once again use the REST service to retrieve books that match the keywords entered into the UI.
We need to formulate a URL with all the details of our query before making our request. The URL that we use to retrieve the books matching our keywords is shown in listing 11.14.
Listing 11.14. Amazon.com REST URL for retrieving books by a keyword
http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&
AWSAccessKeyId={AccessKey}&
Operation=ItemSearch&
SearchIndex=Books&
Keywords={keywords}&
ResponseGroup=Large
Once our request URL is formulated, we make our service call to Amazon by calling the static Load method on XElement. When we call Load, the XElement class asks System.Xml.XmlReader to load the URL. This results in a web request being made to Amazon’s server, which in turn constructs an XML document that contains the results of our search. The following is the code to retrieve the books matching the keywords supplied in the keywords text box:
string requestUrl =
"http://webservices.amazon.com/onca/xml?Service=AWSECommerceService" +
"&AWSAccessKeyId=" + AmazonAccessKey +
"&Operation=ItemSearch" +
"&SearchIndex=Books" +
"&Keywords=" + keywords.Text +
"&ResponseGroup=Large";
XElement amazonXml = XElement.Load(requestUrl);
Now that we have a loaded XML tree, we need to read the books out of the XML and display them to the user. Before investigating how we can display the books, let’s look at the XML that is returned, shown in listing 11.15
Listing 11.15. Amazon XML for a book
<?xml version="1.0" encoding="utf-8"?>
<ItemSearchResponse xmlns="http://webservices.amazon.com/AWSECommerceService/2005-10-05">
<Items>
<TotalResults>1389</TotalResults>
<TotalPages>139</TotalPages>
<Item>
<ASIN>0977326403</ASIN>
<DetailPageURL>http://www.amazon.com/gp/redirect.html%3FASIN=0977326403%26tag=ws%26lcode=xm2%26cID=2025%26ccmID=165953%26location=/o/ASIN/0977326403%253FSubscriptionId=15QN7X0P65HR0X975T02</DetailPageURL>
<SalesRank>49</SalesRank>
<ItemAttributes>
<Author>Jim Collins</Author>
<Binding>Paperback</Binding>
<DeweyDecimalNumber>658.048</DeweyDecimalNumber>
<EAN>9780977326402</EAN>
<ISBN>0977326403</ISBN>
<Label>Collins</Label>
<ListPrice>
<Amount>1195</Amount>
<CurrencyCode>USD</CurrencyCode>
<FormattedPrice>$11.95</FormattedPrice>
</ListPrice>
<Manufacturer>Collins</Manufacturer>
<NumberOfItems>1</NumberOfItems>
<NumberOfPages>42</NumberOfPages>
<PackageDimensions>
<Height Units="hundredths-inches">13</Height>
<Length Units="hundredths-inches">916</Length>
<Weight Units="hundredths-pounds">21</Weight>
<Width Units="hundredths-inches">642</Width>
</PackageDimensions>
<ProductGroup>Book</ProductGroup>
<PublicationDate>2005-11-30</PublicationDate>
<Publisher>Collins</Publisher>
<Studio>Collins</Studio>
<Title>Good to Great and the Social Sectors:
A Monograph to Accompany Good to Great</Title>
</ItemAttributes>
</Item>
</Items>
</ItemSearchResponse>
After reviewing the XML returned by Amazon and taking a quick peek at the grid we’re using to display the results of our search, we find that the two primary pieces of information we need to read from the XML are within the <Title> and <ISBN> elements. Both elements live inside the <ItemAttributes> element. With this knowledge in hand, let’s start to put the pieces in place for our GUI. We’ll start by adding an event handler for the Click event of our search button.
Once our event handler is in place, we add the code for constructing our request URL and then make the web service call to Amazon by calling XElement.Load(requestUrl). Since we’re going to be using the Amazon results outside the scope of the searchButton_Click handler, we assign the results of our web service call to a class-level variable named amazonXml. Finally, we construct a query expression that creates a sequence of Books by reading the title and ISBN out of the XML returned by Amazon. See listing 11.16.
Listing 11.16. When Search button is clicked, query Amazon for books matching our keywords
Before moving on, let’s dig into our query expression and examine how we’re building our list of books. The first thing to point out is that we create an XNamespace instance with the default namespace used in the XML returned by Amazon. As we mentioned in previous chapters, all queries in LINQ to XML use the full expanded name (namespace + local name) of elements. We declare our namespace once, before constructing our query expression, to keep our query clean and concise. Once our namespace is declared, we can move on to constructing our query expression. The XML returned by Amazon contains one <Item> element for each book that matches our search. We use the Descendants query axis method available on XElement to retrieve each <Item> element and build a new Book instance using an inline object initializer. We set the Title and ISBN properties on the book instance to the values within the <Title> and <ISBN> elements of the XML. Also of note is our use of the let clause. Rather than typing the full expression for reading the title and ISBN out of the /Item/ItemAttributes/ Title and /Item/ItemAttributes/ISBN elements, we instead use a let clause to assign the ItemAttributes element to a query variable called attributes. This allows us to use attributes in our select clause, which results in less typing and a more readable query. Finally, we bind our query defined in the books variable to the binding source for our grid, which populates the grid with the books returned by Amazon. Now that we have the books matching our keywords in our grid, let’s move on to figuring out how we can get them imported into our database.
Once the books returned from Amazon are shown in the grid, users can select the books to import by checking the check box in the first column. Once the user has checked the books she would like to import, she clicks the Import button to put them into the LinqBooks database.
The first thing that we need to do when the user clicks the Import button is figure out what items in the grid have been checked. To get the books that have been selected, we want to find all the rows within our grid that have the check box in the first cell checked. To determine whether the check box in the first cell of our grid is checked, we examine the EditedFormattedValue of the DataGridViewCell. If we were still stuck in the olden days of programming, we would loop over every row in the grid and check whether the EditedFormattedValue of the first cell was true. If so, we’d then add the Book that was bound to the row to a list of some sort. Lucky for us, we’re not stuck in the olden days. Rather than looping over the rows in the grid, we can construct a query expression to select the rows that match a predicate, which we’ve defined. The code to do just that follows:
var selectedBooks =
from row in bookDataGridView.Rows.OfType<DataGridViewRow>()
where ((bool) row.Cells[0].EditedFormattedValue) == true
select (Book) row.DataBoundItem;
Notice our use of the OfType<> extension method. OfType<> allows us to turn an old-school DataGridViewRowCollection into an IEnumerable of DataGridViewRow objects. Once we have the DataGridViewRow objects in an IEnumerable, we can query them using LINQ.
Once we have the books that have been checked for import, we can begin to write the code that will import the books into our database. It’s important to remember that the books bound to the DataGridView only contain the title and ISBN of the book. In order to get all the details for the book, we’re going to need to return to the XML that we retrieved from Amazon.
As we saw when we built the list displayed in the grid, most of the details of the book are contained within the <ItemAttributes> element of the XML. We need to match the item attributes contained in the XML with the columns that we have in our database (see figure 11.2 to review the database schema). The mapping of the XML to the database can be seen in table 11.2.
Table 11.2. Mapping of XML to database for Book attributes
In table 11.2 we define the mapping between the XML and our database. Since the title, ISBN, publication date, and price have a straightforward mapping; let’s start by constructing a query that will read those values out of the XML and into our Book objects.
Listing 11.17. Read the values out of the XML returned by Amazon into our Book objects
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice")
};
With this query in place, we need to start thinking about how we’re going to filter the list of books to only those that we checked in the grid. Additionally, we need to figure out how we’re going to set the Publisher for our book, as well as the list of authors. Let’s start by figuring out how we can filter our list of books to only those that the user selected.
To filter the list of books, we need to join the results of the query for finding books from Amazon with the query for retrieving the list of selected books from the grid. See listing 11.18.
Listing 11.18. Joining the results returned by Amazon with the books selected in the grid
var selectedBooks =
from row in bookDataGridView.Rows
where ((bool)row.Cells[0].EditedFormattedValue) == true
select (Book)row.DataBoundItem;
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks on
(string)amazonItem.Element(ns + "ItemAttributes")
.Element(ns + "ISBN")
equals selectedBook.Isbn
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice"))
};
As you can see, we again use the Join operator provided by LINQ for joining our two data sources together. This provides the results we’re looking for, a list of books built from the data returned by Amazon filtered down to only those that were checked for import in the grid. Now that we have the books we’re interested in, let’s explore how we can include the additional details such as the Publishers and Authors.
At first glance, including the Publishers and Authors seems like a simple task. Since the data from Amazon includes the publisher and authors, it’s simply a matter of updating our query expression to select the values out of the XML and assign them to the appropriate properties on our book instance. Unfortunately, nothing in life is as easy as it first seems.
As we import books into our catalog, we’ll slowly build up a list of authors and publishers. If we’re not careful, we could end up with duplicate authors and publishers within our database. If we go to import a book that has the same publisher or author as a book already in the database, we’ll end up with duplicates if we don’t specifically handle that case. We could ignore this fact and insert a new publisher and author for every book, but that would result in data integrity issues, which we’d rather not introduce. If the publisher or author already exists in the database, we want to assign that existing publisher or author to the book. If the publisher or author does not exist, we want to create a new instance and insert them into the database. When building objects in an imperative manner, this wouldn’t be too hard to solve, but how do we handle this when our objects are getting built within a query expression?
Let’s start by looking at how we can include the publisher in our query. The XML returned by Amazon has the publisher stored in the /Item/ItemAttributes/Publisher element. We need to perform a search on the Publisher table to see if a publisher with a Name equal to the value in our <Publisher> element exists. If so, the existing publisher needs to be pulled out of the database and assigned to the Publisher property on our book instance. If a publisher doesn’t exist, we need to create a new Publisher instance with the publisher’s name that is in the XML and assign it to our book. To accomplish this, we’ll use the grouping join support in LINQ as well as the DefaultIfEmpty operator.
By combining a grouping join with the DefaultIfEmpty operator, we are able to create a left outer join between our XML and Publisher table. Once we have our left outer join in place, we can use the null coalescing operator (??) and object initializers to get our desired behavior. If our join results in a null publisher, we’ll create a new Publisher; otherwise, we’ll assign our existing publisher to the Publisher property on our book.
When we put everything together, we end up with a query expression that joins our Amazon item with the Publisher table in our database and either assigns the existing publisher to our book or creates a new one. See listing 11.19.
Listing 11.19. Populate the book publisher by joining to the Publisher table
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "ISBN")
equals selectedBook.Isbn
join p in ctx.Publishers on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "Publisher")
equals p.Name into publishers
let existingPublisher = publishers.SingleOrDefault()
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
Publisher = (existingPublisher ??
new Publisher {
ID = Guid.Empty,
Name = (string)attributes.Element(ns + "Publisher")
}
),
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice"))
};
Now that we have the publisher out of the way, it’s time to move on to the authors. To include the authors, we’re going to use some of the same methods we just covered for including the publisher. The primary difference is that a book can have multiple authors. Since our existing query expression is getting a little more complex than we’d like, we’ll separate out the logic for assigning the authors into a separate method.
Before diving into the code for creating our authors, let’s quickly review what we need to accomplish. The XML returned for a book can have one-to-many <Author> elements. We need to ensure that when we import our book, we don’t re-create an author that is already in our database. As we can see in figure 11.7, we also need to map each author element into an Author object, which will be placed inside a BookAuthor object. The BookAuthor object is an association object that represents the link between a book and the authors who wrote the book. For more details about how one-to-many relationships are represented in LINQ to SQL, refer to chapter 7. Figure 11.7 shows the relationship between the Book class and the Author class.
Figure 11.7. Author and Book class diagram
After reviewing the class diagram in figure 11.7, we see that our Author class has FirstName and LastName properties. That means that we’ll need to map the full author name within our XML to two properties on our object. To make our lives easy, we’ll take a naïve approach and assume that the author element in the XML contains the first and last names of the author separated by a space. Once we have all these details out of the way, we can construct our query expression for retrieving our authors, as in listing 11.20.
Listing 11.20. Query our XML for the authors of the book
var bookAuthors =
from authorElement in amazonItem.Elements(ns + "Author")
join a in ctx.Authors on
(string)authorElement equals a.FirstName + " " + a.LastName into authors
let existingAuthor = authors.SingleOrDefault()
let nameParts = ((string)authorElement).Split(' ')
select new BookAuthor {
Author = existingAuthor ??
new Author {
ID = Guid.Empty,
FirstName = nameParts[0],
LastName = nameParts[1]
}
};
We again use a grouping join and the DefaultIfEmpty operator to create a left outer join between our <Author> element and the Author table. We don’t have a unique key to join on in this case, so we do the best we can by joining the full author name contained in the <Author> element to the first and last name in the Author table. After creating the query expression to retrieve the authors, we need to encapsulate it inside a method that we can call from our main query expression. Additionally, we need to use the results of the authors query expression to create an EntitySet<BookAuthor>, which we’ll assign to the BookAuthors property on our book instance. After a bit of work, we end up with the GetAuthors method shown in listing 11.21.
Listing 11.21. Retrieving the authors from the XML and converting them into an EntitySet
private EntitySet<BookAuthor> GetAuthors(IEnumerable<XElement>
authorElements) {
LinqInActionDataContext ctx = new LinqInActionDataContext();
var bookAuthors =
from authorElement in authorElements
join a in ctx.Authors on
(string)author equals a.FirstName + " " + a.LastName into authors
from existingAuthor in authors.DefaultIfEmpty()
let nameParts = ((string)authorElement).Split(' ')
select new BookAuthor {
Author = existingAuthor ?? new Author {
ID = Guid.Empty,
FirstName = nameParts[0],
LastName = nameParts[1]
}
};
EntitySet<BookAuthor>set = new EntitySet<BookAuthor>();
set.AddRange(bookAuthors);
return set;
}
With the GetAuthors() method in place, we can update our query expression to incorporate the authors into our query:
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "ISBN")
equals selectedBook.Isbn
join p in ctx.Publishers on
(string)amazonItem.Element(ns + "ItemAttributes")
.Element(ns + "Publisher")
equals p.Name into publishers
from existingPublisher in publishers.DefaultIfEmpty()
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
Publisher = (existingPublisher ??
new Publisher {
ID = Guid.Empty,
Name = (string)attributes.Element(ns + "Publisher")
}),
Subject = (Subject)categoryComboBox.SelectedItem,
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice")),
BookAuthors = GetAuthors(attributes.Elements(ns + "Author"))
};
We’ve now completed the process of writing a query expression to create a list of books that should be imported from the XML returned by Amazon.
Thankfully, LINQ to SQL handles the majority of the work once we have our objects created. We do have a small preparatory step that we need to take before we can let LINQ to SQL run with our list of books and save them to the database. In order for our books to get saved, we need to tell the DataContext to add the books to the database. Telling the DataContext to add our books is simply a matter of calling ctx.Books.InsertAllOnSubmit(booksToImport).[2] Once we have all our books added, we call SubmitChanges().
2 The InsertOnSubmit method was named Add in Beta1 and Beta2 of LINQ to SQL.
ctx.Books.InsertAllOnSubmit(booksToImport);
try {
ctx.SubmitChanges();
MessageBox.Show(booksToImport.Count() + " books imported.");
} catch(Exception ex) {
MessageBox.Show("An error occurred while attempting to import the selected books. " + Environment.NewLine + Environment.NewLine +
ex.Message);
}
With everything now in place, we can run our application and begin to import books into our LinqBooks database from Amazon.com. The complete code listing is shown in listing 11.22.
Listing 11.22. Full code for importing books from Amazon.com
using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using System.Xml.Linq;
using Chapter11.Common;
using LinqInAction.LinqToSql;
namespace Chapter11.WinForms {
public partial class ImportForm : Form
{
LinqInActionDataContext ctx;
XNamespace ns =
"http://webservices.amazon.com/AWSECommerceService/2005-10-05";
XElement amazonXml;
public ImportForm()
{
InitializeComponent();
this.Load += new EventHandler(ImportForm_Load);
ctx = new LinqInActionDataContext();
}
void ImportForm_Load(object sender, EventArgs e)
{
subjectComboBox.DataSource = ctx.Subjects.ToList();
}
void searchButton_Click(object sender, EventArgs e)
{
string requestUrl =
String.Format(Amazon.AmazonSearchRestUrl, keywords.Text);
amazonXml = XElement.Load(requestUrl);
var books =
from result in amazonXml.Descendants(ns + "Item")
let attributes = result.Element(ns + "ItemAttributes")
select new Book
{
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
};
bookBindingSource.DataSource = books;
var rows =
from row in bookDataGridView.Rows.OfType<DataGridViewRow>()
let dataBoundBook = ((Book)row.DataBoundItem)
join book in ctx.Books
on dataBoundBook.Isbn equals book.Isbn.Trim()
select row;
foreach (DataGridViewRow row in rows)
{
row.DefaultCellStyle.BackColor = Color.LightGray;
row.Cells[0].ReadOnly = true;
row.Cells[1].Value =
"** Already Exists ** - " + row.Cells[1].Value;
}
}
void importButton_Click(object sender, EventArgs e)
{
var selectedBooks =
from row in bookDataGridView.Rows.OfType<DataGridViewRow>()
where ((bool)row.Cells[0].EditedFormattedValue) == true
select (Book)row.DataBoundItem;
using (var newContext = new LinqInActionDataContext())
{
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks
on (string)amazonItem
.Element(ns + "ItemAttributes")
.Element(ns + "ISBN")
equals selectedBook.Isbn
join p in newContext.Publishers
on ((string)amazonItem
.Element(ns + "ItemAttributes")
.Element(ns + "Publisher")).ToUpperInvariant()
equals p.Name.Trim().ToUpperInvariant()
into publishers
from existingPublisher in publishers.DefaultIfEmpty()
let attributes =
amazonItem.Element(ns + "ItemAttributes")
select new Book
{
ID = Guid.NewGuid(),
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
Publisher = (existingPublisher ??
new Publisher
{
ID = Guid.NewGuid(),
Name = (string)attributes
.Element(ns + "Publisher")
}
),
Subject = (Subject)subjectComboBox.SelectedItem,
PubDate =
(DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice")),
BookAuthors =
GetAuthors(attributes.Elements(ns + "Author"))
};
newContext.Subjects.Attach((Subject) subjectComboBox.SelectedItem);
newContext.Books.InsertAllOnSubmit(booksToImport);
try
{
newContext.SubmitChanges();
MessageBox.Show(booksToImport.Count() + " books imported.");
}
catch (Exception ex)
{
MessageBox.Show(
"An error occurred while attempting to import the
selected books."
+ Environment.NewLine + Environment.NewLine + ex.Message);
}
}
}
private EntitySet<BookAuthor> GetAuthors(IEnumerable<XElement>
authorElements)
{
var bookAuthors =
from authorElement in authorElements
join author in ctx.Authors
on (string)authorElement
equals author.FirstName + " " + author.LastName
into authors
from existingAuthor in authors.DefaultIfEmpty()
let nameParts = ((string)authorElement).Split(' ')
select new BookAuthor
{
ID = Guid.NewGuid(),
Author = existingAuthor ??
new Author
{
ID = Guid.NewGuid(),
FirstName = nameParts[0],
LastName = nameParts[1]
}
};
EntitySet<BookAuthor> set = new EntitySet<BookAuthor>();
set.AddRange(bookAuthors);
return set;
}
private decimal ParsePrice(XElement priceElement)
{
return Convert.ToDecimal(
((string)priceElement.Element(ns + "FormattedPrice"))
.Replace("$", String.Empty)
);
}
}
}
In this section, we pulled together many of the features available within LINQ and LINQ to XML to create an application that allows users to import books from Amazon.com. The application demonstrates the powerful capabilities available within LINQ for joining together data from in-memory objects, XML, and the database, as well as shows how LINQ to XML and LINQ to SQL can be used together to integrate data from disparate systems.
The past three scenarios have all involved integrating LINQ to XML with a relational database via LINQ to SQL. In our next scenario, we’ll take a step away from the database and look at how we can use LINQ to XML to transform text files into XML. Although text files may not be as glamorous as databases, the LINQ framework and LINQ to XML manage to make programming against text files just as fun!
11.6. Transforming text files into XML
In today’s modern world that’s ruled by XML, you’d think everybody would’ve upgraded their internal systems to the latest and greatest, and done away with their arcane flat files. Unfortunately, many internal systems still rely heavily on text files. Rather than stay in the dark ages, we’d like to upgrade all of our internal systems to speak XML. To that end, let’s see what LINQ to XML offers when it comes to converting text files into XML. As you should be accustomed to by now, we’ll get started by exploring our goal for this scenario.
11.6.1. Goal
In this section, we aim to transform a text file into a hierarchical XML document. As shown in listing 11.23, the text file will contain the following book information: the ISBN, title, author(s), publisher, publication date, and price.
Listing 11.23. CSV of Books
01 0735621632,CLR via C#,Jeffrey Richter,Microsoft Press,02-22-2006,59.99 02 0321127420,Patterns Of Enterprise Application Architecture,Martin Fowler,Addison-Wesley,11-05-2002,54.99 03 0321200683,Enterprise Integration Patterns,Gregor Hohpe,Addison-Wesley,10-10-2003,54.99 04 0321125215,Domain-Driven Design,Eric Evans,Addison-Wesley,08-22-2003,54.99 05 1932394613,Ajax In Action,Dave Crane;Eric Pascarello;Darren James,Manning Publications,10-01-2005,44.95
This text file is the same file that we used in the LINQ to Text Files section of chapter 5. You’ll notice some similarities between the code here and that presented in chapter 5, since not only are we using the same file, but both sections are using functional construction to convert the text file into alternate formats. In this scenario, our goal is to parse the data in the text file and produce a hierarchy of XML, as shown in listing 11.24.
Listing 11.24. The XML output that will be created from the transformation
<?xml version="1.0" encoding="utf-8" ?>
<books>
<book>
<title>CLR via C#</title>
<authors>
<author>
<firstName>Jeffrey</firstName>
<lastName>Richter</lastName>
</author>
</authors>
<publisher>Microsoft Press</publisher>
<publicationDate>02-22-2006</publicationDate>
<price>59.99</price>
<isbn>0735621632</isbn>
</book>
<book>
<title>Patterns Of Enterprise Application Architecture</title>
<authors>
<author>
<firstName>Martin</firstName>
<lastName>Fowler</lastName>
</author>
</authors>
<publisher>Addison-Wesley Professional</publisher>
<publicationDate>11-05-2002</publicationDate>
<price>54.99</price>
<isbn>0321127420</isbn>
</book>
...
</books>
Now that we have an idea of the XML we’ll be producing, let’s look at the technique we’ll use to create the XML.
11.6.2. Implementation
The technique used in this scenario is similar to the previous examples we’ve covered. The XML is constructed in a bottom-up manner using LINQ to XML’s functional construction capabilities, along with a set of query expressions that selects the relevant data out of the individual lines of the CSV file. Once again, we see that LINQ to XML allows us to intertwine results from varying data sources, in this case a flat file, with LINQ to XML functional construction statements to create XML.
In order to create our desired XML, we need to open the text file, split each line in the file into an array, and place each item in the array into the appropriate XML element. Let’s start with opening the file and splitting it into parts.
We leverage the static ReadAllLines method available on the File class to read each line within the text file. Since ReadAllLines returns a string array, we can safely use it in our from clause. To split each line, we use the Split method available on string, as well as the let clause that is available in C#. The let clause allows us to perform the split operation once and refer to the result in subsequent expressions. Once we have our line split apart, we can wrap each item into the appropriate XML element, as in listing 11.25.
Listing 11.25. Read the lines from the text file into XElement objects
var booksXml = new XElement("books",
from line in File.ReadAllLines("books.txt")
let items = line.Split(',')
select new XElement("book",
new XElement("title", items[1]),
new XElement("publisher", items[3]),
new XElement("publicationDate", items[4]),
new XElement("price", items[5]),
new XElement("isbn", items[0])
);
We conveniently left the authors out of the query, since they require extra work. Unlike the other fields in our text file, there can be more than one author specified for a single book. If we go back and review the sample text file, we see that the authors are delimited by a semicolon (;).
Dave Crane;Eric Pascarello;Darren James
As we did with the entire line, we can Split the string of authors into an array, with each author being an individual element in the array. To be sure we get our fill of Split, we use it one final time to break the full author name into first and last name parts. Finally, we place the statements for parsing out the authors into a query and wrap the results of our many splits into the appropriate XML.
...
new XElement("authors",
from authorFullName in items[2].Split(';')
let authorNameParts = authorFullName.Split(' ')
select new XElement("author",
new XElement("firstName", authorNameParts[0]),
new XElement("lastName", authorNameParts[1])
)
)
...
When we add it all together we get the final solution, which can be seen in listing 11.26.
Listing 11.26. Flat file to XML implementation
using System;
using System.Linq;
using System.Xml.Linq;
using System.IO;
namespace Chapter11.FlatFileToXml {
class Program {
static void Main(string[] args) {
XElement xml =
new XElement("books",
from line in File.ReadAllLines("books.txt")
where !line.StartsWith("#")
let items = line.Split(',')
select new XElement("book",
new XElement("title", items[1]),
new XElement("authors",
from authorFullName in items[2].Split(';')
let authorNameParts = authorFullName.Split(' ')
select new XElement("author",
new XElement("firstName", authorNameParts[0]),
new XElement("lastName", authorNameParts[1])
)
),
new XElement("publisher", items[3]),
new XElement("publicationDate", items[4]),
new XElement("price", items[5]),
new XElement("isbn", items[0])
)
);
Console.WriteLine(xml);
}
}
}
We’d be remiss if we didn’t point out that the code presented here isn’t recommended if the file that is being processed is large. In this scenario, we used the File.ReadAllLines method in our LINQ to XML query. When working with large files, and/or when performance is critical, the file should be read using a streaming approach like that presented in section 5.3.1.
As we’ve seen over and over again, LINQ to XML allows us to mix and match data from varying data sources into functional construction statements. The result is a consistent programming API for developers, which makes the way XML is created from other data sources—whether they be relational, object, or a text file—consistent and predictable.
11.7. Summary
In this chapter, we took a whirlwind ride through many of the common scenarios you’ll run into when building applications that use XML. Along the way, we explored many of the powerful capabilities that LINQ to XML offers.
We started by exploring LINQ to XML’s transformation capabilities, by building a set of objects from XML. Since we often work with objects, this scenario provided a good overview of how we can read data from XML into a set of objects. It also showed how similar the code is for transforming XML to alternate data formats when using LINQ to XML.
Next we moved on to look at how to create XML from objects using the XML literals support in Visual Basic. By allowing developers to embed XML directly into VB code, XML literals help reduce the confusion that often arises when creating XML.
In addition to having solid support for transforming XML into objects, LINQ to XML also has strong integration with LINQ to SQL. We explored this integration by creating XML from data within a database as well as by filtering and mixing data from a database with XML data in a single query. We rounded out our exploration of LINQ to XML’s integration with LINQ to SQL by looking at how to read information out of an XML file to update a database.
Finally, we completed our common scenarios by exploring how a flat CSV file could be transformed into XML, once again using LINQ to XML’s powerful transformation capabilities.
In this chapter, we were able to create XML from in-memory objects, a relational database, and flat files. We also transformed our XML into other data sources—such as LINQ to SQL objects—and saw how functional construction solves many of our data transformation scenarios.
In the first four parts of this book, we’ve provided you with detailed coverage of all the major pieces of the LINQ puzzle. In the next chapter, we explore how you can extend LINQ to support additional scenarios that it doesn’t support out of the box.