Anomalies and Issues with VB 9 DLinq Code

The January 2006 VB DLinq Technical Preview finally lets VB coders create Visual Studio test projects that explore most LINQ capabilities. I find that I seldom discover bugs, coding pitfalls, performance hits, and other problems when executing simple test examples, such as those of the preview's SampleQueries.sln project. So my "Test Drive VB9 and DLinq" article for Visual Studio Magazine's April 2006 issue and the .NETInsight newsletter uses moderate- to large-size object collections in the accompanying VbLinqDemo.sln sample project. VbLinqDemo.sln illustrates how to take advantage of most new DLinq and XLinq features. The VB team didn't update the "DLinq Overview" documentation for the preview. The white paper states: "Although the samples in this document are shown in C#, DLinq can be used with the LINQ-enabled version of the VB.NET compiler as well." Current LINQ, DLinq, and XLinq documentation contains few—if any—VB code examples, so you're on your own when it comes to VB query syntax beyond simple Select ... From Alias In DataContext Where ... Order By ... operations.

Following are a few of the problems I encountered when writing the article and the accompanying VB 9 sample project, which has about 2,000 lines of original source code, plus an 850-line DataContext class generated by SqlMetal.exe. Clearly, the problems could be the result of cockpit error on my part. I'll post solutions to the problems, if and when they're solved. I'll also post links to the article and its VB 9 source code when they're available from FTPOnline.com.

DataContext.GetQueryText(objQuery) Doesn't Return the SQL String Amanda Silver, a VB Team program manager, provided this reply to a question posed during the February 2006 DLinq chat about the T-SQL generated for DLinq queries:

"A: The magic API is GetQueryText on the DataContext object. And you pass in an IQuery object."

Amanda didn't provide a code example for the "magic API." Windows Live Web Search returned only three distinct hits for DLinq GetQueryText and Google returned only two. One hit of both searches unearthed the following:

Keith Farmer, a Microsoft developer on the DLinq team (and an Expert for the DLinq chat), suggested the following code to return the generated T-SQL statement from a C# 3.0 DLinq query in response to question posted in Channel 9's "Anders Heljsberg - LINQ" video forum:

