SQL Server Magazine

Microsoft built the Language-Integrated Query (LINQ) to SQL component in .NET Framework on top of LINQ to achieve one main purpose: Extend the general-purpose query syntax of LINQ to the world of SQL Server databases. As you may know, accessing in-memory data collections, databases, and XML documents today requires a familiarity with a variety of languages and syntax—one for each technology. With LINQ, you can use the same syntax to query different types of data.

The LINQ query engine is extensible enough to support any collection of objects that exposes a made-to-measure interface—the IQueryable interface. This means that any team of developers could, in theory, expose to LINQ their own data and query it through the common syntax—which is what Microsoft did with the LINQ to SQL and LINQ to XML interfaces. LINQ to SQL, in particular, wraps the content of a SQL Server database and makes it queryable through the LINQ syntax. We'll explore LINQ to SQL in depth here, examining the T-SQL basis for LINQ to SQL queries and walking through examples of using LINQ to SQL to code data-access functionality in .NET applications.

LINQ to SQL and T-SQL
LINQ to SQL doesn't work with databases other than SQL Server. So, unlike ADO.NET or industry-standard object-relational mapping tools, you can't use LINQ to SQL to work with, say, Oracle databases. (You can instead load content stored in Oracle databases through DataSets and query over that using the LINQ to DataSet model.) LINQ to SQL still uses T-SQL under the hood to run its queries. LINQ to SQL doesn't attempt to push an alternate route and doesn't aim to replace T-SQL; it simply offers a higher-level set of query tools for developers to leverage. LINQ to SQL is essentially a more modern tool to generate T-SQL dynamically based on the current configuration of some business-specific objects. T-SQL operates on a set of tables, whereas LINQ to SQL operates on an object model created after the original set of tables.

LINQ to SQL opens up SQL Server querying to the larger universe of .NET developers. However, with LINQ to SQL as well as with raw T-SQL, only T-SQL and SQL Server database experts can fine-tune the queries and remove any bottlenecks and performance hits.

Preparing for LINQ to SQL
LINQ to SQL works by exposing an object-based query language to developers and producing T-SQL statements in response to their input. With LINQ to SQL, you don't explicitly use a connection string, nor do you open or close a connection explicitly. All you do is interact with a central console called the data context.

The data context is a class that's ordinarily created by the Microsoft Visual Studio 2008 wizard when you choose to add a new LINQ to SQL class item to the current project, as Figure 1 shows. Adding a LINQ to SQL class to a project displays the Object Relational Designer, through which the developer selects tables, stored procedures, and functions to add to the LINQ to SQL data context.

The completed data context class incorporates references to collections that represent all selected tables. The data context source file also includes the definition of a class that represents an individual row on each selected table. In other words, if you select the Customers and Orders tables from the Northwind database, you'll have in the data context class properties, which the C# code sample in Listing 1 shows.

You'll work against these collections using the LINQ syntax. Any query you perform against the collections will then be translated to T-SQL statements and executed against the database. The data context class takes care of all this for you.

The data context—usually a class derived from the base class DataContext—has a Log property, as the following example shows, which you use to register a stream to capture all T-SQL statements actually generated for each LINQ query to the database.

NorthwindDataContext nwind = new NorthwindDataContext();
nwind.Log = Console.Out;

Through the Log property, you can track what really happens under the hood—that is, snoop on generated T-SQL statements—and decide to intervene with changes if you think the T-SQL code could be improved.

Running a Query through LINQ
The LINQ query language works on the LINQ to SQL object model in much the same way as T-SQL works on table rows. To express a simple query that selects customers from a given country and returns them ordered by name, in LINQ you use the following query:

var data = from c in nwind.Customers
           where c.Country == "Spain"
           orderby c.CompanyName
           select c;

As you can see, in this query there's no explicit reference to T-SQL; furthermore, the only point of contact between LINQ and the outside world is the data collection you work with. That collection is responsible for taking the input data (filter, order) and selecting the results. Being a LINQ to SQL collection, it will do so by running a dynamically generated T-SQL query. As a result, LINQ to SQL offers a platform for you to express entities in the domain space as objects. You can then query entities and update them, using an object-oriented syntax.