Defaulting to ILIKE for PostgreSQL

ServiceStack.OrmLite PostgreSQL
1

When using the Select API is there a global setting that can be set to default to case insensitive searches in queries like this one for PostgreSQL:

var res = Db.Select<CustomerProperties>(cp => cp.Description.Contains("name"));

SQL: SELECT "id", "customer_id", "description"
FROM "customer_properties"
WHERE "description" like :0 
               PARAMS: :0=%name%

Basically, making all like ILIKE by default.

When using autoquery I’m using QueryDbField like so to work around this:

[QueryDbField(Template="{Field} ILIKE {Value}", 
    Field="description", ValueFormat="%{0}%")]

More generally, is there a way to make everything case insensitive when dealing with PostgreSQL?

Thank you.

2

There isn’t but Contains is already case insensitive by comparing both column and parameter in upper case:

SELECT *
FROM "customer_properties"
WHERE upper("description") like :0

PARAMS: :0=%NAME%
3

I’m missing something here…

Here is another query. I have a tenant description stored like so:

image

mythz in lower case returns no result:

var notWorking = Db.Select<TenantProperties>(x => x.Description.Contains("mythz"));

dbug: ServiceStack.OrmLite.OrmLiteReadCommandExtensions[0]
      SQL: SELECT "id", "description"
FROM "tenant_properties"
WHERE "description" like :0
     PARAMS: :0=%mythz%

I don’t see upper anywhere in the generated SQL…?

To get the record, I need to capitalize the M in mythz in the string:

var works = Db.Select<TenantProperties>(x => x.Description.Contains("Mythz"));

dbug: ServiceStack.OrmLite.OrmLiteReadCommandExtensions[0]
  SQL: SELECT "id", "description"
FROM "tenant_properties"
WHERE "description" like :0
      PARAMS: :0=%Mythz%

From you previous answer I understood that .Contains should have injected upper() somewhere in the query and that

Db.Select<TenantProperties>(x => x.Description.Contains("Mythz"));

Should return your record irrespective of the case of the string in: .Contains(“MyThz”) but it doesn’t seem to be the case ?

Thanks

4

It’s disabled in .NET Core by default, but you can enable it with:

OrmLiteConfig.StripUpperInLike = false;
1 Like