Any external data must be described with appropriate metadata bound to class definitions. Each table must have a corresponding class decorated with particular attributes, which 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 small and simple entity definition.

Listing 5-1: Entity definition for LINQ to SQL

[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 of the relational table. The Name parameter can specify a column name that is different from the data member name (in this example, State corresponds to the Region table column). The Table attribute specifies that the class is an entity representing data of a database table; its Name property can specify a table name that is different from the entity name. It is common to use the singular form for the name of the class (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, we need an instance of a 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, a mandatory parameter needed to create a DataContext instance is the connection string or the Connection object. Its GetTable method 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. As we noted in Chapter 2, “C# Language Features,” an expression tree maintains a representation of the expression in memory instead of 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 all metadata and information we have in the entity classes and in 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 sample, the Customer class is never instantiated, and it is used only by LINQ to analyze its metadata.

We can explore the generated SQL query by using the GetQueryText method of the DataContext class:

Console.WriteLine( db.GetQueryText( query ) );

The previous simple LINQ to SQL query generates the following GetQueryText output:

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

An alternative way to get a trace of all SQL statements sent to the database is to assign a value to the Log property of DataContext:

db.Log = Console.Out;

In the next section, you will see in more detail how to generate entity classes for LINQ to SQL.