VB Linq query on nullable column throws sql exception

[xavierzwirtz]

I am attempting to use the sample NorthwindDB order class from a VB.Net project. Anytime I execute a where linq query against the Order::OrderDate property, BLToolkit throws an exception from the SQL Server saying that there is incorrect syntax. If I remove the ? from OrderDate, making it a nonnullable property, the query executes perfectly fine.

The working C# code:

from ord in Order
where ord.OrderDate == new DateTime(1997, 11, 14)
select ord.OrderID

Executes this SQL on the server:

SELECT
    [t1].[OrderID],
    [t1].[CustomerID],
    [t1].[EmployeeID],
    [t1].[OrderDate],
    [t1].[RequiredDate],
    [t1].[ShippedDate],
    [t1].[ShipVia],
    [t1].[Freight],
    [t1].[ShipName],
    [t1].[ShipAddress],
    [t1].[ShipCity],
    [t1].[ShipRegion],
    [t1].[ShipPostalCode],
    [t1].[ShipCountry]
FROM
    [Orders] [t1]

The failing VB.Net code:

From order In db.Order
Where order.OrderDate = #11/14/1997#
Select order.OrderID

Executes this SQL on the server:

SELECT
    [order1].[OrderID]
FROM
    [Orders] [order1]
WHERE
    IIF(([order1].[OrderDate] = @p1) IS NULL, 0, [order1].[OrderDate] = @p1) = 1

There seem to be two bugs here, first one being that the VB.Net expression is generating invalid sql, second one being that C# is doing no server side filtering. Is this a bug in BLToolkit, or am I using it incorrectly?

[jogibear9988]

post a little bit more of your c# code, i think you doing it wrong in c#!

wich version are you using? newest git?

[xavierzwirtz]

Full C# code:

[Test]
public void SearchCondition3([IncludeDataContexts("Northwind")] string context)
{
    using (var db = new NorthwindDB())
    {

        var cSharpResults = (from ord in Order
                             where ord.OrderDate == new DateTime(1997, 11, 14)
                             select ord.OrderID).ToList();

        var vbResults = (VisualBasicCommon.SearchCondition3(db)).ToList();

        AreEqual(
            cSharpResults,
            vbResults);
    }
}

Full VB Code:

 Public Function SearchCondition3(ByVal db As NorthwindDB) As IEnumerable(Of Integer)
        Return _
            From order In db.Order _
            Where order.OrderDate = #11/14/1997#
            Select order.OrderID
    End Function

SearchCondition3 is being called from the C# code, checking to make sure they return the same results. This is running on top of commit 7dbd5b8 from git.

[jogibear9988]

i think in c# you should also write db.Order, or where does this order field com from?

Von meinem iPhone gesendet

Am 24.06.2014 um 17:53 schrieb VoiceOfWisdom notifications@github.com:

Full C# code:

[Test]
public void SearchCondition3([IncludeDataContexts("Northwind")] string context)
{
using (var db = new NorthwindDB())
{

    var cSharpResults = (from ord in Order
                         where ord.OrderDate == new DateTime(1997, 11, 14)
                         select ord.OrderID).ToList();

    var vbResults = (VisualBasicCommon.SearchCondition3(db)).ToList();

    AreEqual(
        cSharpResults,
        vbResults);
}

}
Full VB Code:

Public Function SearchCondition3(ByVal db As NorthwindDB) As IEnumerable(Of Integer)
Return _
From order In db.Order _
Where order.OrderDate = #11/14/1997#
Select order.OrderID
End Function
SearchCondition3 is being called from the C# code, checking to make sure they return the same results. This is running on top of commit 7dbd5b8 from git.

—
Reply to this email directly or view it on GitHub.

[xavierzwirtz]

Ha! Very good catch. That makes the C# code run this query instead:

-- DECLARE @p1 DateTime

-- SET @p1 = 11/14/1997 00:00:00

SELECT
    [ord].[OrderID]
FROM
    [Orders] [ord]
WHERE
    [ord].[OrderDate] = @p1

The VB code is still behaving the same way however.

[jogibear9988]

maybe you need to look the expression wich will be created in the debugger, in vs2013 you can display expression trees, and look whats different.

or use this http://exprtreevisualizer.codeplex.com

Von meinem iPhone gesendet

Am 25.06.2014 um 19:49 schrieb VoiceOfWisdom notifications@github.com:

Ha! Very good catch. That makes the C# code run this query instead:

-- DECLARE @p1 DateTime

-- SET @p1 = 11/14/1997 00:00:00

SELECT
[ord].[OrderID]
FROM
[Orders] [ord]
WHERE
[ord].[OrderDate] = @p1
The VB code is still behaving the same way however.

—
Reply to this email directly or view it on GitHub.

[xavierzwirtz]

Grabbed the generated expression tree from the C# and VB code.

