VSM Cover Story

Speed O/R Mapping with LINQ to SQL

LINQ to SQL continues to be a top contender in the .NET object/relational mapping tool market despite Microsoft's promotion of the Entity Framework as one of the "Pillars of SQL Server 2008: Dynamic Development."

• By Roger Jennings
• 12/01/2008

LINQ to SQL celebrated its first anniversary on Nov. 11, 2008. As Microsoft's first object/relational mapping (O/RM) tool to reach developers as a finished product, LINQ to SQL gave .NET developers a full-fledged O/RM and persistence tool built into Visual Studio 2008. VS 2008 SP1's only changes to LINQ to SQL were support for new SQL Server 2008 data types (Date, Time, DateTime2, DateTimeOffset, Filestream), improved handling of queries that compare nullable columns with VB, and "numerous bug fixes."

Early adoption by many .NET developers, the recent arrival of needed add-ins, and no requirement for major surgery in VS 2008 SP1 assure LINQ to SQL's future as an object-oriented data access layer (DAL) in small to midsize -- and even some enterprisewide -- Windows, Web, and Silverlight applications that connect to SQL Server 2000 or later. Leveraging LINQ to SQL in your apps has many compelling benefits, but you need to be aware of some of the larger caveats, including the areas where LINQ to SQL and Entity Framework (EF) overlap and their inherent limitations based on the way Microsoft has implemented each technology. I'll walk you through how to take advantage of LINQ to SQL's rapid application development (RAD) features with the graphical O/R Designer and SqlMetal command-line tool, demonstrate new add-ins that overcome several LINQ to SQL limitations, and give you a rundown on its use as a DAL for VS 2008 SP1's ADO.NET Data Services and the latest ASP.NET Dynamic Data preview. Finally, I'll draw up a scorecard that compares LINQ to SQL with its erstwhile big brother -- ADO.NET Entity Framework -- as an aid to developers deciding which O/RM tool to use for specific projects. Downloadable sample code is available for all demonstration projects.

According to lead developer Matt Warren, LINQ to SQL started out as a project to create ObjectSpaces vNext. It was co-designed by the C# 3.0 team in tandem with LINQ as a full-fledged O/RM implementation and grew legs as a competitive relational database query and persistence tool for SQL Server 2000+ after ObjectSpaces fell into WinFS's black hole (see Additional Resources). Domain-driven design (DDD) purists insisted that LINQ to SQL didn't deliver persistence ignorance (PI), but British developer Ian Cooper disagreed in "Being Ignorant with LINQ to SQL," an early analysis of LINQ to SQL's conformance to Martin Fowler's "Patterns of Enterprise Application Development" (Addison-Wesley Professional, 2002). Cooper concluded that "LINQ to SQL scores pretty well against the PI checklist" and is well suited to test-driven development. He then went on to deliver a five-part "Architecting LINQ to SQL Applications" series that became required reading for developers considering LINQ to SQL as a production O/RM tool (see Additional Resources).

LINQ to SQL gained additional traction with .NET developers as the SQL Server Data Programmability (DP) team delayed the projected release of their Entity Data Model and its first concrete implementation -- the enterprise-targeted but heavyweight EF -- to the "first half of 2008." An early series of tests by Microsoft performance guru Rico Mariani, which I referenced in my article "Optimize LINQ to SQL Performance" (November, 2007), demonstrate that LINQ to SQL's query-execution speed compares favorably with bare ADO.NET DataReaders (see Additional Resources). LINQ to SQL's faster query execution than EF -- combined with lower resource consumption -- led the ASP.NET team's Scott Guthrie to use LINQ to SQL as the DAL for demonstrating data-intensive ASP.NET 3.5 Web sites and Web apps, as well as ASP.NET model-view-controller (MVC) and ASP.NET Dynamic Data technology previews. Guthrie's highly detailed nine-part series of LINQ to SQL tutorials convinced many Web developers to replace typed DataSets with LINQ to SQL EntitySets. Use of LINQ to SQL grew neck-and-neck with DataSets by October 2008, according to an informal one-week, blog-based .NET 3.5 subsystem usage survey conducted by Scott Hanselman. Of 4,889 respondents, 1,734 (35.5 percent) reported using LINQ to SQL, while 1,887 (38.6 percent) continued using DataSets.