var customers = from c in db.Customers
select c; // base query
IQuery query; // final query
if (filterByCity)
{
   string cityName = "London";
   // modify the base query
   query = from c in customers
   where c.City == cityName select c;
else
   // use the base query directly
   query = customers;
// base query
Console.WriteLine(db.GetQueryText(customers));
// correctly modified query
Console.WriteLine(db.GetQueryText(query));

The preceding code executes as expected with the original PDC technical preview upgraded to the RTM version of VS 2005. Here's the corresponding—but simplified—VB 9 translation:

Dim qryCustomers As IQuery
Dim strSQL As String
Dim objCustomers = Select c _
   From c In dbNwind.Customers _
   Where c.City = "London"
qryCustomers = objCustomers
strSQL = dbNwind.GetQueryText(qryCustomers)

The two examples' db and dbNwind DataContexts bind the Northwind sample database running on SQL Server 2005 or SQL Express. The VB 9 code fails when attempting to execute the qryCustomers = objCustomers instruction. The error message is Unable to cast object of type '<select>d__b`2[VbLinqDemo.Nwind.Customers, VbLinqDemo.Nwind.Customers]' to type 'System.Data.DLinq.IQuery'. There are even fewer search hits from linq IQuery. The obvious question is "Where or how do you get the IQuery object for GetQueryText's argument?" Logging T-SQL Sent to the Server A partial workaround for the GetQueryText problem is to capture the T-SQL statements sent to the server with a TextWriter object assigned to the DataContext.Log property. The most common log destination is the Console.Out object, but you can use add following code immediately after instantiating the DataContext object to assign a StringBuilder for logging:

dbNwind = _
  New Nwind.Northwind(My.Settings.NwindConn)
Dim sbLog As New StringBuilder
Dim twLog As TextWriter
twLog = New StringWriter(sbLog)
dbNwind.Log = twLog

sbLog contains T-SQL for parameterized SELECT queries and INSERT operations. But UPDATE and DELETE statements are missing (or I'm not privy to the legerdemain needed to capture them.) Alternatively, use SQL Server Profiler to view T-SQL statements and their parameter values. Issues with Bound Object Inserts in DataGridViews Databinding is critical to the success of LINQ and DLinq in mainstream Windows and Web client development. Smart client developers must be able to bind DLinq DataContext objects to design-time components—such as BindingSources—and DataGridViews, TextBoxes, and DateTimePickers. Web page designers need similar capabilities for ASP.NET server controls. To achieve ubiquity, DataContext objects must offer binding flexibility and performance equal to—if not better than—strongly typed DataSets.

One of the two Windows forms of the VbLinqDemo.sln sample project contains a three-level, hierarchical set of DataGridView controls bound to the Customers, Orders, and OrderDetails Table members of a DataContext object generated by SqlMetal.exe from the Northwind sample database.

I used the Data Source Configuration Wizard to generate an Object Data Source from the Nwind.Northwind DataContext class, and dragged the Customers node from the Data Sources window to the form to generate CustomersBindingSource, CustomersDataNavigator, and CustomersDataGridView controls. Dragging the Orders node (under the Customers node), which represents the Orders EntitySet of the Customers member, to the form added the OrdersBindingSource and OrdersDataGridView. Finally, dragging the OrderDetails node (under the preceding Orders node), which represents the OrderDetails EntitySet of the Orders member, to the form added the OrderDetailsBindingSource and OrderDetilsDataGridView.

An empty AddNew row appeared in the Orders and Order Details DataGridViews, but not in the CustomersDataGridView. Setting CustomersBindindingSource.AllowNew = True generates an AddNew row with the CustomerID column set read-only. You can type values in the remaining columns, but you can't persist a Customers member instance for the new row. Populating the cells with a DefaultValuesNeeded event handler doesn't solve the persistance problem. Clicking anywhere in the empty AddNew row of the Orders or OrderDetails DataGridView throws an untrappable "Value does not fall within the acceptable range" exception. Populating the rows with a DefaultValuesNeeded event handler doesn't work and thus doesn't solve the problem.

The incomplete workaround is to write code to populate the new objects with code similar to the following:

Private Sub AddNewCustomer()
   'Create some new OrderDetails instances
   Dim objLineItem1 As New Nwind.OrderDetails
   With objLineItem1
      .ProductID = 1
      .Quantity = 12
      .UnitPrice = 22.25
      .Discount = 0.05
   End With
   ...

   'Create a new Order instance
   'This requires changing Orders.EmployeeID and
   'Orders.ShipVia field data types from
   'Nullable(Of Integer) to Integer
   'Otherwise these field values remain Null,
   'regardless of the property values set
   Dim objOrder As New Nwind.Orders
   With objOrder
      .CustomerID = "ABOGC"
      .EmployeeID = 1
      .OrderDate = Now
      .RequiredDate = Now.AddDays(14)
      .ShippedDate = Nothing
      .ShipVia = 2
      .Freight = 15.15
      .ShipName = "A Bogus Customer, Inc."
      .ShipAddress = "1000 Broadway"
      .ShipCity = "Oakland"
      .ShipRegion = "CA"
      .ShipPostalCode = "94608"
      .ShipCountry = "USA"
      .OrderDetails.Add(objLineItem1)
      ...
   End With

   'Add the new Customer instance
   Dim objCustomer As New Nwind.Customers
   With objCustomer
      .CustomerID = "ABOGC"
      .CompanyName = "A Bogus Customer, Inc."
      .ContactName = "Joe Bogus"
      .ContactTitle = "President and CEO"
      .Address = "1000 Broadway"
      .City = "Oakland"
      .Region = "CA"
      .PostalCode = "94608"
      .Country = "USA"
      .Phone = "(510) 555-1212"
      .Fax = "(510) 555-1213"
      .Orders.Add(objOrder)
   End With
   dbNwind.Customers.Add(objCustomer)
   dbNwind.SubmitChanges()
End Sub

The most important issue with creating new Orders object instances is the need to modify the data type of the class's EmployeeID and ShipVia fields from Nullable(Of Integer) to Integer. Foreign key constraints with the Employees and Shippers tables preclude NULL values.

The need to add a new Orders object for an existing customer to the Customers.Orders EntitySet, rather than to the Table(Of Orders) object directly is an anomaly. Doing the latter operation adds an orphan Orders record with a NULL CustomerID value. The same is likely to be true for adding a new Order Details record, but hasn't been tested.

Adding new Customers or Orders records in a grid isn't a common practice in the real world. Thus, the sample project's literal values would be replaced by values supplied from TextBoxes, ComboBoxes, DateAndTimePickers, or the like on another form. The preceding code assumes a business rule that requires a new customer to process an initial order with at least one line item.

Deletion of Rows in Bound DataGridViews

You can delete Orders and OrderDetails instances and their corresponding table rows by selecting the row in the DataGridView and pressing Delete. The DataGridView.UserDeletingRow event handler displays a message box that lets the user cancel the pending deletion. The DataGridView.UserDeletedRow event handler contains or calls the code to delete the entity and related entities, if any.

Deleting a parent instance deletes it's child (and grandchild) entities, if present. However, the deletion process differs depending on the position of the deleted instance in the DataContext hierarchy. As an example, deleting a Customers entity and its related Orders and OrderDetails entities requires code similar to the following:

Dim objNewCust = (Select c _
   From c In dbNwind.Customers _
   Where c.CustomerID = "ABOGC").First()
dbNwind.Customers.Remove(objNewCust)
dbNwind.SubmitChanges()

Deleting one of two or more OrderDetails entities from an order requires the following code:

Dim objDelDetail = (Select d _
   From d In dbNwind.OrderDetails _
   Where d.OrderID = delOrderID AndAlso _
      d.ProductID = delProductID).First()
dbNwind.OrderDetails.Remove(objDelDetail)
dbNwind.SubmitChanges()

where the OrderDetailsDataGridView.UserDeletingRow event handler returns the delOrderID and delProductID values.

However, deleting one of two or more Orders entities requires invoking the dbNwind.SubmitChanges() method only. These apparent inconsistencies seem to be anomalies.

Persisting edits of existing entities appears to require invoking the dbNwind.SubmitChanges() method only.

Design-Time Errors Occur with an OrderBy Clause

The sample project's other form (VBLinqDemo.vb) demonstrates creating singleton and multi-row joins between a DLinq entity (Orders) and XLinq in-memory XML documents (Customers, LineItems, Products, Suppliers, Employees, and Shippers). The joined entities use VB 9 XML literal code to generate a complex, hierarchical XML SalesOrders document.

Adding the Order By o.OrderID Descending _ clause to the following code

Dim docSalesOrders As XDocument = _
<?xml version="1.0" standalone="yes"?>
<SalesOrders>
  <%= Select _
    <SalesOrder _
      EmployeeID=<%= o.EmployeeID %>
      ShipVia=<%= o.ShipVia %>>
      ...
    </SalesOrder> _
    From o In dbNwind.Orders _
    Where o.ShipCountry = strCountry _
    Order By o.OrderID Descending _
  %>
</SalesOrders>

displays an "Order By clause in query expression is invalid" error and the project won't compile.

Large Amounts of XLinq Code Confound the Debugger

Setting a breakpoint on a valid instruction in the code for the CreateXmlFromJoins procedure behind the VBLinqDemo.vb form and pressing F5 throws an exception with the following (typical) message: "The following breakpoint cannot be set: At VbLinqDemo.vb, line 917 character 13, ('Create from Joins' line 16). The Common Language Runtime was unable to set the breakpoint." (Windows Live Search returns a link to the Visual Studio Debugger Breakpoint Helper.)

Clicking OK to dismiss the message opens another message box that states: "Execution has stopped at the beginning of the program because one or more breakpoints could not be set." (No search returns.)

The lack of search hits indicates that this problem occurs infrequently.

Conclusion

LINQ and its DLinq and XLinq expressions are in the pre-beta stage, so it's not surprising to find that these VB 9 extensions have anomalies and problems. However, it would be useful if documentation covering the technical previews was more forthcoming about limitations and issues. Updated overview whitepapers with non-trivial VB 9 sample code also would be useful.

Leave a comment or e-mail me at Roger_Jennings@compuserve.com if you have questions, suggestions, or solutions.

Technorati: LINQ DLinq XLinq Visual Basic 9 VB 9 Orcas Visual Studio 2005 VS2005 Visual Studio Visual Basic Express