VB.NET SqlExpression "Where" on a String Property causes exception

If the POCO or the SqlExpression code is written in VB.NET I can’t use a “WHERE” on a STRING property when building a SQLExpression (however, integers and other simple data types work)

eg:

        Dim Customers As New List(Of Customer)()
        Customers = db.[Select](Of Customer) _
                    (db.From(Of Customer)() _
                    .Where(Function(c) c.Name = "John"))

This works great if the POCO & actual SqlExpression code are in C#, but when I run it in VB.NET (like the above code), I get the following error:

Additional information: variable ‘c’ of type ‘Customer’ referenced from scope ‘’, but it is not defined

I can work around the issue in a couple ways, but I’d rather use the standard syntax and I think this is a bug.

Workaround:

Dim Customers As New List(Of Customer)
Customers = db.[Select](Of Customer) _
        (db.From(Of Customer)() _
        .Where(Function(c) {"John"}.Contains(c.Name)))

Or I can simply use OrmLite’s T-SQL support, which is not ideal either.

Any idea why this would be happening or is this just a bug?

Thanks

hmm not sure about VB.NET, but the OrmLite API that this calls is:

Where(Expression<Func<T, bool>> predicate)

In C# when you call it with

.Where(c => c.Name == "John")

It passes in an Expression<Func<T, bool>> that can be used to parse the expression provided which we can then analyze and rewrite into SQL fragments, so I’m then assuming that the VB.NET version:

.Where(Function(c) c.Name = "John"))

Isn’t equivalent? and perhaps is just passing in the opaque delegate instead of the Expression? Do you know if VB.NET has different/special syntax for providing a lambda Expression<T> tree instead of the delegate?

I’ve had a deeper look into this and the issue is similarly described in this answer, essentially it’s due to VB.NET converting this Expression:

x => x.Name == "Foo"

Into the C# equivalent of:

x => Microsoft.VisualBasic.CompilerServices.Operators.CompareString(x.Name, "Foo", False) == 0

Which wasn’t catered for, but I’ve just added support for this converted expression in this commit which is available from v4.0.41+ that’s now available on MyGet.

Hopefully VB.NET has very limited cases where it rewrites expressions like this as it bound to cause issues in every LINQ parser not explicitly written to support it.

That worked great, thank you very much for looking into it (when I realize vb isn’t exactly a top priority) and also explaining your solution

So is this fix going to make it into the official channel any time soon? We keep checking every version and it doesn’t seem it’s ever officially made it to the source code.

The commit was added in v4.0.41 on MyGet, so should be available in the v4.0.42 and v4.0.44 releases on NuGet.

Okay so

    Dim Customers As New List(Of Customer)()
    Customers = db.[Select](Of Customer) _
                (db.From(Of Customer)() _
                .Where(Function(c) c.Name = "John"))

actually does work fine in the .44 release, I guess I assumed that if = is fixed, then <> would also be fixed, but it exceptions with the same error as = used to.

For example, the following code in vb.net will throw an exception, even on the .44 release:

    Dim Customers As New List(Of Customer)()
    Customers = db.[Select](Of Customer) _
                (db.From(Of Customer)() _
                .Where(Function(c) c.Name <> "John"))

This issue requires explicit special casing to support, i.e. it wont naturally work for different operators. I’ve just added support for <> in VB.NET in this commit which is now available from v4.0.45+ that’s now available on MyGet.

Re: versioning, if it’s available in v4.0.41, it will always be available in subsequent versions (unless it was explicitly removed), i.e. there aren’t any parallel versions, just the pre-release versions on MyGet which ends with an odd number (e.g. v4.0.45) and even number versions which are only published on NuGet (e.g. v4.0.44).

1 Like

This problem appears to be back in version 4.0.56, is that true?

I checked the source code and it looks like the patch is still there in the newest version, so it may not be hitting that case for some reason, or throwing an error somewhere else in the code.

Yeah looks like it was left over from switching the rest of the code over to use parameterized statements, should now be fixed in this commit.

This change is available from v4.0.57 that’s now available on MyGet.

Thanks. We realized it was actually a slightly different problem. The ORM query: c.Field <> "" will generate the SQL ("Field" <>) instead of ("Field" <> '')

The latest changes now generates a parameterized query so this should work as well.