Where clause optimized with VB 2008 SP1
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?