LINQ on Mobile Devices – Some Big Surprises from Visual Basic … Part 2 (continuing the story of improved SqlCeResultSet LINQ query efficiency)

As you'll recall from my last post, we were talking about some of Visual Basic's unexpected behavior when compiling and resolving method signatures in LINQ queries. Hopefully, most of you will also recall, where this all started … the webcast discussion of using extension methods to efficiently query data held in a SQL Server Compact (SSC) database with LINQ rather than incur the overhead of loading that data into a DataSet. This post shows the difference in resource usage between using DataSets for LINQ queries and direct database LINQ queries through "smart" SqlCeResultSet handing.

So building on our observations in my previous post as to how VB is handling LINQ queries and the difficulties we observed that this creates when using custom extension methods, the following describes how I worked around the issues so we can employ SqlCeResultSet LINQ query optimizations in VB similar to those we observed in C#.

(see this post for C# code examples and this post for VB code examples)

A Quick Review of the Problem

SqlCeResultSet implements the older object-based IEnumerable interface rather than the more modern generic IEnumerable(of T) interface. We observed that when a class implements the older IEnumerable interface that the VB compiler appears to force the creation of a IEnumerable(of Object) wrapper over the class reference by way of a call to the Cast(of Object) extension method. As a result, the reference resolves to the built-in Where extension method that accepts IEnumerable(of T) rather than to our custom Where extension method that accepts a SqlCeResultSet reference.

Working Out a Solution

When working out the optimized SqlCeResultSet LINQ query solution, I wanted to insure that the solution had minimal impact on how you write your code. I wanted to be able to use the SqlCeResultSet class just as it is (no new derived class, etc.) and I wanted to be able to use the same LINQ query syntax that we use for other LINQ queries.

With that in mind, the following is the solution I was able to work out for VB. It is certainly not the only solution to the problem and may not be the best solution. I do feel, however, that it is a reasonable solution.

Getting past the IEnumerable Problem

Because the VB compiler appears to intercede and immediately wrap the SqlCeResultSet instance in a IEnumberable(of Object) collection before resolving the Where extension method, I had to identify an easy way to short-circuit this behavior. I figured the easiest way to do this was to create a simple wrapper class that would basically allow me to hide the SqlCeResultSet from the VB compiler long enough for the compiler to select my custom Where extension method rather than the built-in IEnumberable(ofT) version.

To do this, I defined the VBLinqHandlingWorkAround class…

Public Class VBLinqHandlingWorkAround
Implements IEnumerable(Of SqlCeUpdatableRecord)

Public Sub New(ByVal resultSet As SqlCeResultSet)
_resultSet = resultSet
End Sub

Private _resultSet As SqlCeResultSet

Public ReadOnly Property ResultSet() As SqlCeResultSet
Get
Return _resultSet
End Get
End Property

Public Function GetEnumerator() As System.Collections.Generic.IEnumerator(Of System.Data.SqlServerCe.SqlCeUpdatableRecord) Implements System.Collections.Generic.IEnumerable(Of System.Data.SqlServerCe.SqlCeUpdatableRecord).GetEnumerator
Return Nothing
End Function

Public Function GetEnumerator1() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
Return Nothing
End Function
End Class

I then defined an extension method, GetVBLinqHandlingWorkAround…

<Extension()> _
Public Function GetVBLinqHandlingWorkAround(ByVal resultSet As SqlCeResultSet) As VBLinqHandlingWorkAround
Debug.WriteLine("GetVBLinqHandlingWorkAround(this SqlCeResultSet resultSet")
Return New VBLinqHandlingWorkAround(resultSet) ' Wraps the SqlCeResult
End Function

With the VBLinqHandlingWorkAround class and the GetVBLinqHandlingWorkAround extension method in place, I modified the actual LINQ query just slightly…

Dim records = From order In resultSet.GetVBLinqHandlingWorkAround() _
Where order("Ship Country") = "UK"

By calling the GetVBLinqHandlingWorkAround method, the type being queried is the VBLinqHandlingWorkAround class which implements IEnumerable(of SqlCeUpdatableRecord). By implementing the strongly-typed IEnumerable(of T), the VB compiler does not wrap the class reference in a new collection as it did with the SqlCeResultSet. So the type being queried is VBLinqHandlingWorkAround which internally contains a reference to the SqlCeResultSet.

Notice that the VBLinqHandlingWorkAround class doesn't actually implement an enumerator; we have to provide the empty IEnumerable(of T) implementation so that the VB compiler will accept that the VBLinqHandlingWorkAround class can be queried. As you recall from Example 1 in my previous post, without the interface implementation VB considers the type illegal to query even though there's a Where extension method available for the type (C# does not enforce such a requirement).

Accessing the SqlCeResultSet Custom Where Extension Method

Because the type being queried remains the VBLinqHandlingWorkAround class, we can provide a custom Where extension method that accepts the VBLinqHandlingWorkAround reference…

<Extension()>
_Public Function Where(ByVal workAroundReference As VBLinqHandlingWorkAround, _
ByVal theFunc As Func(Of SqlCeUpdatableRecord, Boolean)) _
As IEnumerable(Of SqlCeUpdatableRecord)
Dim resultSet As SqlCeResultSet = workAroundReference.ResultSet
Return resultSet.Where(theFunc)
End Function

Notice what this custom Where extension method does. It simply accesses the SqlCeResultSet reference contained within the VBLinqHandlingWorkAround instance and then uses the method syntax to call the SqlCeResultSet Where extension method. And viola, we're now using our "smart" SqlCeResultSet LINQ query implementation.

When I first designed the SqlCeResultSet optimized query solution I was working in C#. One of the things I had found so elegant about the optimized SqlCeResultSet query solution was the fact that it didn't require any changes to the actual LINQ query. As I so far understand the behavior of the VB compiler when working with LINQ queries, we're not able to achieve that same degree of invisible integration. That said, given the huge payoff in query performance and resource efficiency when using the "smart" SqlCeResultSet query solution, having to include the GetVbLinqHandlingWorkAround method call in the query is a pretty small price to pay. J