C#

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Where(
        .Constant<BLToolkit.Data.Linq.Table`1[Data.Linq.Model.Northwind+Order]>(Table(Order)),
        '(.Lambda #Lambda1<System.Func`2[Data.Linq.Model.Northwind+Order,System.Boolean]>)),
    '(.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>))

.Lambda #Lambda1<System.Func`2[Data.Linq.Model.Northwind+Order,System.Boolean]>(Data.Linq.Model.Northwind+Order $order) {
    $order.OrderDate == (System.Nullable`1[System.DateTime]).New System.DateTime(
        1997,
        11,
        14)
}

.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>(Data.Linq.Model.Northwind+Order $order) {
    $order.OrderID
}

VB

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Where(
        .Constant<BLToolkit.Data.Linq.Table`1[Data.Linq.Model.Northwind+Order]>(Table(Order)),
        '(.Lambda #Lambda1<System.Func`2[Data.Linq.Model.Northwind+Order,System.Boolean]>)),
    '(.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>))

.Lambda #Lambda1<System.Func`2[Data.Linq.Model.Northwind+Order,System.Boolean]>(Data.Linq.Model.Northwind+Order $order) {
    $order.OrderDate == (System.Nullable`1[System.DateTime]).New System.DateTime(
        1997,
        11,
        14) ?? False
}

.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>(Data.Linq.Model.Northwind+Order $order) {
    $order.OrderID
}

The only difference I am seeing is the ?? False on the VB code. From my googling the ?? operator is a null
coalescing operator, saying if the prior expression evaluates to null return false. It seems incredibly bizarre for that
to ever be possible in that expression, due to that just being a date comparison.

[jogibear9988]

maybe than your error is a compiler error, since i think same code should return the same expression!

maybe you ask on stack overflow if there is a bug known, or if its a bug in our understanding! if its a vb bug, maybe open a ticket at microsoft connect

Von meinem iPhone gesendet

Am 25.06.2014 um 21:55 schrieb VoiceOfWisdom notifications@github.com:

Grabbed the generated expression tree from the C# and VB code.

C#

.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Where(
.Constant<BLToolkit.Data.Linq.Table1[Data.Linq.Model.Northwind+Order]>(Table(Order)), '(.Lambda #Lambda1<System.Func2[Data.Linq.Model.Northwind+Order,System.Boolean]>)),
'(.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>))

.Lambda #Lambda1<System.Func2[Data.Linq.Model.Northwind+Order,System.Boolean]>(Data.Linq.Model.Northwind+Order $order) { $order.OrderDate == (System.Nullable1[System.DateTime]).New System.DateTime(
1997,
11,
14)
}

.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>(Data.Linq.Model.Northwind+Order $order) {
$order.OrderID
}
VB

.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.Where(
.Constant<BLToolkit.Data.Linq.Table1[Data.Linq.Model.Northwind+Order]>(Table(Order)), '(.Lambda #Lambda1<System.Func2[Data.Linq.Model.Northwind+Order,System.Boolean]>)),
'(.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>))

.Lambda #Lambda1<System.Func2[Data.Linq.Model.Northwind+Order,System.Boolean]>(Data.Linq.Model.Northwind+Order $order) { $order.OrderDate == (System.Nullable1[System.DateTime]).New System.DateTime(
1997,
11,
14) ?? False
}

.Lambda #Lambda2<System.Func`2[Data.Linq.Model.Northwind+Order,System.Int32]>(Data.Linq.Model.Northwind+Order $order) {
$order.OrderID
}
The only difference I am seeing is the ?? False on the VB code. From my googling the ?? operator is a null
coalescing operator, saying if the prior expression evaluates to null return false. It seems incredibly bizarre for that
to ever be possible in that expression, due to that just being a date comparison.

—
Reply to this email directly or view it on GitHub.

[xavierzwirtz]

Good idea, Ill ask on stack overflow to see if anyone else has seen this. Thanks for your help so far, didn't know you could log the expression tree like that.

[xavierzwirtz]

Question asked here.

[xavierzwirtz]

Someone answered my question on stack overflow, looks like this is bizarre handling for legacy VB code. Can BLToolkit be updated to handle this?

[jogibear9988]

Maybe you also create a issue at https://github.com/linq2db/linq2db because it has nearly the same expression parsing code...

[xavierzwirtz]

Does BLToolkit use that library for expression parsing?

[jogibear9988]

no, it is a lib from the bltoolkit author also for db access! but it's much newer, and shares most of the expression parsing code

[Firebie]

Now fails next code:

Additional information: The binary operator Equal is not defined for the types 'System.Nullable`1[System.Boolean]' and 'System.Boolean'.

/*
-- sql server
create table test5
(
  data int null
)
*/
  class test5
  {
    public bool? data;
  }

  using (var db = new DbManager())
  {
    var items = db.GetTable<test5>().Where(i => (i.data ?? false) == false).ToList();
  }