Programming with LINQ to SQL Greensboro NC

provided by: 
Originally published at Internet.com

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: _ 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. _ Public Class Order _ Public OrderID As Integer _ Public CustomerID As String _ Public EmployeeID As Integer _ Public OrderDate As DateTime _ Public ShipCity As String End Class _ Public Class OrderDetail _ Public OrderID As Integer _ Public ProductID As Integer _ Public UnitPrice As Decimal _ Public Quantity As Int16 _ 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.

Query SQL Server with LINQ to SQL

Now, all you have to do to query orders or details is to write some queries. Listing 2 is the complete example. This listing uses the ORMs defined in Listing 1, the DataContext, and a LINQ query to display all of the ProductID's from the [Order Details] table to the console.

Listing 2: The complete listing including the TableAttributes, ColumnAttributes, ORM definition, the DataContexts, and a LINQ query to select the data. Imports System.Data.Linq Imports System.Data.Linq.Mapping Imports System.IO 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)() Dim allDetails = From detail In details _ Select detail For Each d In allDetails Console.WriteLine(d.ProductID) Next Console.ReadLine() End Sub End Module _ Public Class Order _ Public OrderID AsInteger _ Public CustomerID As String _ Public EmployeeID As Integer _ Public OrderDate As DateTime _ Public ShipCity As String End Class _ Public Class OrderDetail _ Public OrderID As Integer _ Public ProductID As Integer _ Public UnitPrice As Decimal _ Public Quantity As Int16 _ Public Discount As Single End Class

I hope you see that this is quite a bit easier than using connections and DataReaders to populate entity classes.

LINQ to SQL is completely compatible with ADO.NET 2.0 so you can mix in LINQ with your existing ADO.NET code and blend both styles—LINQ and plain vanilla ADO.NET. And, of course, you can still use straight ADO.NET with stored procedures if you want.

Summary

LINQ to SQL lightens the load of ADO.NET programming. All you need to do is define entity maps—which you have done before—with the TableAttribute and ColumnAttribute, and use a DataContext to get the data. LINQ writes the SQL. In Part 2 of this article, you'll use this code to define a left join on the Orders and Order Details objects.

LINQ is completely compatible with ADO.NET 2.0, so you can use these new capabilities with or to replace existing code.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C# from Sams due in July 2008. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at pkimmel@softconcepts.com.

If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org. Glugnet opened a users group branch in Flint, Michigan in August 2007. If you are interested in attending, check out the www.glugnet.org web site for updates.

Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.