Introduction
The concept of object relational mapping (ORM) is not new. An ORM is a class (called an entity in this context) that has properties or fields that map to database columns. In this article, I will show you how easy it is to use LINQ to SQL to define an ORM, mapping a class to a database table, and then query that table with LINQ using a DataContext. A DataContext plays the role of the connection here.
Mapping Classes to Tables
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.)
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.
This LINQ to SQL mechanism works because LINQ builds expression trees and queryable providers can transmogrify expression trees to anything else, like SQL. (For an example of queryable providers, check out my book LINQ Unleashed: for C# (due in July 2008) or Bart De Smet's blog.
IE 7
Firefox 2.0
