LINQ enables developers to write code in either C# or
VB.NET using the same set of syntactic conventions to query object
collections (known as LINQ to Objects), XML documents (known as LINQ to XML), SQL Server data (known as LINQ to SQL), and other queryable resources. The focus in the following sections is exclusively on LINQ to SQL.
A LINQ to SQL statement is translated into an in-memory data structure known as an expression tree.
An expression tree is an abstract representation of executable code.
These trees provide a means for translating LINQ expressions into
something else—in the case of LINQ to SQL, T-SQL statements. Once
translated into T-SQL, the code is executed on SQL Server (not in .NET).
This point is important because it highlights the fact that LINQ is
independent from its various data providers.
Another important point is that
the T-SQL produced by LINQ is not executed until the moment in your
.NET code when your LINQ to SQL query is first utilized, not where it is
first defined. You may, for example, declare a LINQ to SQL statement
near the top of your .NET method but then only actually use it in one
branch of an if-else block. This provides enormous efficiency.
We should also mention
that a certain amount of trust is involved in using LINQ to SQL because
it requires you to accept the fact that Microsoft’s LINQ team has made
most of the right decisions in doing this translation work (and it has).
This does not mean that, as a .NET developer, you no longer need to
learn T-SQL; on the contrary, there is much more to T-SQL that LINQ
simply doesn’t do, and you will run into its limitations if you use it a
lot. It does mean, however, that you can save an immense amount of time
in developing data-driven applications because you no longer have to do
the repetitive work of writing T-SQL stored procedures for every simple
create, retrieve, update, and delete (CRUD) query.
Getting Started
To start with LINQ to SQL, launch Visual Studio 2008, then right-click
your sample project in Solution Explorer, and choose Add New Item. Click
on the Data folder on the left side of the ensuing dialog and then select the LINQ to SQL Classes item on the right. Name your new file AdventureWorks2008 and click OK (illustrated in Figure 1).
After your new .dbml
file is created in your project, Visual Studio’s Object/Relational
(O/R) Designer opens up with its blank surface ready. On this surface,
you add C# data classes that mirror the tables and relationships in your
database. This is the heart of what is known as object/relational mapping
(ORM): each class represents a database table. Each instance of that
class represents a row in that table. Each property on each instance
represents either a column and its value, or a collection of related
(via ORM) objects. Changes to these object instances (including updates,
insertions, and deletions) once committed, are reflected as changes to
the underlying rows in your tables. LINQ to SQL operates on these ORM
objects, which are defined in the code of your .dbml file.
Using Visual Studio’s
View menu, open Server Explorer. Right-click its root node and select
Add Connection. Fill out the form to set up a connection to your AdventureWorks2008 database. When done, navigate to that new node in Server Explorer (it is named [ServerName]\[InstanceName].AdventureWorks2008.[SchemaName]). (This new node should have a tiny image of a plug on its icon.) Expand this node and then expand the Tables node to view the tables in your database.
Shift-click the following
table nodes in Server Explorer and then drag them all to your O/R
Designer surface and release the mouse button (answer Yes to the ensuing
warning dialog):
Product (Production)
ProductModel (Production)
ProductInventory (Production)
ProductReview (Production)
Your O/R Designer surface should now look something like the one in Figure 2.
The best way to introduce any new technology is with sample code, so let’s jump right in and write a LINQ to SQL query.
Right-click your Windows Forms project in Solution Explorer and then click Add Class. Name your new class LINQExamples.cs and then add the code from Listing 45.2 into its body.
Listing 1. Your First LINQ to SQL Query
public List<Product> GetProductsById(int ProductId)
{
AdventureWorks2008DataContext Context =
new AdventureWorks2008DataContext();
var query =
from p in Context.Products
where p.ProductID == ProductId
select p;
return query.ToList();
}
|
Going Deeper
LINQ requires an understanding of generics
(introduced in .NET 2.0), which provide a means of working with classes
in a type-independent manner. That is, a generic class provides methods
and properties just like any other class; however, it also has a type
parameter that enables users of that class to supply a type at runtime
that the algorithms in the class will then operate on. In Listing 45.2, for example, the return type of GetProductsById uses the generic Framework class System.Collections.Generic.List<T>, substituting Product as a type parameter for T.
When working with LINQ to SQL, you also use the new var keyword, which indicates that the named variable is implicitly typed.
This means that the compiler will infer the type of the statement on
the right by walking down the expression at compile time. In many cases,
your LINQ statements end up being implicitly typed as System.Linq.IQueryable<T>.
This generic class stores the abstract expression tree that will be
translated (at execution time) to T-SQL, information about the query
provider (in this case SQL Server), as well as the enumerable collection
itself that provides access to the data the query returns.
IQueryable<T> itself derives from System.Collections.Generic.IEnumerable<T>. One reason for this is that, under the hood, the compiler converts the query syntax used in Listing 1 to actually use generic query operators defined as extension methods to IEnumerable<T>, including Select, Where, OrderBy, Distinct, and so on. (Extension methods provide a means of adding methods to classes that are otherwise sealed, that is, noninheritable). This means that the query in Listing 45.2 could also have been written as
var query = Context.Products.Where(p => p.ProductID == ProductId);
Because of their common return type (IEnumerable<T>), LINQ’s extension methods may be chained together. For example:
var query =
Context.Products.Where(p => p.ProductID == ProductId).OrderBy(p =>
p.ProductID).Distinct();
The parameter to the Where and OrderBy methods shown here is built on another new construct called a lambda expression, which is an anonymous function that can be cast to a delegate (such as System.Func<T, TResult>, the delegate type required by the input parameter to the extension method Where). Lambda expressions take the form input parameters => expression, where the lambda operator =>
is read as “goes to.” (For more information, refer to the MSDN article
titled “Lambda Expressions.”) Put simply, these expressions provide a
compact syntax for writing anonymous functions that you will use more
often as you progress deeper in your knowledge of LINQ. For now, let’s
get back to the code in Listing 1.
First, in the method signature, you can see that GetProductsById takes an integral ProductId as input and returns a generic List of Product objects. What is a Product object? It’s a LINQ to SQL class instance that points (is object/relationally mapped) to a particular row in the Production.Product table.
You can think of a Product
object as an “objectified” SQL Server row, insofar as its primitively
typed properties are akin to SQL Server column values. It also has
collection-backed properties that point to rows stored in the tables to
which it is related. These specialized LINQ collections are of type System.Data.Linq.EntitySet. Let’s examine the class definition of Product to see how this plays out.
Using Solution Explorer, expand the AdventureWorks2008.dbml file to reveal AdventureWorks2008.designer.cs.
This code file contains all the ORM logic needed to use LINQ to SQL
with your database. Double-click this file and, using the drop-down at
the top left of your code editor window, select [YourProjectName].Product.
Above your class’s name, notice the Table attribute (attributes are .NET classes used to decorate other classes with information queryable at runtime). Its NameProduction.Product.
As you scroll down, examine this class a bit more. Notice how its
primitive properties map nicely to the columns of your table. Notice
also how each property has its own Column attribute, signifying the specific SQL Server column to which it is mapped. property reveals that the class it decorates is to be mapped to rows in
As we mentioned earlier, the Product class also has collection-backed properties, such as ProductInventories and ProductReviews. These properties represent the tables related (via primary and foreign keys) to Product. By using properties in your .NET code, you can navigate from a parent object in Product to a child object in ProductInventories, just like you would when writing T-SQL joins. Not surprisingly, each Association attribute found on your collection-backed properties denotes the direction of the navigational path from parent to child.
The glue that holds all this together is the System.Linq.DataContext class, represented in Listing 45.2 by your Context object (which is of type AdventureWorks2008DataContext). Put simply, DataContext is the ORM bridge or conduit from .NET to SQL Server and back.
At the top of your designer file, you can see that the DBML class inherits from System.Data.Linq.DataContext. Notice its mapping attribute, Database, which indicates that it is mapped to AdventureWorks2008. As the SQL Server conduit, you use the DataContext
instance to select your object-mapped rows. Through it, you commit
inserts, updates, and deletes to the underlying tables, by adding,
changing properties of, and removing objects from its System.Data.Linq.Table
collection’s properties. These collections represent the tables you
added to your O/R Designer’s surface. And this is the real power of LINQ
to SQL: no longer is it necessary to waste hours writing boilerplate
T-SQL to perform simple database operations; you can get it all done
with pure .NET code.
Let’s look at a slightly
more complex LINQ query that leverages the power of key-based
relationships to select related database objects. In Listing 2, you add a new method to the sample class that gets a List of ProductReview objects for a given Product.
Listing 2. Using Database Relationships to Select Related Objects with LINQ to SQL
public List<ProductReview>
GetProductReviewsByProduct(Product MyProduct)
{
AdventureWorks2008DataContext Context =
new AdventureWorks2008DataContext();
var query =
from p in Context.Products
join r in Context.ProductReviews
on p.ProductID equals r.ProductID
where p.ProductID == MyProduct.ProductId
select r;
return query.ToList();
}
|
Notice the join syntax introduced in this example. As you can see, it’s a lot like T-SQL’s INNER JOIN, and it performs the same basic function. The where clause in the example is also just like T-SQL’s WHERE, except for the fact that you have to use C#’s == operator instead of T-SQL’s =). The one big difference to pay attention to is that with LINQ your select statement comes last, and your from clause comes first. Remember that all the tables you want to select from are properties (System.Data.Linq.Table objects) of the DataContext object. Very simple, very powerful.
