Extension UseFsharpTypes() interferes with the npgsql translation for where statement on jsonb columns

[thomrad]

Hello!

I have a postgres database which hosts json data in a table with a column using the datatype jsonb. When trying to query a specific json value, the translation into the correct sql statement is wrong.

Npgsql actually has that feature for the correct translation, but it won't get applied.

The setup I am using:

• PostgreSQL 16.0.1
• Npgsql.EntityFrameworkCore.PostgreSQL 7.0.11
• EntityFrameworkCore.FSharp 6.0.7
• FSharp.Core 7.0.0

I found out, that when I remove the UseFSharpTypes() extension from the DbContext class, the translation to the correct SQL statement works. If I enable the extension again, it fails.

According to the npgsql documentation, this is how it should be used (translated to F#):

let result = mydbcontext.Table.Where(fun x -> x.data.jsonfieldname = "value").ToList()

This is the correct translation:

SELECT s.data
FROM table AS s
WHERE s.data->>'jsonfieldname' = 'value'

This is the wrong translation when UseFSharpTypes() is enabled:

SELECT s.data
FROM table AS s
WHERE s.data = 'value'

The expected behaviour of course would be the correct translation from the npgsql library.

I have no clue what that issue could be, but my guess is, that the extension does not link to the npgsql library for translation but to the Microsoft version of the framework. I tried to put the extension methods in different orders - UseFSharpTypes() before UseNpgSql() - but that didn't help either.

I hope I gave all the infos for understanding the issue. Your help is very appreciated.

Thank you in advance Thomas