Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 782
Part V Data Connectivity
(4) WHERE
(5) GROUP BY
(6) WITH
(7) HAVING
(10) ORDER BY
Notice that the FROM clause is processed first, while the SELECT clause is processed almost last. Any
clause that is not specified in the query is simply skipped by the query processing engine. This informa-
tion is important because it points out the similarities between LINQ query syntax and how SQL Server
processes a query.
For more information on SQL logical query flow, please see Chapter 8, ‘‘Introducing Basic
Query Flow.’’
In a LINQ query, the first clause is the FROM clause, which specifies the source of the data, and it must
end with either a
SELECT clause or a GROUP clause. The first clause, called a generator, defines where
the data will be coming from when the query is executed. It also specifies the range variable that is used
as a reference for each element in the data source. For example, the following uses LINQ to return a list
of the directories in the root of a hard drive:
DirectoryInfo di = new DirectoryINfo("C:\\");
var query =
from dir in di.GetDirectories()
order by di.Name
select new { dir.Name };
foreach (var item in query)
Console.WriteLine(item.Name);
In this example, the first statement creates a new instance of the DirectoryInfo class. This is what is
used to retrieve a list of the directories.
The second statement is the LINQ statement. The variable
var is the query variable. This variable does
not store actual result data. The results are produced during the iteration of the query, which is done in

the
foreach loop. When the foreach statement executes, the query results are returned during each
iteration in the variable item.
Whereas the LINQ query and the T-SQL query are executed similarly, the T-SQL query syntax is a bit
different. In T-SQL, the query would be executed internally following the steps described earlier. With
LINQ, the query does not need to go through the rewriting process.
In the next example, the LINQ query uses the same syntax to query data from the
Contact table in the
AdventureWorks database in SQL Server:
var query =
from c in Contact
where c.FirstName.StartsWith("S")
orderby c.LastName
select c;
782
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 783
LINQ 34
Notice in this query that the syntax is exactly the same as the first query. An additional WHERE clause
has been added, but syntactically it is the same. This is what is great about LINQ. Similar query expres-
sions can be written against two completely different sources of data.
Query syntax and method syntax
LINQ has the ability to write queries using both query syntax and method syntax. The examples shown
so far have used query syntax, which is writingthequeryasaqueryexpression,asfollows:
IEnumberable<string> query =
from c in Contact
where c.FirstName.StartsWith("S")
select c;
This declarative syntax is easy to read and understand, but developers also have the option of writing
queries using method syntax. When a LINQ query is compiled, the query expression is translated into

method syntax because the .NET CLR (common language runtime) doesn’t understand query syntax.
Thus, at compile time, query expressions are translated into method calls because this is what the CLR
understands.
The following shows the previous query written using method syntax:
IEnumberable<string> query = contact.Where(c =>
c.FirstName.StartsWith("S"));
For clarification, take a look at a couple more examples. This first example adds an additional filter to
the previous example:
IEnumberable<string> query =
from c in Contact
where c.FirstName.StartsWith("S")
&& c.LastName.StartsWith("A")
select c;
Hereisthesamequeryinmethodsyntax:
IEnumberable<string> query = contact.Where(a =>
a.FirstName.StartsWith("S") && a.LastName.StartsWith("A"));
Just to complicate things a bit more, this next example adds an OrderBy clause:
IEnumberable<string> query =
from c in Contact
where c.FirstName.StartsWith("S")
&& c.LastName.StartsWith("A")
order by c.LastName
select c;
783
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 784
Part V Data Connectivity
The preceding query written using method syntax is as follows:
IEnumberable<string> query = contact.Where(c =>
c.FirstName.StartsWith("S") && c.LastName.StartsWith("A"))