Get RAD with the O/R Designer
Mapping relational tables to CLR objects with LINQ to SQL is dead simple. To create a new LINQ to SQL WinForm project, add a new LINQ to SQL Classes template item and rename the XML mapping file to Northwind.dbml. Click on Add to open the graphical O/R Designer window and add a reference to the System.Data.Linq namespace. Drag table items to the Designer window from a SQL Server 2000+ database that you've added previously to Server Explorer. (Although LINQ to SQL was designed with pluggable data sources, the DP team determined that LINQ to SQL was to be a SQL Server-only DAL when it assumed control of the implementation from the C# team in 2007.)

Lines representing associations (relationships) connect widgets that represent EntityTypes (see Figure 1). If you accept the default Tools | Options | Database Tools | OR Designer | Pluralization of Names setting of true, the O/R Designer singularizes the EntityType names, which is a generally accepted naming convention. After a bit of practice, you can create a raw data model for a 120-table database in less than five minutes. However, it might take you an hour or two to arrange the designer's EntityType widgets into an optimal pattern.

At this point, LINQ to SQL's code generator has created a 2,450-line Northwind.designer.cs (or a 2,630-line .VB) file that defines

partial CLR classes for the top-level NorthwindDataContext object. NorthwindDataContext contains a System.Data.Linq.Ta­-ble<EntityType> class with a GetTable<EntityType> member for each table, as well as a partial class that defines each EntityType and its properties (fields). These partial classes have a Table attribute decoration to specify the source table and Column attributes for field names:

[Column(Storage="_CategoryID", 
  AutoSync=AutoSync.OnInsert, 
  DbType="Int NOT NULL IDENTITY", 
  IsPrimaryKey=true, 
  IsDbGenerated=true)]
public int CategoryID
{
  get
  {
    return this._CategoryID;
  }
  set
  {
    if ((this._CategoryID != value))
    {
      this.OnCategoryIDChanging(value);
      this.SendPropertyChanging();
      this._CategoryID = value;
      this.SendPropertyChanged("CategoryID");
      this.OnCategoryIDChanged();
    }
  }
}

The setter for a property value change sends PropertyNameChanging and PropertyNameChanged events to the DataContext for validation and change tracking.

EntityType properties include one:many and many:one entity associations that represent relationships between tables. Here's a typical set of association attributes for the relationship between the Categories.CategoryID and Products.CategoryID fields:

[Association(Name="Category_Product", 
   Storage="_Products", 
   ThisKey="CategoryID", 
   OtherKey="CategoryID")]
public EntitySet<Product> Products
{
  get
  {
    return this._Products;
  }
  set
  {
    this._Products.Assign(value);
  }
}

The attribute decorations prevent classifying default LINQ to SQL classes as plain old CLR objects (POCO), which opens up LINQ to SQL (and EF) to criticism from .NET O/RM purists who favor the Hibernate and NHibernate O/RM tools. However, Hibernate and NHibernate are complex applications, have a steep learning curve, and don't come close to qualifying as RAD tools. I'll show you how to generate POCO-compliant code later in this article.

To add support for databinding WinForm controls to DataContext members, choose Data, and then Add New Data Source to launch the Data Source Configuration Wizard. The process is similar to that for databinding typed DataSets, except that you select Object instead of Database in the Wizard's Choose a Data Source Type dialog. Click on Next, expand the ProjectName nodes (LinqToRadCS for this example), and select the topmost EntitySet in the object hierarchy for your project, which is Customers in this example (see Figure 2). Click on Finish to dismiss the Wizard and display the Customer node in the Data Sources window.

Next, you need to expand the Customer node to display its properties, which includes the Orders subnode that represents the one:many Customer_Order association. The Orders node has subnodes for many:one Customer_Order, Employee_Order, and Shipper_Order associations, as well as a one:many Order_Order_ Details association. Similarly, Order_Details has many:one Order_Order_Details and Product_ Order_Details associations. Change the Customer control type from DataGridView to Details to display and edit Customer data in TextBox controls. Finally, drag the Customer node to the upper left corner of the active form area to add the bound text boxes, customerBindingNavigator, and customerBindingSource

