In this section, you’ll perform LINQ to Entities queries using the LINQ query syntax that was introduced in Chapter 9. In particular, you’ll learn how to obtain query results that combine data from multiple tables (Figs. 22.27–22.29). The Joining Tables with LINQ app uses LINQ to Entities to combine and organize data from multiple tables, and shows the results of queries that perform the following tasks:
Get a list of all the authors and the ISBNs of the books they’ve authored, sorted by last name, then first name (Fig. 22.27).
Get a list of all the authors and the titles of the books they’ve authored, sorted by last name, then first name; for each author sort the titles alphabetically (Fig. 22.28).
Get a list of all the book titles grouped by author, sorted by last name, then first name; for a given author sort the titles alphabetically (Fig. 22.29).
For this example (Fig. 22.30–Fig. 22.33), perform the steps in Section 22.5.2 to create a new Windows Forms Application project named JoinQueries
in the same solution as the previous examples. Rename the Form1.cs
source file to JoiningTableData.cs
. Set the Form
’s Text property to Joining Tables with LINQ
. Be sure to add references to the Books-Examples
and EntityFramework
libraries, add the connection string to the project’s App.Config
file and set the JoinQueries
project as the startup project. We set the following properties for the outputTextBox
:
Font
property: Set to Lucida Console
to display the output in a fixed-width font.
Multiline
property: Set to True
so that multiple lines of text can be displayed.
Anchor
property: Set to Top, Bottom, Left, Right
so that you can resize the window and the outputTextBox
will resize accordingly.
Scrollbars
property: Set to Vertical
, so that you can scroll through the output.
DbContext
The code uses the entity data model classes to combine data from the tables in the Books
database and display the relationships between the authors and books in three different ways. We split the code for class JoiningTableData
into several figures (Figs. 22.30–22.33) for presentation purposes. As in previous examples, the DbContext
object (Fig. 22.30, line 19) allows the program to interact with the database.
The first query (Fig. 22.31, lines 22–26) joins data from two tables and returns a list of author names and the ISBNs representing the books they’ve written, sorted by LastName
, then FirstName
. The query takes advantage of the properties in the entity data model classes that were created based on foreign-key relationships between the database’s tables. These properties enable you to easily combine data from related rows in multiple tables.
The first from
clause (line 23) gets each author
from the Authors
table. The second from
clause (line 24) uses the Author
class’s Titles
property to get the ISBNs for the current author
. The entity data model uses the foreign-key information stored in the data-base’s AuthorISBN
table to get the appropriate ISBNs. The combined result of the two from
clauses is a collection of all the authors and the ISBNs of the books they’ve authored. The two from
clauses introduce two range variables into the scope of this query—other clauses can access both range variables to combine data from multiple tables. Line 25 orders the results by the author
’s LastName
, then FirstName
. Line 26 creates a new anonymous type containing an author
’s FirstName
and LastName
from the Authors
table and the ISBN
for one of that author’s book
s from the Titles
table.
Recall from Section 9.3.5 that a LINQ query’s select
clause can create an anonymous type with the properties specified in the initializer list— in this case, FirstName
, LastName
and ISBN
(line 26). Note that all properties of an anonymous type are public
and read-only. Because the type has no name, you must use implicitly typed local variables to store references to objects of anonymous types (e.g., line 31). Also, in addition to the ToString
method in an anonymous type, the compiler provides an Equals
method, which compares the properties of the anonymous object that calls the method and the anonymous object that it receives as an argument.
The second query (Fig. 22.32, lines 38–42) gives similar output, but uses the foreign-key relationships to get the title of each book that an author wrote.
The first from
clause (line 39) gets each book
from the Titles
table. The second from
clause (line 40) uses the generated Authors
property of the Title
class to get only the authors for the current book
. The entity data model uses the foreign-key information stored in the database’s AuthorISBN
table to get the appropriate authors. The author
objects give us access to the names of the current book’s authors. The select
clause (line 42) uses the author
and book
range variables to get the FirstName
and LastName
of each author from the Authors
table and the title of one of the author’s books from the Titles
table.
Most queries return results with data arranged in a relational-style table of rows and columns. The last query (Fig. 22.33, lines 55–62) returns hierarchical results. Each element in the results contains the name of an Author
and a list of Title
s that the author wrote. The LINQ query does this by using a nested query in the select
clause. The outer query iterates over the authors in the database. The inner query takes a specific author and retrieves all titles that the author wrote. The select
clause (lines 58–62) creates an anonymous type with two properties:
The property Name
(line 58) is initialized with a string
that separates the author’s first and last names by a space.
The property Titles
(lines 59–62) is initialized with the result of the nested query, which returns the title of each book written by the current author
.
In this case, we’re providing names for each property in the new anonymous type. When you create an anonymous type, you can specify the name for each property by using the format name = value.
The range variable book
in the nested query iterates over the current author’s books using the Titles
property. The Title1
property of a given book
returns the Title
column from that row of the Titles
table in the database.
The nested foreach
statements (lines 67–77) use the anonymous type’s properties to output the hierarchical results. The outer loop displays the author’s name and the inner loop displays the titles of all the books written by that author.