Bug: Querying by string field fails in VB .Net

[Trobeloo]

Bug Description

The following line raise Object reference Not Set To an instance Of an Object.
Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Country = "France").First

This line is working ok:
Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Id_Facture = 13624).First

Exception Message:

   '        System.NullReferenceException
        '        HResult = 0x80004003
        '  Message = Object reference Not Set To an instance Of an Object.
        '  Source = RepoDb
        'StackTrace:
        '        at RepoDb.QueryGroup.<> c__DisplayClass61_0.<GetFields>b__0(QueryGroup queryGroup)
        '   at RepoDb.QueryGroup.<> c__DisplayClass61_0.<GetFields>b__0(QueryGroup queryGroup)
        '   at RepoDb.QueryGroup.GetFields(Boolean traverse)
        '   at RepoDb.QueryGroup.Fix()
        '   at RepoDb.QueryGroup.Parse[TEntity](Expression`1 expression)
        '   at RepoDb.DbConnectionExtension.ToQueryGroup[TEntity](Expression`1 where)
        '   at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, Expression`1 where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
        '   at TestRepoDB.Module1.Main() in D: \Data\TestRepoDB\TestRepoDB\Module1.vb:line 17

Schema and Model:

CREATE TABLE [dbo].[Factures](
[Id_Facture] [int] NOT NULL,
[Country] varchar NULL,
CONSTRAINT [PK_Factures] PRIMARY KEY CLUSTERED

And also the model that corresponds the schema.

Public Class Factures
    Public Property Id_Facture() As Integer
    Public Property Country() As String
    Public Property Company_Num_Compta As Integer
End Class

Library Version:

RepoDb v1.12.8 and RepoDb.SqlServer v1.1.3

[Trobeloo]

My problem seems to be simmilar to the one described here : https://stackoverflow.com/questions/2413046/lambda-expressions-in-vb-net-what-am-i-doing-wrong

I tried to use Equals:
Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Country.Equals("France")).First

But it throw a not supported exception:

System.NotSupportedException
HResult=0x80131515
Message=Expression 'f => f.Country.Equals("France")' is currently not supported.
Source=RepoDb
StackTrace:
at RepoDb.QueryGroup.Parse[TEntity](Expression1 expression) in D:\Data\repos\mikependon\RepoDB\RepoDb.Core\RepoDb\QueryGroup\ParseExpression.cs:line 66 at RepoDb.DbConnectionExtension.ToQueryGroup[TEntity](Expression1 where) in D:\Data\repos\mikependon\RepoDB\RepoDb.Core\RepoDb\Extensions\DbConnectionExtension.cs:line 2456
at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, Expression1 where, IEnumerable1 fields, IEnumerable1 orderBy, Nullable1 top, String hints, String cacheKey, Nullable1 cacheItemExpiration, Nullable1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder) in D:\Data\repos\mikependon\RepoDB\RepoDb.Core\RepoDb\Operations\DbConnection\Query.cs:line 538
at TestRepoDB.Module1.Main() in D:\Data\TestRepoDB\TestRepoDB-VB\Module1.vb:line 17

Is there a solution or should I consider that RepoDb won't work with VB .Net.

[mikependon]

The Equals is not parsed by RepoDB if I remember. Can you try this f => f.Country == "France"?

[mikependon]

Apology, I am bit occupied recently, a preparation for the talk. Can you help me here. Do you have a very small Console application that could mimic this? A one that is written in VB.NET?

[mikependon]

If you as well can attached the SQL Script for Schema, that would be better. Just attached it here is zip file and I will give priority to it by tomorrow, then probably issue a beta if I find it really a bug. Thanks

Note: Your problem is a very common case, which I pre-assumed well supported by RepoDB. Of course, I had not tested that in VB.NET yet.

[Trobeloo]

Thank you Mike
Creating a database is not requiered since the error is in the parse of the query tree.
f => f.Country == "France" is a C# lambda expression the VB equivalent is Function(f) f.Country = "France"
I made a C# equivalent project and of course it works.

To reproduce create a console app in VB then paste the following code in the Module1.vb file

Module Module1

Dim ConStr As String
Dim Connection As SqlConnection

Sub Main()
    RepoDb.SqlServerBootstrap.Initialize()
    ConStr = "Data Source=srv;Initial Catalog=Database;Integrated Security=False;Persist Security Info=True;User ID=sa;Password=pwd;MultipleActiveResultSets=True;Application Name=Tests;"
    Connection = New SqlConnection(ConStr)

    'NOK Null Reference Exception
    Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Country = "France").First

    'NOK Not Supported Exception
    'Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Country.Equals("France")).First

    'Ok
    'Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Id_Facture = 13624).First

    'Ok
    'Dim myInvoice = Connection.Query(Of Factures)(Function(f) f.Company_Num_Compta = 4113427).First

    Console.WriteLine($"Invoice: {myInvoice.Id_Facture} Country: {myInvoice.Country}")

End Sub

End Module
Public Class Factures
Public Property Id_Facture() As Integer
Public Property Country() As String
Public Property Company_Num_Compta As Integer
End Class

Jacques

[mikependon]

Will get back to you probably EOW together with a new beta build. Is that okay with you?

[Trobeloo]

It is ok. I am in the process of testing RepoDB to replace Dapper in our internal apps.

[Trobeloo]

Hi Mike,

If it helps I found similar/related problems here (https://letmegooglethat.com/?q=linq+lambda+expression+vb+net+comparestring):

• The LINQ expression 'CompareString' could not be translated VB.NET dotnet/efcore#19592
• The LINQ expression 'Is Nothing' could not be translated VB.NET dotnet/efcore#19688
• CompareString in VB Project will be evaluated locally dotnet/efcore#10707

Jacques

[mikependon]

Initial investigation result, it seems to me that the expression you used is using the CompareString method underneath, in which is internal to VB.Net.

Let me see how I can handle this.

[mikependon]

While the issue is being fixed, can you for now utilize the Query Object like below?

Dim myInvoice = Connection.Query(Of Factures)(New QueryField("Country", "France")).FirstOrDefault

[mikependon]

Referencing the actual fix from here. Was tagged wrongly to the other issue.

[mikependon]

The fix is now available at v1.12.8-beta2.

[Trobeloo]

Problem is fixed with the beta
Thank you