A table mapping has two basic elements. The TableAttribute maps the class to a table in a database, and you need the ColumnAttributes to map each property to a column in the database.

To map the class to a Table, you use the TableAttribute with the Named argument to associate a class with a table. To begin, add a reference to System.Data.Linq and an imports statement for System.Data.Linq and System.Data.Linq.Mapping. For example, to map a class to an Orders table, you can write:

<Table(Name:="Orders")> _
Public Class Order
End Class

To map the columns of the Northwind.Orders table (or any table) to properties or fields in the Order class, tag each element with the ColumnAttribute. Listing 1 shows both the Orders and Order Details table of Northwind mapped to the classes Order and OrderDetail.

Listing 1: Mapping classes to tables with LINQ to SQL.

<Table(Name:="Orders")> _
Public Class Order
   <Column()> _
   Public OrderID As Integer
   <Column()> _
   Public CustomerID As String
   <Column()> _
   Public EmployeeID As Integer
   <Column()> _
   Public OrderDate As DateTime
   <Column()> _
   Public ShipCity As String
End Class


<Table(Name:="Order Details")> _
Public Class OrderDetail
   <Column()> _
   Public OrderID As Integer
   <Column()> _
   Public ProductID As Integer
   <Column()> _
   Public UnitPrice As Decimal
   <Column()> _
   Public Quantity As Int16
   <Column()> _
   Public Discount As Single
End Class

You can add named arguments to the Column attributes to specify a Name, DbType, Storage, and information such as whether the value in the database might send you a null. Name is used to indicate the column name. If yu don't use the Name argument, LINQ to SQL then assumes the property or field name maps to the Column name as defined. The Storage named argument is used to define where the value is stored in the class. For example, if you define a Property OrderID, you could have a field FOrderID and add Storage:="FOrderID" to the ColumnAttribute's list of arguments. Because you are using fields in the example, Storage is not needed. If you specify the type, you want to match the DbType to an actual database type, such as DbType:="NChar(5)", for example, for the CustomerID column. LINQ will figure it out if you leave this argument out too.

Constructing the DataContext

To get data for the ORM class, you use a DataContext. The DataContext is initialized with a connection string to the database, and you request the data from DataContext.GetTable. Because, ostensibly, you could have any variation of table, GetTable returns an instance of Table(Of T) where T is one of your entity classes.

The elided code below demonstrates how to construct the DataContext, define the generic Table(Of T) class, and request the data. (You will have to change the connection string for your copy and location of the database.)

Module Module1

   Public connectionString As String = _
      "Data Source=.\SQLEXPRESS;AttachDbFilename=" + _
      "C:\Books\Addison Wesley\LINQ\Northwind\northwnd.mdf;" + _
      "Integrated Security=True;Connect Timeout=30;
         User Instance=True"

   Sub Main()
      ' Use LINQ to SQL to get the data - context represents
      ' the database
      Dim orderContext   As DataContext =
         New DataContext(connectionString)
      Dim detailsContext As DataContext =
         New DataContext(connectionString)

' generic table does the ORM association
   Dim orders As Table(Of Order) =
      orderContext.GetTable(Of Order)()
   Dim details As Table(Of OrderDetail) =
      orderContext.GetTable(Of OrderDetail)()
...

At this point, you can treat orders and tables like any queryable sequence. The reason for this is that LINQ can query anything that implements IEnumerable and IQueryable. Table(Of T) implements both interfaces.

Tip: DataContext has a Log property. If you assign a System.IO.TextWriter to the DataContext.Log, LINQ will send information about the query transformations it is defining to convert the LINQ query to SQL. Console.Out is a TextWriter.