|
Layer Data Access with LINQ to SQL
Orcas' LINQ to SQL and its Object/Relational Designer enable ADO.NET developers to combine O/R mapping, object persistence, and LINQ queries in an autogenerated data access layer for SQL Server.
by Roger Jennings
June 29, 2007
Technology Toolbox: VB .NET, C#, SQL Server 2005 SP2, Other: Visual Studio "Orcas" Beta 1 or later
Object orientation is firmly established as today's software abstraction model, but it's a good bet that 90 percent or more of current .NET development projects connect to relational databases.
Connecting .NET's "plain old CLR objects" (POCOs) to rows of relational tables generates a programming disconnect that's often called an impedance mismatch. Almost 50 proprietary and open-source add-ins, libraries, and tools offer to bridge the object-relational chasm for .NET developers, but Microsoft won't offer an object/relational mapping (O/RM) tool until it ships Orcas. Redmond's first attempt to deliver an O/RM platform appeared briefly in Whidbey (VS 2005 Beta 1) as ObjectSpaces, but later vanished into the black hole generated by the demise of WinFS. Judging by the ruckus that Whidby participants raised when ObjectSpaces disappeared, data-oriented .NET developers definitely are O/RM enthusiasts.
LINQ to SQL (formerly called DLinq) is Microsoft's first effort at filling the ObjectSpaces void with an O/RM data access layer (DAL) that combines object-persistence management and language-independent query (LINQ) technology. The purpose of a DAL is to isolate the data store from other application layers; the DAL processes create, retrieve, update, and delete (CRUD) operations. Only a few lines of code give you business objects for your presentation layer and LINQ-enhanced querying of in-memory objects or, when necessary, of the underlying data store. LINQ to SQL also handles data identification to prevent duplication of in-memory object instances and data tracking to persist instance changes in the data store's tables.
ADO.NET's traditional SqlCommand and OleDbCommand objects rely on query strings to perform CRUD operations on databases. Query strings have two serious drawbacks: First, they require developers to learn another programming language (SQL), which has several distinct dialects. Second, SQL commands are late-bound, so they don't benefit from compiler syntax checks or support IntelliSense. LINQ overcomes these issues by adding a strongly-typed, SQL-like query syntax to C# 3.0 and VB 9.0. LINQ delivers full IntelliSense, type safety, and static-type checking to queries against in-memory objects (LINQ to Objects), XML documents (LINQ to XML), SQL databases (LINQ to SQL), and data objects defined by Orcas's Entity Framework and Entity Data Model (LINQ to Entities). LINQ brings first-class status to queries by incorporating their keywords in the next version of C# and VB.
Orcas Beta 1 includes an early but operable LINQ to SQL implementation and an updated graphical O/R Designer, which I'll cover in depth in this article. Before embarking on a full-scale evaluation of LINQ to SQL as an O/RM tool, however, you should be aware that LINQ to SQL connects to SQL Server 200x, MSDE 2000, or SQL Server Express only. There's no support planned for SQL Server CE or any other backend database. LINQ to SQL generates classes directly from the database metadata, so mapping is limited to the table per class model. The inheritance model is also restricted; single-table mapping requires the inheritance hierarchy to be stored in one table that has a column designated as the discriminator value to specify subclasses.
Writing the code or authoring the XML files to define O/RM is a daunting process, but LINQ to SQL does most of the work for you. You can create a set of classes from an existing SQL Server database by using the SQLMetal.exe command-line tool or the graphical O/R Designer. Starting a new Windows form or Web site project and adding a Linq [sic] to SQL File object adds a default DataClasses.dbml node with items for a DataClasses.dbml.diagram XML file and DataClasses.designer.vb or cs file. The latter file contains a skeleton DataClassesDataContext partial class with two default constructors. All WinForm projects get a System.Xml.Linq reference for LINQ to XML by default; adding the Linq to SQL File item adds the System.Data.Linq reference to WinForm and Web site projects. The System.Linq namespace for basic LINQ queries is part of the System.Core.dll assembly, so you don't need to add a reference to it.
Populate the DataContext with the Designer
Click on the DataClasses.dbml node to open an empty O/R Designer surface with empty main and Method panes. Then drag tables from the data store's Server or Database Explorer nodes to the main pane to add entity class widgets and dashed lines that specify associations with dependent entities. An association between entity classes represents a bidirectional foreign key relationship in the data store. Dragging the first table to the designer surface adds the default parameterless New constructor to the Data Context and its connection string to the AppName.exe.config or the Web.config file's <connectionStrings> element. By default, LINQ to SQL's de-pluralizer changes plural table names to singular entity class names automatically, but you can edit the name in the entity-class widget. Adding the eight entity classes derived from the tables of the original Northwind sample database takes only a few minutes (Figure 1).
To create a WinForm data source for a parent/child/grandchild form, press F5 to build and compile your project, choose Data, and then Add New Data Source to start the DataSource Configuration Wizard. Then click on Next, select Object in the Choose Data Source Type dialog, click on Next, expand the ProjectName node, and select the topmost member of the databinding hierarchy—Customer, for this example. Finally, click on Finish. The complete entity hierarchy represented by the Customer entity and its chain of associations appears as an object graph in the DataSources window (Figure 2). The ASP.NET team promises a LINQDataSource control for Web projects in the Orcas Beta 2 timeframe.
You can tweak table, column, and association properties in the designer, but the default settings are usually satisfactory for initial testing with dynamically generated T-SQL statements for queries and updates. If your tables contain a timestamp (rowversion) column, the DataContext class uses it for optimistic concurrency conflict detection. Adding a timestamp column sets all other columns' UpdateCheck property value to Never, which eliminates the need to compare original values (other than the timestamp value) with current database values for updates and deletions.
DataContext is the top-level object in the LINQ to SQL hierarchy; it manages the database's SqlClient connection and maintains a System.Data.Linq.Table(Of T) entity set for each data store table (Listing 1). DataContext also tracks all in-memory entities (unless you explicitly disable entity tracking), handles data store table updates, and enables resolving optimistic concurrency conflicts. The Table(Of T) class implements the IEnumerable(Of T) and LINQ-specific IQueryable(Of T) interfaces. The class can act as the DataSource of DataGridView and other edit controls or of BindingSource components. LINQ to SQL materializes each association that represents the many sides of a one-to-many foreign-key relationship as an EntitySet(Of T), which is an extended implementation of Table(Of T). This means that you can generate parent/child[/grandchild] forms from the Data Source window's object graph by the same technique that you use for DataSets.
Let's give this technology a test drive using the Northwind database as an example. Drag the Customer node from the Data Source window to the form as a CustomerDataGridView or Details View. This adds a CustomerBindingSource and CustomerBindingNavigator for parent-entity editing. Next, drag the Customer.Order_s association node to the form to create the synchronized Order_sDataGridView/BindingSource. Finally, drag the Customer.Orders.Order_Details node to the form to add the Order_DetailsDataGridView/BindingSource. The Order_s and Order_Details nodes represent EntitySets, so plural names are appropriate. The Order_ entity name has an underscore suffix because the Order identifier might conflict with the LINQ Order By operator; the pluralizer retains the underscore.
If your entities contain timestamp columns, you must remove these columns from DataGridView controls, because the control misinterprets the timestamp's Byte() data type as an image and throws an error when your code attempts to populate it. The OrdersDataGridView includes columns for EntityRef(Of Customer), EntityRef(Of Employee), and EntityRef(Of Shipper), which represent one side of the association. Similarly, the OrderDetailsDataGridView has EntityRef(Of Order_) and Entity Ref(Of Product) columns. It's a good practice to remove these columns from DataGridViews, too.
Add this code to your WinForm class to start a DataContext session and populate the form's text boxes and DataGridViews:
Imports System.Data
Imports System.Data.Linq
Imports System.Linq
Public Class frmLINQ2SQL
Private dcNwind As LINQ2SQLDataContext
Private Sub frmLINQ2SQL_Load(ByVal sender _
As System.Object, ByVal e _
As System.EventArgs) _
Handles MyBase.Load
dcNwind = New NwindDataContext
CustomerBindingSource.DataSource = _
dcNwind.Customer
End Sub
End Class
The LINQ2SQLDataContext type assumes that you named the LINQ to SQL designer file LINQ2SQL.dbml.
Press F5 to build and run the project, and then verify that the OrdersDataGridView is synchronized with the Customer data and that OrderDetailsDataGridView displays the line items for the order you select (Figure 3).
Explore the Designer-Generated Classes
Gaining an understanding of the O/RM process requires spelunking at least part of the 2,000-line autogenerated entity class code in the LINQ2SQL.designer.vb or cs file for the Northwind database. Shippers table code is used in most examples for brevity (Listing 2). LINQ to SQL's O/RM uses attribute-based mapping by default; this snippet's TableAttribute decoration maps the Shipper table to the Shipper entity class:
<Global.System.Data.Linq.Table(Name:= _
"dbo.Shippers")> _
Partial Public Class Shipper
Implements Global.System.Data.Linq._
INotifyPropertyChanging, _
Global.System.ComponentModel._
INotifyPropertyChanged
...
End Class
The table name appears as the Source property value in the Designer. Classes implement System.Data.Linq.INotifyPropertyChanging and System.ComponentModel.INotifyPropertyChanged interfaces to support the DataContext's data tracking features. Supporting the INotifyPropertyChanged interface eliminates the need to interpose a BindingSource between the object and databound controls. However, Microsoft recommends adding the BindingSource.
The O/R Designer adds three default ColumnAttribute name/value pairs to ordinary Column members: Storage (private member name), Name (database column name), and DBType (SQL Server data type):
<Global.System.Data.Linq.Column(Storage:= _
"_Phone", Name:="Phone", _
DBType:="NVarChar(24)")> _
Public Property Phone() As String
Get
Return Me._Phone
End Get
Set
If ((Me._Phone Is value) _
= false) Then
Me.OnPropertyChanging("Phone")
Me._Phone = value
Me.OnPropertyChanged("Phone")
End If
End Set
End Property
Several additional ColumnAttributes designate primary-key, non-nullable, auto-increment, and timestamp columns, as well as entity properties that aren't represented as attributes (Table 1). The OnPropertyChanging("PropertyName") method raises Property-Changing events that store original property values to the DataContext for comparison with database values to manage optimistic concurrency. OnPropertyChanged("PropertyName") methods raise PropertyChanged events to track current property values.
Associations connect EntityRef(Of T) properties to EntitySet(Of T) collections to define the object equivalent of a many-to-one table relationship. Associations have fewer attributes than columns, but many more properties that you set in the Designer's property sheet (Table 2). The OnProperty-Changing code for an association is more complex than that for a column because the process requires removing the original and attaching a new current instance instead of just changing a foreign key value (Listing 3).
Most O/RM tools support three inheritance mapping strategies: table per class hierarchy, table per subclass, and table per concrete class. LINQ to SQL supports only the table per class hierarchy, which requires a discriminator column to specify the subclass. This snippet designates the Shipper base class with Shipper-Type discriminator code S and Truck, Rail, Air, and Ocean subtypes:
<Table(Name:="dbo.Shippers", ...)>
<InheritanceMapping(Code:="S", _
Type:=GetType(Shipper), IsDefault=True)> _
<InheritanceMapping(Code:="T", _
Type:=GetType(Truck))> _
<InheritanceMapping(Code:="R", _
Type:=GetType(Rail))> _
<InheritanceMapping(Code:="A", _
Type:=GetType(Air))> _
<InheritanceMapping(Code:="O", _
Type:=GetType(Ocean))> _
Partial Public Class Shipper
,,,
<Column(Name:="ShipperType", ... _
IsDiscriminator:=True)> _
Public Property ShipperType() _
As String
...
End Property
End Class
Table per class mapping would require five tables (base class plus four subclasses) in this snippet; table per concrete class would require four tables. If your domain model requires either of these two mapping strategies, try the Entity Framework and its Entity Data Model or one of the other popular O/RM tools for .NET (Additional Resources). Work is underway on a LINQ to NHibernate provider.
If you need attribute-free POCOs for entity classes, you must use the SqlMetal.exe command-line tool to generate a ClassName.vb or .cs file and a Mapping.xml file with an instruction like this one:
SqlMetal /server:oakleaf-vs20\SQLEXPRESS
/database:NwindLINQ
/map:\LINQ2SQLTest\NwindLINQ.xml
/language:vb /namespace:NwindLINQ
/code:\LINQ2SQLTest\NwindLINQ.vb /pluralize
Then create the DataContext—dcNwind in this example—with a constructor overload that takes a connection string and mapping filespec:
Private Sub frmLINQ2SQL_Load(ByVal sender _
As System.Object, ByVal e _
As System.EventArgs) _
Handles MyBase.Load
Dim strFile As String = _
"\LINQ2SQL\NwindLINQ.xml"
Dim strConn As String = _
"Data Source=.\SQLEXPRESS;Initial " + _
Catalog=NwindLINQ;" + _
Integrated Security=SSPI"
Dim xmsNwind As XmlMappingSource = _
XmlMappingSource. _
FromXml(File.ReadAllText(strFile))
dcNwind = New NwindLINQ(strConn, xmsNwind)
CustomerBindingSource.DataSource = _
dcNwind.Customers
End Sub
Substitute Stored Procedures for T-SQL Updates
There is considerable controversy regarding O/RM tools' preference for dynamically generated SQL statements rather than stored procedures for querying and, especially, updating data stores. By default, LINQ to SQL generates T-SQL SELECT statements from expression trees created by LINQ's From … In … Where … Order By … Select queries. Similarly, adding, updating, or removing an entity instance and invoking the DataContext.SubmitChanges() method sends T-SQL INSERT, UPDATE, or DELETE statements to the database. You can generate from DataContext.Log a TextWriter for a log file or StringBuilder to store or display the T-SQL commands that LINQ to SQL sends to the SQL Server 200x instance. This snippet writes to a txtSQL text box:
Private sbLog As New StringBuilder
Private swLog _
As New StringWriter(sbLog)
...
dcNwind = New NwindLINQDataContext
dcNwind.Log = swLog
...
txtSQL.Text = sbLog.ToString()
The fact that you don't need SQL Profiler to check LINQ to SQL's emitted commands is a boon to developers running SQL Server Express and a great time saver for those with the full set of SQL Server client tools.
LINQ to SQL's O/R Designer lets you substitute conventional stored procedures for SELECT, INSERT, UPDATE, and DELETE T-SQL batches. Dragging a SELECT Stored Procedures node from Server Explorer to the Designer's surface adds a StoredProcName(ParameterList) item to its Methods pane, a Partial Public Class StoredProcName entity class, and a Public Function StoredProcName to the DataContext. The function returns StoredProcName entities, such as uspGetCustomersByCountry. You can return a previously defined, compatible entity instead of an entity named for your stored procedure by adding a renamed function that takes advantage of the IMultipleResults interface but returns a single result to a partial class file (Listing 4).
Register an INSERT, UPDATE, or DELETE stored procedure by dragging its node from Server Explorer to the Designer's surface. In this case, you must right-click on the entity-class widget for the corresponding table and choose Configure Behavior to open the same-named dialog that lets you associate the stored procedure with an existing entity class. Accept or select the entity class in the dialog's Class list, and then choose Insert, Update, or Delete from the behavior list. Select the Customize option, open the list of registered stored procedures and choose the one that's appropriate for the entity and behavior to populate the Method Arguments and corresponding Class Properties lists. For UPDATEs, select PropertyName (original) for the primary-key field(s) that specify the row to update; PropertyName (current) items specify update values. Click on Apply or OK to generate a Sub InsertEntityName, Sub UpdateEntityName, or Sub DeleteEntityName override method and a corresponding Function StoredProcName that executes the stored procedure (Figure 4). The methods don't execute until your code invokes the DataContext.SubmitChanges() method. (Beta 1 introduced a few problems into using stored procedures for updates, but these should be fixed in the next CTP or Beta 2. You'll find descriptions of the problems in the blog post at http://tinyurl.com/2o23ch.)
You can reduce the complexity of the stored procedures' code by replacing parameters for original values with a timestamp column to manage concurrency conflicts. For example, this Update-Order_Detail procedure updates Order_Details records that have an added timestamp column:
Private Sub UpdateOrder_Detail(ByVal current As _
Order_Detail, ByVal original _
As Order_Detail)
Me.uspUpdateOrder_Detail(original.ProductID, _
current.UnitPrice, current. _
Quantity, current.Discount, _
original.OrderID, original.TimeStamp)
End Sub
The DataContext provides the current and original values as arguments of UpdateEntityName methods. InsertEntityName methods have only a current argument and DeleteEntityName methods have a single original argument.
Here's the code for the uspUpdateOrder_Detail function that executes the stored procedure by reflection:
<DebuggerNonUserCodeAttribute(), _
StoredProcedure(Name:= _
"dbo.uspUpdateOrder_Detail")> _
Public Function uspUpdateOrder_Detail( _
<Parameter(Name:="@ProductID")> _
ByVal ProductID _
As Global.System.Nullable(Of Integer), _
<Parameter(Name:="@UnitPrice")> ByVal _
UnitPrice As Global.System.Nullable(Of _
Decimal), <Parameter(Name:="@Quantity")> _
ByVal Quantity As Global.System.Nullable(Of _
Short), <Parameter(Name:="@Discount")> _
ByVal Discount As Global.System.Nullable(Of _
Single), <Parameter(Name:="@OrderID")> _
ByVal OrderID As Global.System.Nullable(Of _
Integer), <Parameter(Name:="@TimeStamp")> _
ByVal TimeStamp() As Byte) As Integer
Dim result As Provider.IExecuteResults = _
Me.ExecuteMethodCall(Me, CType( _
MethodInfo.GetCurrentMethod, _
MethodInfo), ProductID, _
UnitPrice, Quantity, Discount, _
OrderID, TimeStamp)
Return CType( _
result.ReturnValue,Integer)
End Function
Note that I removed the Global.System.Diagnostics, Global.System.Data.Linq, and Global.System.Reflection namespace prefixes from this code for brevity.
Resolve Optimistic Concurrency Conflicts
DataAdapters throw a DbConcurrencyException if the number of rows affected by executing an INSERT, UPDATE, or DELETE statement or stored procedure is zero. The exception's Row property returns the DataRow object that caused the first exception. Resolving the conflict usually requires writing a substantial amount of business logic code, which isn't easy to test. In many cases, you might need to deal with a cascade of exceptions thrown when attempting to update related records.
When you execute LINQ to SQL's DataContext.Submit-Changes(ConflictMode.ContinueOnConflict) overload, the DataContext.ChangeConflicts property returns a Change-ConflictsCollection(Of ObjectChangeConflict), which has a member for each instance of any object that has incurred a concurrency conflict. ObjectChangeConflict members contain a MemberConflicts(Of MemberChangeConflict) collection for each member with a conflict. The Member-ChangeConflict object has OriginalValue, Current-Value, and DatabaseValue properties, as well as an overloaded Resolve method that accepts Refresh-Mode.Keep-Changes, RefreshMode.KeepCurrent-Values or RefreshMode.OverwriteCurrent-Values as its argument value. These nested collections let business-rule code or users resolve multiple conflicts with a pair of nested For Each … Next loops that populate a simple message box (Figure 5). This article's LINQ2SQL.sln sample project contains a ProcessChanges procedure with the conflict resolution code for that message box (download the code here).
Some beta testers have called LINQ to SQL's entity classes "glorified typed Data-Sets," which I call damnation by faint praise. LINQ to SQL's O/R Designer has some similarities to the DataSet designer and Object Data Sources created from these entity classes offer databinding capabilities similar to DataSets. However, the underlying LINQ to SQL philosophy is to migrate from manipulating collections of cached DataRows to collections of in-memory custom-business objects that support LINQ queries. Download the LINQ2SQL.sln project and its NwindLINQ.mdf database. Compare performance with BindingNavigators bound to Tables, LINQ queries, or the result of a SELECT stored procedure, as well as execution times of INSERT, UPDATE, and DELETE operations on multiple object instances. Contrast the simplicity of LINQ to SQL coding techniques with that for Data-Sets. Even at this early beta stage, LINQ to SQL looks to me like a winner for SQL Server 200x users.
About the Author
Roger Jennings is an independent architect/database developer and writer with more than 1.25 million English copies printed. Roger's latest .NET book is Expert One-on-One Visual Basic 2005 Database Programming (ISBN 0-7645-7678-X) for WROX/Wiley. He's also a Visual Studio Magazine contributing editor and online columnist, and manages the OakLeaf Systems blog. His Code of Federal Regulations Web services won Microsoft's 2002 .NET Best Horizontal Solution Award. Reach him at roger_jennings@compuserve.com.
Back to top
|