components (see Figure 3). To create a DataGridView bound to a BindingSource for editing the selected customer's Orders entity set, drag the Customer.Orders node to the form. Similarly, drag the Customer.Orders.Order_ Details node to the form to display and edit the Order_Detail EntitySet for the selected Order.

LINQ to SQL stores the standard ADO.NET connection string in the project's app.config or web.config file as DbmlFilenameConnectionString. You must add code to instantiate the DataContext object and assign the BindingSource's DataSource property to an EntitySet. To populate the text boxes, add the following using directive and Form_Load event handler (or their VB equivalents) to the code behind a Windows form:

using System.Data.Linq;

private void MainForm_Load(object sender, EventArgs e)
{
    NorthwindDataContext ctxNwind = 
        new NorthwindDataContext();
    this.customerBindingSource.DataSource = 
        ctxNwind.Customers;
}

Be sure to check out the complete LinqToSqlRadCS.sln sample project -- which includes a fleshed out version of this code -- that you can download.

Use the LinqDataSource with Web Apps
Creating a corresponding RAD LINQ to SQL Web application is even easier. The LinqDataSource server control handles the entire process of populating the control to which it's bound. Add a LINQ to SQL Classes item to a new Web app, and drag the table nodes from Server Explorer to the O/R Designer window. Next, add an ASP.NET data-aware control, such as a FormView to the page, open its SmartTag, and select New Data Source to open the Data Source Configuration Wizard. In the Choose a Data Source Type dialog, select LINQ, and type a unique ID (ldsCustomers for this article's example). Finally, click on Next to open the Choose a Context Object dialog, accept the default ProjectName.DataContextName object, and click on Next to open the Configure Data Selection dialog, which offers a dropdown list of tables in the data source.

Select Customers (Table<Customer>) to display the available Customer properties and accept the default * selection (see Figure 4) if you want the bound control to be updatable; otherwise, mark all but the * and Orders text boxes. If you selected all properties, click on the Advanced button to open the Advanced Options dialog and mark the check boxes for those types of updates you want to perform: deletes, inserts, updates, or all three. Clicking on the Where button opens the Configure Where Expression dialog that lets you specify a parameterized Where clause for the LINQ query expression; the parameter value can be obtained from a control, cookie, form field, profile property, query string, or session field. Click on Finish to bind the FormView to the LinqDataSource control.

Next, activate the FormView, set its ID property to fvCustomer, open its SmartTag, autoformat the control with the desired color scheme, and mark the Enable Paging check box to generate groups of 10 links that display a specific Customer entity. If you selected all properties in an earlier step, Orders is an EntitySet of the customer's orders, which a textbox can't display. So select Edit Templates, accept the default ItemTemplate, and open the OrdersLabel's SmartTag; choose Edit DataBinding, remove the Data Bindings by setting the Field Binding's Bound to List to (Unbound), and delete the Orders label or text box items. You need to repeat the preceding steps for the EditItemTemplate and the InsertItemTemplate. Press F5 to build and run the project.

Conform Code to POCO Requirements
The SqlMetal.exe command-line tool can generate code that's the same as or similar to what the O/RM designer generates. Alternatively, you can specify an XML mapping file to eliminate the need for decorating the generated classes for attribute-based mapping, which DDD purists object to because it compromises PI. To rid the LINQ to SQL classes of storage-related attribute/name pairs, execute this SqlMetal command from the \Program Files\Microsoft SDKs\Windows\v6.0A\Bin folder (assuming the sample project is in the \VSM\LinqToSqlPocoCS folder):

SqlMetal /server:localhost\SQLEXPRESS 
/database:Northwind
/context:NorthwindContext
/map:\VSM\LinqToSqlPocoCS\Northwind.xml 
/namespace:LinqToSqlPocoCS 
/code:\VSM\LinqToSqlPocoCS\Northwind.cs 
/pluralize

Here are the POCO versions of the CategoryID and Products association EntitySet properties without the default attribute decorations from Northwind.cs's Category class:

{
  get
  {
    return this._CategoryID;
  }
  set
  {
    if ((this._CategoryID != value))
    {
      this.OnCategoryIDChanging(value);
      this.SendPropertyChanging();
      this._CategoryID = value;
      this.SendPropertyChanged("CategoryID");
      this.OnCategoryIDChanged();
    }
  }
}

public EntitySet<Product> Products
{
  get
  {
    return this._Products;
  }
  set
  {
    this._Products.Assign(value);
  }
}

