Postgres Autoquery Like is case sensitive

#1

Hello

I’m having an issue with %Like% case sensitivity on Postgres.

On northwind both requests return same result sets
https://northwind.netcore.io/query/orders?shipNameLike=%Alcools%
https://northwind.netcore.io/query/orders?shipNameLike=%alcools%

while on Postgres (AWS), results are not same due to case sensitivity.

Example:
https://api.poslovnisoftver.rs/artikli?nazivLike=%traka%
https://api.poslovnisoftver.rs/artikli?nazivLike=%Traka%

OrmLiteConfig.StripUpperInLike is not set.
Any assistance would be appreciated.

Kind regards

#2

Strange it should be querying with UPPER({Field}) LIKE UPPER({Value}) unless OrmLiteConfig.StripUpperInLike=true:

Can you check what SQL it’s using for the query.

You can override it to use PostgreSQL’s native case-insenstive like with:

var autoquery = new AutoQueryFeature { ... };
autoquery.ImplicitConventions["%Like%"] = "{Field} ILIKE {Value}";
Plugins.Add(autoquery);
#3

Thank you for your quick response.

SQL:
WHERE “artikli”.“naziv” LIKE :0
PARAMS: :0=%traka%

When i override it it works as expected.

Same behavior is with StartsWith, EndsWith and Contains but I wasn’t checking if it should behave like that.

Thank you.

(I’ve edited this answer since it contained my wrong observation)

#4

Then the behavior is StripUpperInLike=true which defaults to true in .NET Core builds.

To change it globally (if you want it to be the default in OrmLite):

OrmLiteConfig.StripUpperInLike=false;

Or if you just want case-insensitive in AutoQuery:

Plugins.Add(new AutoQueryFeature {
    StripUpperInLike = false
});
1 Like
#5

It works as expected. Thanks.

1 Like