In other words, I don't think you can use my comments at #9126 (comment) as guidance.

I want to call out that since apparently in PostgreSQL the SQL you write controls completely whether lookups will be case sensitive or insensitive, I don't think there are enough similarities between this decision and the way we decided to not try to force case sensitivity for SQL Server. my expectations as a user of EF Core + Npgsql may be skewed towards PostgreSQL behaviors or towards EF Core behaviors depending on where I am coming from and what I am the most familiar with. I'll have PostgreSQL's implementation of EF.Functions.Like() be case-sensitive (as PostgreSQL LIKE is), and add a PostgreSQL-specific EF.Functions.ILike() which will generate PostgreSQL ILIKE, the up to you 😄 Personally I can see it both ways, e.g. I wonder if in PostgreSQL the situation is similar. Neither option seemed worth pursuing and it seemed to make more sense to leave it to the application developer and the administrator of the database to control case-sensitiveness through the existing mechanisms rather than getting in the way. compensating for the comparison to always be case insensitive would have required also generating SQL that explicitly overrides the collation (keep in mind that although it is common to get a case-insensitive collation in SQL Server, that is not how all servers, databases, tables or columns are configured) and rewriting all in-memory evaluations to perform case insensitive string comparisons.compensating for the comparison to always be case-sensitive on a SQL Server database would have involved either generating SQL that explicitly overrides the collation or repeating every comparison in memory.We made an explicit decision not to go out of our way to compensate. In case it helps you make a choice for Npgsql, here is some precedent:įor direct equality comparisons between strings we kind of made the opposite call: depending on the actual collation in effect, the comparison is likely to be case-insensitive in the database, but it will be case-sensitive in memory.

We think it is ok to have variations across providers for this kind of thing, although it is nice to match the in-memory behavior as long as you don't need to go out of way to do so. But to cause less surprise with PostgreSQL users who are used to LIKE being sensitive, EF.Functions.Like() should generate LIKE - and accept that there are going to be variations across providers with this kind of thing. I'm wondering what you'd recommend for the Npgsql behavior: to align with the 'default' EF Core behavior, I'd generate case-insensitive ILIKE clauses rather than LIKE.

Enabling the current provider to influence the in-memory behavior to match the database behavior (that could potentially break existing code but would at least be scoped to specific providers).

Adding a flag at the context options level to control the behavior.

Adding overloads of Like that take a StringComparison argument.

Hello In retrospective the choice may have been a bit arbitrary, but that is correct: It is very late in the 2.0 game and it is very unlikely that we would change the behavior at this point.Īlso, changing the default later would be a breaking change, but we can certainly consider adding ways to control the behavior, e.g.: I'm guessing that at least the client-side implementation is going to remain case-insensitive. Given that I'm popping this out a bit last minute with 2.0.0 around the corner, I'm guessing that at least the client-side implementation is going to remain case-insensitive. At the very least Oracle seems to be case-sensitive by default as well. PostgreSQL LIKE is case-sensitive (and an alternative ILIKE exists for insensitive).

Now, I don't know SqlServer or the SQL Standard that well, but I'm wondering about the 'correct' default for LIKE case sensitivity. (the regex below similarly has RegexOptions.IgnoreCase).

Postgresql like ignore case