I need some assistance.
I'm running
.NET Core 3.1
Npgsql.EntityFrameworkCore.PostgreSQL 3.1.2
Microsoft.EntityFrameworkCore.Tools 3.1.2
against a postgres db
PostgreSQL 11.5
We moved from MSSQL to postgres, and previously this code was working:
_context.Users.SingleOrDefaultAsync(x => x.EmailAddress == emailadress);
With that said, now the data is case sensitive and e.g. comparing "[email protected]" with "[email protected]" stopped working.
One could change all to lower -strings, but as there is a lot of these kinds of comparisons, it would take some time to change this.
Then I found citext to the rescue, I thought.
I have altered the table/column and then there is the .net entity I'm a bit insecure of how fix.
I tried to add to the entity, based on this link: https://www.npgsql.org/efcore/mapping/general.html?tabs=data-annotations
[Column(TypeName = "citext")]
public string EmailAddress { get; set; }
but it didn't help.
I find this in the logs:
Executing DbCommand [Parameters=[@__emailadress_0='?'], CommandType='Text', CommandTimeout='30']
And I don't know if I should expect the CommandType='citext' here or not.
Do anyone have any input regarding this?
Thanks alot!
The user used to connect to the Postgres database didn't have default schema set. The data/tables used, didn't exist in the default schema set for the database. After the preferred/default schema was set on the user used, to connect to the database, the citext comparison started to work.
ALTER ROLE myUser IN DATABASE myDatabase SET search_path TO mySchema;