There are subtle differences between VB and C# in terms of nullability. This caused a significant difference in the TSQL generated on even simple LINQ queries. Consider the following query from Northwind's Orders table where the Freight column is generated as a Nullable(Of Integer) type:

 Dim filtered = _
       From o In dc.Orders
       Where o.Freight > 100 
       Select o

First the bad news: with the RTM of VB 2008, this query resulted in the following TSQL:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE (COALESCE(
    (CASE
        WHEN [t0].[Freight] > @p0 THEN 1
        WHEN NOT ([t0].[Freight] > @p0) THEN 0
        ELSE NULL
     END),@p1)) = 1

In this case, I've highlighted the relevant portion of the Where clause. Notice here the simple comparison translates into a COALESCE(CASE WHEN... construct. This was done due to VB's handling of nullable type comparisons under the covers.

AND NOW THE GOOD NEWS:

With the VB 2008 SP1, the difference in nullablity was removed by the SQLProvider used by LINQ to SQL. As a result the above LINQ query now generates the following TSQL:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[Freight] > @p0

Notice in this case the where clause direcly reflects the statement ffrom the LINQ query and the performance from SQL Server's perspective is GREATLY improved! Who says LINQ to SQL is dead?

The VS 2008 SP1 includes lots of new features (more than a typical service pack, but that's another matter). There are a number of smaller enhancements that could easily go un-noticed otherwise. One of these is to fix a bug in the way the VB compiler generates anonymous types.

In most cases, you will only notice this if you are binding an anonymous projection to a DataGridView or the ASP GridView. With these controls, you will find that the columns used to be generated alphabetically rather than in the order in which they were declared in the anonymous type projection. Consider the following query (using the Northwind Customers table of course):

With VS 2008 RTM, a grid bound to these results would display the columns in the following order unless you explicitly set the columns: Address, CompanyName, ContactName, ContactTitle. Notice here that they are listed alphabetically. Interestingly if you use the same query in C#, the columns would be displayed retaining the order of the projection.

To determine the cause for the difference, one must view the actual IL using ILDASM. Comparing the C# with VB IL, you can see that the C# compiler retains the order where-as VB alphabetized it. No, you can't use Lutz's Reflector this time because he too alphabetizes the properties from the IL.

This was true until VS 2008 SP1 which fixed this bug. Now, the anonymous types are generated in the order in which they were declared and the column ordering is retained. Running the same query we had above results in the following column ordering with VB 2008 SP1: CompanyName, ContactTitle, ContactName, Address.

Thanks to the VB team for fixing this bug in the compiler.

One of the key things that LINQ to SQL does for us is offers a quick way to fill a set of objects with data from a database. Typically this is done by setting up some mapping and calling the GetTable method on the DataContext. There are cases, particularly when you already have an infrastructure set-up to populate objects using a DbDataReader, where it would be nice if you could just populate the columns without the need to set-up a mapping.

The DataContext has a little known method called Translate which can take a DBDataReader as a parameter and fill an IEnumerable list of objects without the need for mapping structures. To use it, specify the type you want to populate and pass the instance of the reader and let it do it's magic. Here's a sample:

Using cn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)
  cn.Open()
  Using cmd As New SqlClient.SqlCommand("SELECT * FROM Region", cn)
    Dim reader = cmd.ExecuteReader()
    Using nwind As New NorthwindDataContext

      Dim RegionList = nwind.Translate(Of Region)(reader)

      For Each item In RegionList
        Console.WriteLine(" {0}: {1}", item.RegionId, item.RegionDescription)
      Next
    End Using
  End Using
End Using

In this case, we tell the context to translate the resultset of the reader into a generic IEnumerable(Of Region). There are number of caveats to keep in mind here:

1. The column name in the result set must correspond to the property name.The translation is quite forgiving however. It is not case sensitive.
2. If you have a row in the result set but no corresponding property, that row will be ignored (no exception will be thrown).
3. If you have a property in your object with no corresponding row in the result set, that property's value will not be set.
4. The translation must be done to a known type. You can not project into an anonymous type. You can return an object or simple IEnumerable if the type is not known and then use reflection to work with the results.
5. Since the translate method returns an IEnumerable, make sure not to close the connection or the reader before iterating over the results.

The Translate method supports 3 overloads as follows:

    Public FunctionTranslate(Of TResult)(ByVal reader As DbDataReader) As IEnumerable(Of TResult)
    Public Function Translate(ByVal reader As DbDataReader) As IMultipleResults
    Public Function Translate(ByVal elementType As Type, ByVal reader As DbDataReader) As IEnumerable

Translate does not require any mappings to be set. It simply sets the public property values based on the column names. If you have business logic in your Property Set implementations, that logic will run. As far as I can see, there is no way to instruct Translate to use the private storage field instead of the public property.

I had the honor of participating in Chris Williams "Nine Questions" project. If you are interested in getting to knowing a bit more about me other than just how I thinq, check it out on his blog.

Former Atlanta luminary, Kirk Allen Evans reminded me about the Windows Live translator service that I tried unsucessfully to add to this site a couple months ago. I was able to get it to work this time based on the code he posted. All you need to do is add the following to your site and the magic translator will be added with no extra work for you:

<!-- Translator -->
<div id="translator">
  <div id="trans">
    <br />
    <script type="text/javascript" src="http://translator.live.com/TranslatePageLink.aspx?pl=en"></script>
    <br />
  </div>
</div>

I did take the liberty of correcting the code to make it XHtml complant by adding the end slash on the br tags. You can try it out by selecting the drop down list on the right hand site of this site that says "Translator", selecting the language you want and clicking the -> button.

As always, let me know what you Thinq.