Sanitize parameter names to account for VB closure members

[roji]

Parameters don't work when using EF with VB.NET against PostgreSQL:

Imports ConsoleApp2.ConsoleApp1

Module Program
    Sub Main(args As String())
        Dim email = "[email protected]"
        Dim db As New MyDbContext
        db.Database.EnsureDeleted()
        db.Database.EnsureCreated()
        db.Add(New Person With {.Id = Guid.NewGuid(), .Email = email})
        db.SaveChanges()
        
        Dim p = db.Person.Single(Function(x) x.Email = email)
        
        Console.WriteLine(p.Email)
    End Sub
End Module

The reason is that captured closure variables in VB.NET seem to generated by the VB compiled as a field access with names such as $VB$Local_email; this causes the following command to be executed with Npgsql:

SELECT p."Id", p."Email"
FROM "Person" AS p
WHERE p."Email" = @__$VB$Local_email_0
LIMIT 2

Now, PostgreSQL has positional parameter placeholders ($1, $2) and not named ones (@p1, @p2), but as a legacy feature, Npgsql rewrites named placeholders to positional ones (e.g. to ease transitioning from SQL Server and other DBs). Unfortunately, the dollar character is special in PostgreSQL, causing @__$VB$Local_email_0 to not be identified as a parameter placeholder.

The proper solution here is to modify EF to use positional placeholders directly when using PG (#27377) - but that's not trivial. The $VB$ is generally useless in the database parameters even where it's not harmful (e.g. SQL Server). Our funcletizer already performs some parameter name sanitization, trimming out characters after >; we should also trim out anything up to the 1st dollar sign.

Originally flagged by @sorenjakobsen in npgsql/efcore.pg#3109.

/cc @vonzshik @NinoFloris

[sorenjakobsen]

Thank you for the very quick fix, much appreciated. When can we expect this to be released - it will not be in 9.0?

I know that not so many people use VB.NET and that C# and VB.NET are largely interchangeable, but I've been using both languages for more than 10 years and I really find VB.NET an indispensable tool for working with XML (and HTML), since it has XML literals (unlike C#). I hope you will prioritize VB.NET support.

[roji]

@sorenjakobsen this has been merged, so it will be release with 9.0 - you can already test it via the daily build, and also use an upcoming 9.0 preview version.

[sorenjakobsen]

Glad to hear that, thank you! I will start testing then.

[sorenjakobsen]

@roji if I run the same code with 9.0.0-preview.3.24151.3 (or 9.0.0-preview.2.24128.4) I get an exception - System.MissingMethodException: 'Method not found'
I get this exception even if I don't use parameters.
I guess I need .NET 9 to use the efcore 9 preview (?), but I get the same exception with .NET 9 SDK preview (https://dotnet.microsoft.com/en-us/download/dotnet/9.0) installed (though am not able to select 'net9.0' as 'TargetFramework' in .vbproj file).

[roji]

@sorenjakobsen this error is the result of mixing incompatible versions of EF itself (where the fix is) and EFCore.PG. You'll probably have to wait for preview.3 to come out (both EF and EFCore.PG) - in a bit over a month.

[sorenjakobsen]

@roji hi Roji, is there not a daily (preview) build available for efcore.pg? would like to start testing with the latest efcore preview:)

[roji]

@sorenjakobsen take a look at the milestone above - the fix should be in 9.0.0-preview.2 and above.

[sorenjakobsen]

@roji I guess something also needs to be updated on the npgsql side? I am using v. 8.0.2 of Npgsql.EntityFrameworkCore.PostgreSQL and with the latest efcore preview I am getting this error:

System.TypeInitializationException: 'The type initializer for 'Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlBigIntegerTypeMapping' threw an exception.'

[roji]

@sorenjakobsen this has been fixed for 9.0 (in preview.2), but not backported to 8.0. We can consider backporting the fix, but usually we need a few more users to be affected for that to be justified etc.

[sorenjakobsen]

@roji thanks, it seems to work now! I was just using the wrong 'package source', so didn't see that there was also a later (preview) version of Npgsql.EntityFrameworkCore.PostgreSQL available.