LINQ to SQL: Querying Data

The first and most obvious application of Microsoft Language Integrated Query (LINQ) is in querying an external relational database. LINQ to SQL is a LINQ component that provides the capability to query a relational Microsoft SQL Server database, offering you an object model based on available entities. In other words, you can define a set of objects that represents a thin abstraction layer over the relational data, and you can query this object model by using LINQ queries that are automatically converted into corresponding SQL queries by the LINQ to SQL engine. LINQ to SQL supports Microsoft SQL Server 2008 through SQL Server 2000 and Microsoft SQL Server Compact 3.5.

Using LINQ to SQL, you can write a simple query such as the following:

var query =
    from    c in Customers
    where   c.Country == "USA"
            && c.State == "WA"
    select  new {c.CustomerID, c.CompanyName, c.City };

This query is converted into a SQL query that is sent to the relational database:

SELECT CustomerID, CompanyName, City
FROM   Customers
WHERE  Country = 'USA'
  AND  Region = 'WA'

At this point, you might have a few questions, such as:

• How can you write a LINQ query using object names that are validated by the compiler?

• When is the SQL query generated from the LINQ query?

• When is the SQL query executed?

To understand the answers to these questions, you need to understand the entity model in LINQ to SQL, and then delve into deferred query evaluation.

Entities in LINQ to SQL

Any external data must be described with appropriate metadata bound to class definitions. Each table must have a corresponding class decorated with particular attributes. That class corresponds to a row of data and describes all columns in terms of data members of the defined type. The type can be a complete or partial description of an existing physical table, view, or stored procedure result. Only the described fields can be used inside a LINQ query for both projection and filtering. Listing 5-1 shows a simple entity definition.

Listing 5-1. Entity definition for LINQ to SQL

using System.Data.Linq.Mapping;
[Table(Name="Customers")]
public class Customer {
    [Column] public string CustomerID;
    [Column] public string CompanyName;
    [Column] public string City;
    [Column(Name="Region")] public string State;
    [Column] public string Country;
}

The Customer type defines the content of a row, and each field or property decorated with Column corresponds to a column in the relational table. The Name parameter can specify a column name that is different from the data member name. (In this example, the State member corresponds to the Region table column.) The Table attribute specifies that the class is an entity representing data from a database table; its Name property specifies a table name that could be different from the entity name. It is common to use the singular form for the class name (which represents a single row) and the plural form for the name of the table (a set of rows).

You need a Customers table to build a LINQ to SQL query over Customers data. The Table<T> generic class is the right way to create such a type:

Table<Customer> Customers = ...;
// ...
var query =
    from    c in Customers
    // ...

The Customers table object has to be instantiated. To do that, you need an instance of the DataContext class, which defines the bridge between the LINQ world and the external relational database. The nearest concept to DataContext that comes to mind is a database connection—in fact, the database connection string or the Connection object is a mandatory parameter for creating a DataContext instance. DataContext exposes a GetTable<T> method that returns a corresponding Table<T> for the specified type:

DataContext db = new DataContext("Database=Northwind");
Table<Customer> Customers = db.GetTable<Customer>();

Listing 5-2 shows the resulting code when you put all the pieces together.

Listing 5-2. Simple LINQ to SQL query

DataContext db = new DataContext( ConnectionString );
Table<Customer> Customers = db.GetTable<Customer>();
var query =
    from    c in Customers
    where   c.Country == "USA"
            && c.State == "WA"
    select  new {c.CustomerID, c.CompanyName, c.City };
foreach( var row in query ) {
    Console.WriteLine( row );
}

The query variable is initialized with a query expression that forms an expression tree. An expression tree maintains a representation of the expression in memory rather than pointing to a method through a delegate. When the foreach loop enumerates data selected by the query, the expression tree is used to generate the corresponding SQL query, using the metadata and information from the entity classes and the referenced DataContext instance.

The data returned from the SQL query accessing row and placed into the foreach loop is then used to fill the projected anonymous type following the select keyword. In this example, the Customer class is never instantiated, and LINQ uses it only to analyze its metadata.

To explore the generated SQL command, you can use the GetCommand method of the DataContext class by accessing the CommandText property of the returned DbCommand, which contains the generated SQL query; for example:

Console.WriteLine( db.GetCommand( query ).CommandText );

A simpler way to examine the generated SQL is to call ToString on a LINQ to SQL query. The overridden ToString method produces the same result as the GetCommand( query ).CommandText statement:

Console.WriteLine( query );

The simple LINQ to SQL query in Listing 5-2 generates the following SQL query:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City]
FROM   [Customers] AS [t0]
WHERE  ([t0].[Country] = @p0) AND ([t0].[Region] = @p1)

To get a trace of all SQL statements that are sent to the database, you can assign a value to the DataContext.Log property, as shown here:

db.Log = Console.Out;

The next section provides more detail on how to generate entity classes for LINQ to SQL.

External Mapping

The mapping between LINQ to SQL entities and database structures has to be described through metadata information. In Listing 5-1, you saw attributes on an entity definition that fulfills this rule. However, you can also use an external XML mapping file to decorate entity classes instead of using attributes. An XML mapping file looks like this:

<Database Name="Northwind">
   <Table Name="Products">
       <Type Name="Product">
           <Column Name="ProductID" Member="ProductID"
                   Storage="_ProductID" DbType="Int NOT NULL IDENTITY"
                   IsPrimaryKey="True" IsDbGenerated="True" />

The Type tag defines the relationship with an entity class, and the Member attribute of the Column tag defines the corresponding member name of the class entity (in case it differs from the column name of the table). By default, Member is not required and if not present, is assumed to be the same as the Name attribute of Column. This XML file usually has a .dbml file name extension.

To load the DBML file, you can use an XmlMappingSource instance, generated by calling its FromXml static method, and then pass that instance to the DataContext derived class constructor. The following example shows how to use such syntax:

string path = "Northwind.dbml";
XmlMappingSource prodMapping =
        XmlMappingSource.FromXml(File.ReadAllText(path));
Northwind db = new Northwind(
        "Database=Test_Northwind;Trusted_Connection=yes",
        prodMapping
    );

One use of this technique is in a scenario in which different databases must be mapped to a specific data model. Differences in databases might include table and field names (for example, localized versions of the database). In general, consider this option when you need to realize a light decoupling of mapping between entity classes and the physical data structure of the database.