SearchEvaluator and case sensitivity

[fretje]

As the SearchEvaluator is using the EF.Functions.Like method, this can apparently have different behaviour with regards to case sensitivity depending on which provider you're running.

AFAICT for mssql Like is always case insensitive, but for postgresql it is dependent on the collation of the database or column. There's ways to overcome this by using Collate on the collumn, or by using EF.Functions.ILike (which is an extension in the postgresql provider).

Some interesting links on the topic:
https://stackoverflow.com/questions/7005302/how-to-make-case-insensitive-query-in-postgresql
https://stackoverflow.com/questions/43277868/entity-framework-core-contains-is-case-sensitive-or-case-insensitive

But my question is: Would there be any way to handle case sensitivity in a consistent way, regardless of which provider you're using?

I was thinking maybe having an api for it (maybe as an extra bool caseSensitive argument on the ISpecificationBuilder<T>.Search method), as I would think case sensitivity while searching is something you would want to be able to configure in a specification?

[fiseni]

Hey @fretje,

Yes, that's correct. The EF.Functions.Like is tightly coupled to a given EF's provider. Generally, the LIKE operator is not strictly standardized and can behave quite differently across various DB engines, case sensitivity being one of them.

We can keep such a flag in a specification, but that won't solve the issue. The main issue is that the type of the comparison will depend on the database configuration. That's why the EF.Function.Like is not offering such an option either.

To avoid this issue, a common approach is just to use "ToLower" or "ToUpper" while creating your expression.

[fretje]

Yes, using ToLower and ToUpper is indeed a common approach, but IMO not the right one as this can have issues with special characters in some cultures... It also has issues with performance and indexes.

I'll have to think a bit about it... but using the new EF.Functions.Collate, I think it's possible to make this db provider independent somehow...

Here's the actual documentation... Reading that, it seems Collate has the same issues on indexes and performance...
Hmm... might have not been such a good idea after all... ;-)

[fiseni]

We want to exclude ourselves from any customization. In some sense, we're just a proxy here, and we try not to affect the underlying behavior. We're not pretending to be an ORM, that's not the intention here :)

So, if there is an easy way to pass the knowledge to "EF.Functions" then sure, we'll do that, otherwise, we won't get into it.

Imagine the scenario without specifications. You install the EFCore and EFCore.SqlServer packages. You use "EF.Functions.Like" in your queries. Tomorrow, you decide to use EFCore.PostgreSQL provider instead. What changes do you make to your queries containing "EF.Functions.Like"?

[fretje]

Sure, I understand all that!

Just for background, I'm working on https://github.com/fullstackhero/dotnet-webapi-boilerplate which is a template that offers the ability to change providers (at this time mssql, mysql, postgresql and oracle are supported) with the flip of a switch in configuration... just trying to make them behave the same, at least for what's in the base template (which includes advanced searching and filtering).

[ardalis]

Closed as inactive.