LINQ Takes Shape in May CTP
Language Integrated Query (LINQ) prepares to join the Visual Studio Orcas upgrade with a new graphic DLinq Designer, better support for bound controls, and almost parity for C# 3.0 and VB 9.0 programmers.
The Language Integrated Query (LINQ) May 2006 Community Technical Preview (CTP), which Microsoft released on May 10, 2006, adds a raft of new and improved DLinq and XLinq features to the VB-centric January 2006 CTP. Optionally, the new CTP updates the C# components of the PDC 2005 LINQ Technical Preview to provide all DLinq features and most XLinq capabilities to C# LINQ projects. These additions are the first step in productizing LINQ for inclusion in the next release of Visual Studio, presently code-named "Orcas." I'll start by giving you a brief summary of new and improved VB 9.0 and C# 3.0 features, and then dig deeper into the latest domain-specific DLinq component additions with illustrated, step-by-step examples and sample VB code that you can Erik Meijer—a major force behind Cω's development—announced in September 2005 that Visual Basic had become his programming language of choice. His reasons for the move are that VB "allows static typing where possible and dynamic typing where necessary in the form of relaxed delegates, improved nullable support, dynamic identifiers ... and last but not least dynamic interfaces." These features enabled Paul Vick's VB team to outpace their C# colleagues in the process of adding advanced domain-specific LINQ features, especially to XLinq.
For VB developers, the May 2006 CTP adds these new features to the January 2006 CTP:
- A graphical DLinq Designer replaces SQLMetal.exe command-line statements with a visual design surface for creating DLinq entity classes. You drag tables from the Server Explorer to the DLinq Designer window and then create a DLinq Data Source with the Data Source Configuration Wizard. The Designer detects table relationships and creates "DLinq associations" between the tables.
- A graphical DLinq visualizer helps you debug queries
- DLinq now supports table inheritance, stored procedures, user-defined functions, optimistic concurrency conflict resolution (OCCR), multitier entities, remote and local entity sets, and XML mapping files.
- LINQ over DataSet adds a new component to query DataSets and DataTables; offers DistinctRows, EqualAllRows, UnionRows, IntersectRows, and ExceptRows operators that return DataRows; and provides the ToDataTable() method to return a DataTable from a sequence.
- LINQ queries now support Group By query comprehensions.
- XML literals get outlining support in the VB editor window. Outlining lets you collapse or expand XML element literals that occupy more than one line.
- XML axis properties (IEnumerable(Of XElement) and IEnumerable(Of XAttribute)) get a Value extension property, which detects the first object in an IEnumerable(Of <T>); if the object exists, the Value property is called on it.
For more details on new DLinq features for VB 9.0, see the DLinq Overview for VB Developers.doc file's Chapter 7, "New For Spring 2006." You also should review the new LINQ Over DataSet for VB Developers.doc and the updated Visual Basic 9.0 Overview.doc files.
The LINQ May 2006 CTP makes these changes to the January 2006 CTP's VB features:
Prerequisites for installing the VB 9.0 feature set are Visual Basic 2005 Express edition or later and, if you want to run the DLinq samples, SQL Server 2000a or SQL Server 2005 Express edition or later. DLinq remains tied to SQL Server, but Microsoft promises to provide assistance to other RDBMS vendors who want to write DLinq-enabled native data providers. The CTP doesn't include a sample provider.
C# 3.0 Catches Up With VB 9.0
The LINQ January 2006 CTP didn't include any C# content, so here are new C# 3.0 features added to the original PDC 2005 release:
- LINQ acquires an IQueryable<T> object to enable pluggable query processors, the ToQueryable method to return an expression tree from an IEnumerable<T> object for pluggable query processor optimization and execution; the Expression.Compile method for in-memory execution; support for inner equi-joins and the GroupJoin operator to provide "outer-join like capabilities"; and Single, SingleOrDefault, Last, LastOrDefault, ElementAt, and ElementAtOrDefault operators.
- C# 3.0 query expressions now provide syntax for Join and GroupJoin and a Let statement to declare temporary variables in query expressions. Lambda statement blocks supplement lambda expressions.
- XLinq's C# IDE gains support—similar to VB 9.0's IDE—for pasting XML content directly into the C# editor. XLinq now supports annotations, mixed content, and streaming output.
- A new expression tree visualizer provides detailed inspection of expression trees.
- DLinq gets the same graphical designer and query visualizer as VB 9.0 and supports stored procedures, user-defined functions, inheritance, OCCR, multitier entities, remote and local entity sets, and XML mapping files.
- LINQ Over DataSet features are the same as VB 9.0's.
These new features bring C# 3.0 to about even par with VB 9.0 LINQ capabilities. As usual, VB gets the nod on LINQ, DLinq, and XLinq usability while C# offers more sophisticated language extensions.
Prerequisites for installing the C# 3.0 update are the Visual C# 2005 Express edition or later and, if you want to run the DLinq samples, SQL Server 2000a or SQL Server 2005 Express edition or later. Unlike the VB version, the C# Readme page states that VS 2005 Standard Edition or later is required to support the DLinq Designer.
Installing the LINQ May 2006 CTP on a development machine with VS 2005 Standard Edition or later is simple and quick. The installer detects if you have the VB option and January 2006 CTP installed, and, if you do, removes the CTP. If you specified the C# option in VS 2005 Setup, the installer removes the PDC 2005 Technical Preview bits, if present. You're then presented with an "Update C# Language Service for LINQ" installer dialog that offers the option to perform the update. Select the Update C# Language Service option and click Next to complete the installation.
Updating the VB IDE is automatic, but you must enable the C# IDE for the May 2006 CTP manually. If you don't enable the C# IDE, IntelliSense for LINQ language features doesn't work, you incur spurious build errors, and the editor decorates LINQ code with wavy red underlines. Run the C:\Program Files\LINQ Preview\Bin\Install C# IDE Support.vbs script to modify the editor. The Uninstall C# IDE Support.vbs script returns the editor to its original condition. Use the Add or Remove Programs tool to remove the LINQ May 2006 CTP bits and return VS 2005 or VB Express to its RTM condition.
LINQ, DLinq, and XLinq documentation has expanded dramatically. The installer adds a LINQ Preview choice to the Programs menu with pointers to 19 documents, which include LINQ, XLinq and DLinq Overviews and Hands-On Labs, and LINQ Over DataSet overviews for C# and VB. The menu also opens folders that contain C# and VB sample projects.
Bind DataGridViews to DLinq Data Sources
Emulating the data-binding and concurrency management capabilities of ADO.NET 2.0 typed DataSets is essential to the future success of DLinq in the VS "Orcas" release, especially among VB developers. Microsoft's DLinq Designer.doc technical paper shows you how to build the archetypical Customers-Orders-Order Details form from DataGridView controls bound to elements of a DLinq Data Source. The purpose of the DLinq Designer is to simplify creating DLinq DataContexts and Data Sources.
Surprisingly, current DLinq Data Sources don't solve the DataGridView data-binding problems I reported in my "Anomalies and Issues with VB 9.0 DLinq Code" blog post. In fact, a documentation omission and two errors in auto-generated VB code for the DataContext class complicate the process of emulating with DLinq the capabilities of DataGridView controls bound to typed DataSets.
Here are the simplest steps to test data-binding capabilities and OCCR behavior:
- Create a three-level hierarchical DLinq Data Source from the Northwind sample database.
- Provide a Customers-Orders-Order Details form by adding bound DataGridView controls for each data-source entity.
- Use the bound DataGridView controls to perform typical create, retrieve, update, and delete (CRUD) operations on the tables. Add code to work around missing DataGridView functionality.
- Run two instances of the project, emulate a concurrency conflict, and evaluate OCCR.
The DLinq Designer.doc file's step-by-step instructions are intended to demonstrate DLinq capabilities beyond simply testing data binding. These next procedures simplify the form-design process; they also illustrate the documentation error and auto-generated code errors I mentioned earlier. Follow this initial drill to create the three-level hierarchical DLinq Data Source:
- If you don't have the Northwind sample database installed in your SQL Server 2005 [Express] instance, download the Northwind and pubs Sample Databases for SQL Server 2000 and run SQLServerSampleDB.msi. Open and run the C:\SQL Server 2000 Sample Databases\instnwnd.sql script in SQL Server Management Studio [Express] to create the database and its tables.
- Create a new LINQ Windows Application named TestDataBinding or the like.
- Open Server Explorer, create a connection with Windows authentication to the Northwind database, and expand the Tables node.
- Choose Project, Show All Files, then expand the References node and verify that a reference to the System.Query namespace is present. If not, see Figure 1 to handle the first gotcha.
- Open Solution Explorer, right-click TestDataBinding, choose Add New Item, select the DLinq Objects icon, rename DLinqObjects1.dlinq to Northwind.dlinq, and click Add to add the DLinq Designer to the project.
- Drag the Customers table icon from Server Explorer to Northwind.dlinq's surface to add a Customer DLinqClass object, and press F5 to build and run the project. (You must build the project after adding each DLinqClass object.) If you receive a build error message, see Figure 1.
- Create a DLinq Data Source from the Customer DLinq class by choosing Data, Add New Data Source to start the Data Source Configuration Wizard. Select Object, click Next, expand the two TestDataBinding nodes, and select the Customer object (see Figure 2). Click Next and Finish to create the Customer DLinq Data Source.
- Open the Data Sources window and click Refresh to display the Customer DLinq Data Source and its properties (fields). Press F5 to build and run the project.
- Open Server Explorer and drag the Orders table icon to Northwind.dlinq to create an Order DLinqClass and an Association (relationship line) with the Customer DLinqClass. Reposition the added Order element to the right of the Customer element. Press F5 to build and run the project.
- Open the Data Sources window and click Refresh to display the Orders DLinq Data Source and its properties. Press F5 to build and run the project.
- Open Server Explorer and drag the Order Details table icon to Northwind.dlinq to create an Order_Details DLinqClass and an Association (relationship line) with the Order DLinqClass. Press F5 to build and run the project. If you encounter a build error, close and reopen the project and press F5 again.
At this point you have the DLinq Data Source required for the Customers, Orders, and OrderDetails DataGridViews (see Figure 3).
Next, add and bind the three DataGridViews to the Customer, Orders and Order_Details DLinq Data Sources, and work around the DLinq Designer documentation error by following these steps:
- Open the Form1.vb (Design) window and drag the Data Sources window's Customer node to the form to add the CustomerBindingSource, CustomerBindingNavigator, and CustomerDataGridView. (Why Customer is singular and the other Data Sources are plural is a DLinq mystery.) Size the DataGridView, which has random-ordered columns, to about 600 by 130 pixels.
- Repeat step 1 for the Orders and Order_Details data sources, but set the width of the OrderDetails DataGridView to about 500 pixels. Press F5 to save the changes.
- Optionally, open the properties sheet for each DataGridView, click the Columns (Collection)'s builder button to open the Edit Columns dialog, and rearrange the column sequence to correspond to the table design. Remove the Customer column from the OrdersDataGridView and the Order_ column from the OrderDetailsDataGridView (see Figure 4). Save the changes with F5.
- Add an Imports System.Data.Dlinq.DataQueryExtensions directive to the top of the Form1 class file and add a Private dcNwind As New NorthwindDataContext declaration.
- Add a Form1_Load event handler, and then add a CustomerBindingSource.DataSource = dcNwind.Customers.ToBindingList() instruction to the event handler. BindingLists from DLinq Data Sources only support change notification; sorting and filtering features are missing.
- Press F5 to build and run the project. You incur a mysterious "The type 'Order_Detail' has no identity key" unhandled exception message. The exception occurs because the Order_Detail entity's source table has a composite primary key; the problem doesn't occur with Customer and Order_ types. Press Shift+F5.
- To specify OrderID as an identity key, open Northwind.dlinq, select the Order_Details DLinqClass' OrderID field and open its properties window. Change the IsID property value from False to True (see Figure 5). Build and run the project.
- Observe that Order_Detail rows display duplicate values. For example, the default ALFKI order 10643 has three rows for ProductID 28 (see Figure 6). Obviously this result is incorrect because OrderID and ProductID are the composite primary key fields of the Order Details table.
- Step 7 of the DLinq Designer.doc file's "Creating Objects from the Toolbox" topic on page 9 states: "Set the IsID property to True (for the OrderID column only)," which is incorrect. You also must change the ProductID property's IsID property value from False to True, and then build the project. After this course correction, the Order_DetailsDataGridView displays the correct rows.
Searching for more information on IsID returns only this gem for the IsIdentity attribute of the Column element (on page 49 of the DLinq Overview for VB Developers.doc file): "Describes whether the column is part of the overall primary key. This information is redundant given the PrimaryKey element and may be removed." (Hopefully, the LINQ team won't remove it.) The current DLinq implementation doesn't appear to auto-detect composite primary key fields of source tables.
Test DLinq Data-Binding Features
Set up the form to save changes to the source tables by selecting the CustomerBindingNavigatorSaveItem button and setting its Enabled property value to True. Double-click the button to generate a CustomerBindingNavigatorSaveItem_Click event handler and add these instructions to the handler:
The Validate method assures that all changes update, and invoking SubmitChanges sends the changes to the database tables.
Adding these two instructions after dcNwind.SubmitChanges() lets you verify update persistence without closing and reopening the form:
dcNwind = New NorthwindDataContext
CustomerBindingSource.DataSource = _
If you didn't complete the preceding two exercises, the sample TestDataBinding.sln VB project in this article's sample code file contains the basic test form with the preceding instructions. You'll also find a TestDataSet.sln project to let you compare DLinq Data Source and DataSet editing.
BindingSources from DataTables support sorting, advanced sorting, filtering, searching, and change notification. Sorting lets users order rows by ascending or descending sorts on columns. As mentioned earlier, BindingSources from DLinq Data Sources only support change notification, so you can't sort bound DataGridViews.
The process of editing the test form's existing DataGridView cell values is identical to that for DataSets, as is adding a new Customers row. However, adding a new Orders or Order Details row differs considerably. Adding a new row to the OrdersDataGridView bound to a DataTable adds a default incremented OrderID and the CustomerID foreign key values automatically. The DLinq Data Source adds a default 0 OrderID value and no CustomerID value. Similarly, adding a new Order_Details row to a DataTable adds the incremented OrderID value; the DLinq Data Source adds the default 0 value to the Order_DetailsDataGridView.
The most serious editing deficiency with DLinq Data Sources is the inability to delete rows from the underlying database by deleting DataGridView rows. The January 2006 CTP implemented cascading deletions, which persisted to the database. For example, deleting a Customers row deleted it and all related Orders and Order Details rows in the database after invoking SubmitChanges; similarly, deleting an Orders row deletes it and all related Order Details rows.
However, deleting a row in the Order_DetailsDataGridView manually didn't persist the change to the Order_Details table. The May 2006 CTP doesn't persist any deletions that you make in the DataGridViews. However, if you attempt to delete a Customers row—with or without having deleted the related Orders and Order Details rows—you receive an error message that indicates violation of the FK_Orders_Customers constraint. When you delete an Orders row with related Order_Details rows, you don't receive an error message for the FK_Order_Details_Orders constraint violation. It's likely that this defect is related to the presence of the Order Details table's composite primary key.
The inability to permanently delete rows from bound DataGridViews manually requires writing a substantial amount of workaround code. The DLinqDataBinding.sln project in the sample code file adds workaround code from my January 2006 CTP VbLinqDemo.sln sample project to the DLinqGridsClass.vb partial class file. Substantial changes to this code in the partial class file were required to accommodate the May 2006 CTP. However, the editor automatically updates the VB LINQ query syntax sequence from Select ... From to From ... Select.
Deleting related child objects with code requires deleting the corresponding association element, which throws a NullReferenceException. The exception occurs because there's an obvious error in the auto-generated code for the Order_ entity's Customer property and the Order_Detail entity's Order_ property. In both cases, the code sets the entity to remove to Nothing before applying the Remove method. The fix is to reverse the order of the instructions, as shown here with the erroneous line emphasized:
If (Not (Me._Customer.Entity) Is Nothing) Then
'Fix for deletions
'Me._Customer.Entity = Nothing (moved below)
Me._Customer.Entity = Nothing
This problem is serious because fixes are likely to be lost when regenerating the code. Search for these fixes in the DLinqDataBinding.sln project's Northwind.vb DataContext file.
OCCR is reminiscent of concurrency violation handling for ADO.NET DataAdapter update exceptions with the DbConcurrencyException class. You apply OCCR by adding the ConflictMode.ContinueOnConflict or ConflictMode.FailOnFirstConflict enum to a new overload of the SubmitChanges method. If you elect to ContinueOnConflict, you can write error-handling code to return a list of all concurrency violations for a single SubmitChanges invocation (see Listing 1 and Figure 7). You also can retry updates by applying the Resolve method with the appropriate member of the RefreshMode enum, such as KeepChanges. For more details on OCCR, read the DLinq Overview for Developers.doc file's section "7.6 Optimistic Concurrency Conflict Resolution."
Despite the problems with IsID values, row deletion persistence, and NullReferenceExceptions when deleting rows with code, LINQ and its DLinq and XLinq domain-specific implementations are shaping up as far more than an enhanced object-relational mapper and replacement for ObjectSpaces. Download the DLinq sample code and give the three sample projects a test drive. Stay tuned for future FTPOnline articles on LINQ Over DataSet and other new LINQ, DLinq, and XLinq features.