.OrderBy(c => c.LastName);
The output of both queries is identical.
Choosing a query syntax
As you have seen, although there are syntactical differences between query syntax and method syntax,
execution is essentially the same. However, the .NET CLR does not understand query syntax, so it is
translated into method syntax anyway. The CLR understands method calls, so query expressions are
translated into method syntax (method calls) and executed as such.
Although there is no semantic difference between method syntax and query syntax, I recommended that
developers use query syntax whenever possible, as it is easier to read, understand, and maintain.
Now that you have a good understanding of LINQ, the standard query operators, and LINQ query
expressions, the rest of this chapter focuses on the LINQ providers and offers detailed examples of each,
beginning with LINQ to SQL.
LINQ to SQL
LINQ to SQL is part of ADO.NET which provides a run-time infrastructure for mapping relational data
as objects. In essence, it is an ORM (object-relational mapping) framework that enables the direct 1-to-1
mapping of a SQL Server database and its objects to .NET classes without losing the ability to query.
One of most important things that developers need to understand when working with LINQ to SQL is
the
DataContext class. The DataContext is the main medium through which objects are retrieved
from the database and changes are resubmitted. The
DataContext is much like an ADO.NET connec-
tion in that it is initialized with a connection string.
The
DataContext has the responsibility of converting objects, which means that because LINQ to SQL
deals with objects, the
DataContext converts the objects into SQL queries, as well as reassembling the
results back into queryable objects.
The
DataContext has several overloads, one of which is just a simple connection string specifying the
connection information, as shown here:

