I've been trying to solve a little problem with VB.NET and the expression trees it likes to generate.
I have a simple test...
Public Sub ActiveRecord_Find_By_NonKey_Returns_123()
Dim orders = Order.Find(Function(item As Order) item.EmployeeID = 1)
Assert.Equal(Of Integer)(123, orders.Count)
End Sub
One would expect that to work but there is a problem with the item.EmployeeID = 1 bit and it is only a problem for VB.NET and not C#. VB.NET likes to be clever with the expression tree it compiles because of the fact item.EmployeeID is nullable! (Read blog relating to this)
The problem is that expression item.EmployeeID = 1 gets wraps with a Convert expression node. At which point the TSql generator gets completely lost and creates the following WHERE clause.
WHERE ([t0].[EmployeeID] = 1) <> 0
That snippet tends to fail somewhat when it is executed on the database.
So that seems to be a bug in SubSonic. Unfortunately trying to work out how/where to fix it is doing my head in!
It does get more interesting though.
Public Sub ActiveRecord_Find_By_NonKey_Returns_123_Linq()
Dim orders = From item In Order.All Where item.EmployeeID = 1 Select item
Assert.Equal(Of Integer)(123, orders.Count)
End Sub
But its WHERE clause is...
WHERE COALESCE(CASE WHEN (([t0].[EmployeeID] = 1)) THEN 1 ELSE 0 END, 0) <> 0
Well that seems sub-optimal! But at least it works.
Finally, there seems to be a work around for the original example above after reading the blog entry. Use the new coalesce operator (If) in VB.NET...
Public Sub ActiveRecord_Find_By_NonKey_Returns_123_Fix()
Dim orders = Order.Find(Function(item As Order) If(item.EmployeeID, 0) = 1)
Assert.Equal(Of Integer)(123, orders.Count)
End Sub
Which produces this WHERE clause...
WHERE (COALESCE([t0].[EmployeeID], 0) = 1)
A slightly more concise query though it there a difference between using ISNULL as opposed to COALESCE? Perhaps only in terms of compatibility across versions of SQL.
Basically, I want the first example to work as it is. I'd also like the second example to keep working but produce much cleaner SQL.
I would like to fix this myself but haven't gotten very far other than realise I have some issues with VB.NETs expression tree generation.