The primary differences between the LinqToSqlRadCS.dbml and Northwind.xml mapping files are the attribute name/value pairs of Column and Association elements. One drawback of using SqlMetal.exe to generate default or POCO entity classes is it's all-or-nothing; you can't exclude specific tables from the mapping process.

A problem that has limited LINQ to SQL's traction among .NET developers is its inability to update the data model for inevitable changes to the data store's schema. Accommodating a schema change has required manual DBML file editing or starting the design over from scratch. Recently, Huagati Systems Co. Ltd. released an add-in with a workaround for this problem (see "VS 2008 SP1 Add-Ins Remove LINQ to SQL Roadblocks"). The add-in also has several additional functions that increase developer productivity with LINQ to SQL and EF.

Deciding When to Use LINQ to SQL or EF for O/RM
The DP group released the first community technology preview (CTP) of EF and EDM on Aug.16, 2006, almost a year after LINQ to SQL (then called DLinq) arrived in a C# 3.0 CTP for Orcas beta 2 at Microsoft's Professional Developers Conference (PDC) 2005 on Sept. 13, 2005. Data-oriented .NET developers worrried that the DLinq and EF/EDM implementations were direct O/RM competitors and that they needed guidance to choose the correct tool. The decision process grew more complex as EF's feature set changed during the two years leading up to its delayed release with EDM in VS 2008 SP1.

There are several important differences between the SP1 versions of the two O/RM tools (see Table 1). Your decision is cast in concrete if your project must connect to Oracle, MySQL, PostgreSQL, or SQLite, as well as IBM DB2, IDS, and U2, or other databases that have EF-enabled managed ADO.NET data providers: LINQ to SQL connects only to SQL Server 200x (Express or Compact). Another deciding factor is LINQ to SQL's one-to-one mapping of tables to EntitySets. If you are in control of -- or can exert substantial influence on -- the SQL Server database schema and domain object model, LINQ to SQL is a valid contender as the O/RM tool. If a supported POCO option is important for your project, LINQ to SQL is the answer. Otherwise, project complexity and feature tradeoffs become the deciding factors.

You'll notice that Table 1 contains many instances of "(promised in version 2)" in the Entity Framework/EDM column, but there's no official timetable for version 2's released to manufacturing (RTM) date. LINQ to SQL survived the VS 2008 RTM triage, but the SQL Server group appears to be stacking the deck in favor of EF by devoting substantial resources to development of EF version 2 while not even mentioning an upgrade path for LINQ to SQL. Furthermore, the DP team has incorporated data-source bias in other products. For example, the original version of the ADO.NET Data Services Framework -- then called by its code-name "Astoria" -- had a dependency on EF as its data source. However, EF seems a heavy-duty data layer for a relatively lightweight RESTful Web service. Astoria's SP1 version now accepts for read-only use any data source that exposes a class whose properties implement IQueryable<T>.

But Astoria SP1 requires data sources other than EF to implement a new IUpdatable<T> interface for clients to invoke HTTP POST, PUT, and DELETE methods. EF doesn't implement IUpdatable <T> on its ObjectContext; Astoria SP1 implements it for EF in the System.Data.Services namespace. Other data sources -- such as LINQ to SQL, LINQ to DataSets, or LINQ to NHibernate -- require the developer to implement IUpdatable<T>. Implementing IUpdatable<T> involves writing a substantial amount of code to define its 12 methods, as Mike Taulty demonstrated in his "ADO.NET Data Services: IUpdatable on LINQ to SQL" post of June 14, 2008.

The ASP.NET team generally favors LINQ to SQL over EF as a data source for the reasons mentioned earlier, so LINQ to SQL runs out-of-the-box as a data source for ASP.NET Dynamic Data. ASP.NET Dynamic Data requires a workaround to replace the default Entity Framework Data Model provider for Dynamic Data with a new provider that solves issues with 1->0..1 and *->1 associations.

You'll find reports in .NET developer-oriented blogs describing commercial LINQ to SQL implementations in Web sites and smart-client applications. There's a growing list of open source add-ins and add-ons for LINQ to SQL DALs. If you decide on LINQ to SQL as the DAL for your next data-intensive project, you'll have plenty of company in the .NET community.

• Get Code Download