DataContext db = new DataContext("Initial Catalog=AdventureWorks; i
Integrated Security=sspi");
It is recommended, however, that you use strongly typed DataContexts. This is as simple as creating
a new class that inherits from the
DataContext class:
[Database(Name = "AdventureWorks")]
public class AdventureWorks : DataContext
{
public AdventureWorks(string connection) : base(connection) {}
}
784
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 785
LINQ 34
Here, the strongly typed DataContext is given the same name as the database to use — in this case,
the
AdventureWorks (2005 and 2008) database. Therefore, no database needs to be specified in the
connection string.
In the preceding example, the
[Database] attribute is applied to any strongly typed DataContext
declaration. This attribute is optional, but when it is used a Name property must also be used to sup-
ply the name of the database to which you are mapping. This attribute maps the database in the
Name
property to your entity class.
With the
DataContext created, the next step is to create mappings to the specific objects. Database
tables are represented by entity classes in LINQ to SQL. An entity class is a normal class annotated with
a specific tag that maps that class to a specific table in the database.
The
Table attribute, part of the System.Data.Linq.Mapping namespace, maps an entity class to a

database table or view. This attributehasasingleproperty,
Name, which specifies the name of the rela-
tional table or view.
In the following example, the
Table attribute is applied to a class to define a mapping between the
Person.Contact table in the AdventureWorks database and a class named Contact:
[Table(Name="Person.Contact")]
public class Contact
{
//
}
Once the table is mapped to an entity class, table columns must be mapped to class properties. This is
accomplished by applying the
Column attribute, which maps a database table column to a member of
an entity class, as shown here:
[Column(DbType="nvarchar(50) not null")]
public string FirstName
Several properties are commonly applied with this attribute, including the following:
■
Name: The name of the table column
■
DbType: Database type of the column
■
IsPrimaryKey: Specifies that the associated column is the primary key of the corresponding
table
■
IsDbGenerated: Specifies that the associated column auto-generates its values
■
CanBeNull: Specifies that the associated column can contain null values
So far, the connection has been defined via the

DataContext, and the table and column mappings
have been defined. When this is all put together, it looks like the following:
public class AdventureWorks : DataContext
{
public AdventureWorks(string connection) : base(connection) {}
public Table<Contact> Contact;
}
785
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 786
Part V Data Connectivity
[Table(Name="Person.Contact")]
public class Contact
{
[Column(DbType="nvarchar(50) not null")]
public string FirstName
}
To help this sink in, this section offers a couple of examples. The first example applies the mappings
manually, and the second example illustrates an easier way.
Example 1: Manually applying the mappings
For this first example, follow these steps:
1. Create a new Visual Studio C# Windows project.
2. Add a reference to the
System.Data.Linq component.
3. On the form, add a button and a list box.
4. Add a new class to the project and name it
AdventureWorks.
5. Open the new class and replace all the code that is there with the following code. (Because
I named my project LINQ, the namespace is called LINQ. Be sure to change the namespace
name if you named your project something different.)

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using System.Linq.Expressions;
using System.ComponentModel;
using System;
namespace LINQ
{
[Database(Name = "AdventureWorks")]
public class AdventureWorks : DataContext
{
public AdventureWorks(string connection) : base(connection)
{
}
public Table<Contacts> Contact;
}
[Table(Name="Person.Contact")]
public class Contacts
{
[Column(DbType = "int not null", IsDbGenerated = true,
IsPrimaryKey = true)]
786
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 787
LINQ 34
public int ContactID;
[Column(DbType = "nvarchar(8)")]

public string Title;
[Column(DbType = "nvarchar(50)")]
public string FirstName;
[Column(DbType = "nvarchar(50)")]
public string MiddleName;
[Column(DbType = "nvarchar(50)")]
public string LastName;
[Column(DbType = "nvarchar(50)")]
public string EmailAddress;
[Column(DbType = "int")]
public int EmailPromotion;
[Column(DbType = "bit")]
public byte NameStyle;
}
}
This code first defines the DataContext class. The next section of code defines a class that
maps that class to the
Person.Contact table (in SQL Server 2005) as well as maps several
columns from the table to members of the class.
Note some of the properties used in the
Column attribute applied to the ContactID mem-
ber. The
ContactID column in the Person.Contact table is a primary key column that
auto-generates its IDs. Thus, in order for LINQ to support this and recognize this functionality,
the
IsDbGenerated and IsPrimaryKey properties need to be used.
6. In the Click event of the button on the form, add the following code:
AdventureWorks db = new AdventureWorks("Data Source=ServerName;
Initial Catalog=AdventureWorks;Integrated Security=sspi");
var query =

from con in db.Contact
where con.FirstName.StartsWith("S")
select con;
foreach (var cont in query)
listBox1.Items.Add(cont.FirstName);
7. Compile the project to ensure that there are no errors, and then run it. When the form dis-
plays, press the button. The list box should be populated with the first name of all contacts
whose first name begins with the letter S.
Prior to moving on to the next example, let’s take a closer look at this one. The class created earlier
used LINQ to SQL to define the mapping between the relational data and the class. On the form, an
787
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 788
Part V Data Connectivity
instance of the class was created, and a LINQ query was created and used, employing a few of the
standard query operators. The query was then iterated over, at which point the data was returned to the
application.
The thing to pay attention to here is the query itself. As the query was typed, IntelliSense displayed the
column names of the table, as well as a list of the available standard query operators and methods. This
is LINQ in all its glory! Equally, notice that the syntax used to retrieve relational data is exactly the same
as what was used to query the directories on the local drive in the example shown earlier.
This first example demonstrated how to create the mapping classes manually in order to provide a good
understanding of how the mapping works. However, there is an easier way.
Example 2: The easy way
For this second example, follow these steps:
1. In Visual Studio, right-click the project in the Solution Explorer and select Add ➪ New Item.
2. When the Add New Item dialog appears, select the LINQ to SQL Classes item from the list of
templates. Name it
AWDB.dbml and click OK.
A LINQ to SQL designer page will open in the Visual Studio environment. This object-

relational designer provides a visual surface for creating LINQ to SQL entity classes and rela-
tionships based on database objects. In essence, this is where the object model is defined that
maps database objects within an application.
3. The empty design surface represents a
DataContext that is ready to be configured. From
here, open the Server Explorer and, if necessary, create a connection to the AdventureWorks
database. Once that is created, database objects can be dragged onto the designer.
4. For this example, drag the
Person.Contact table onto the designer and then save the
project. Next, modify the code behind the button on the form to look like the following:
AWDBDataContext db = new AWDBDataContext();
var query =
from con in db.Contacts
where con.FirstName.StartsWith("S")
select con;
foreach (var cont in query)
listBox1.Items.Add(cont.FirstName);
The only thing that changes is the first line; everything else remains the same. Run the project
and click the button. The list box will be populated with the same data presented in the first
example.
5. Before moving on, however, a few things need to be highlighted. In the Solution Explorer,
expand the AWDB node. Underneath that node are two files:
AWDB.dbml.layout and
AWDB.designer.cs. The layout file simply contains the layout information for the designer. If
you open this file you will see that it is simply an XML file.
The important file is the
designer.cs file. Open this file and notice that it looks very similar
to the file manually created in the first example. It contains a bit more information than the
one you manually created earlier, but overall it is the same.
788

www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 789
LINQ 34
One of the differences between the two examples is that connection string information needed to be
passed in the first example, whereas no connection information needed to be passed in the second
example. This is because the Server Explorer connection information was automatically saved and
added to the project’s resource file when the OR mapping was created. In the
designer.cs file, the
DataContext class contains the following:
public AWDBDataContext() :
base(global::LINQ.Properties.Settings.Default
.AdventureWorksConnectionString, mappingSource)
{
OnCreated();
}
Right-click on the line AdventureWorksConnectionString and select Go To Definition, which
will take you to where the connection string is defined in the project settings. Notice that the
AWDBDataContext has several overloads, which enables different connection information to be passed
to the
DataContext.
Much more could be said about LINQ to SQL, but this should provide a solid foundation for under-
standing how it works. Your homework assignment for this section is to add more tables to the
designer, look at their relationships, and modify the LINQ query to utilize the relationships (hint: use
the
JOIN operator).
LINQ to SQL and Stored Procedures
A
few words on using LINQ to SQL and stored procedures. I am a firm believer in using stored procedures
for several reasons:
■ They provide a layer of abstraction.

■ They provide increased maintainability.
■ They enable performance improvement through database query optimizations.
The great thing about LINQ is that it supports accessing data by calling and executing stored procedures. For
example, given the following stored procedure:
CREATE PROCEDURE [dbo].[uspGetSalesOrderDetail]
@SalesOrderID [int]
AS
BEGIN
SET NOCOUNT ON;
SELECT SalesOrderDetailID,
OrderQty,
continued
789
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 790
Part V Data Connectivity
continued
ProductID,
UnitPrice,
LineTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderID
END;
Calling a stored procedure is as simple as the following:
var query = db.uspGetSalesOrderDetail(43659)
foreach (SalesOrderDetailResult salesOrderDetail in query)
{
//do something
}
However, while using stored procedures does have its advantages, it is still an individual call as to when and

where stored procedures are appropriate. For example, if I know that a query will always return a single row
(and maybe only few columns), then I may forgo using a stored procedure in that case. If I have a complex
T-SQL query, then I will more than likely use a stored procedure.
My recommendation is to experiment and find what works best given the scenario in your application. Using
stored procedures provides certain benefits, such as those listed earlier, but not all scenarios need stored
procedures; and as you become familiar with LINQ and LINQ to SQL, you will find what works best in each
situation.
LINQ to XML
I love XML. The fact that it is entirely possible to query XML using LINQ just rocks my world. It
is an excellent form of moving and storing data. Those who have used other technologies to query
XML know that it can be somewhat difficult and cumbersome. LINQ to XML is heaven sent. This
section introduces LINQ to XML and demonstrates how easy it is to create and query XML using LINQ
to XML.
The component that gives LINQ to XML its power is the
System.Xml.Linq namespace, and
its corresponding classes. These classes provide the capability to work with XML with ease, leav-
ing behind the need to work with complex and cumbersome technologies such as the DOM and
XQuery.
Table 34-2 describes the 19 classes contained in the
System.Xml.Linq namespace that enable devel-
opers to easily work with XML.
790
www.getcoolebook.com
Nielsen c34.tex V4 - 07/23/2009 1:56pm Page 791
LINQ 34
TABLE 34-2
LINQ to XML Classes
Class Description
XAttribute Represents an XML attribute
XCData Represents a CDATA text node

XComment Represents an XML comment
XContainer An abstract base class representing nodes that have child
nodes
XDeclaration Represents an XML declaration
XDocument Represents an XML document
XDocumentType Represents an XML DTD
XElement Represents an XML element
XName Represents the name of an XML element or attribute
Xnamespace Represents an XML namespace
XNode An abstract class representing nodes of an XML element
tree
XNodeDocumentOrderComparer Provides mechanisms for node comparisons regarding
their order within the XML document
XNodeEqualityComparer Provides mechanisms for node comparisons regarding
their equality value
XObject An abstract class representing XNodes and XAttributes
XObjectChange The event type when an XObject event is raised
XObjectChangeEventArgs Provides information and data for the Changing and
Changed events
XObjectChangeEventHandler The method that handles the XObject’s Changed and
Changing events
XProcessingInstruction Represents an XML processing instruction
XText Represents an XML text node
791
www.getcoolebook.com