Marten: case-insensitive queries by default

[mdissel]

Coming from a sql server world, I like to set a global case insensitive option that is used when no string comparision is supplied in the query statement. Opinions/ideas?

ps. I'm not sure if the current ILIKE operator is the best choice for best performance, some hits on google suggests that ILIKE is not using an index and it's better to use LOWER(aAa) = aaa.. but i'm not at all a postgresql guru..

[mdissel]

Trigrams seems to have a better performance with ILIKE / LIKE queries.
https://about.gitlab.com/2016/03/18/fast-search-using-postgresql-trigram-indexes/

[phillip-haydon]

I think it would be either citext or lower(...) to do this check. I guess it's a case of figuring out the best way to configure it and create the queries without hindering perf of query generation.

[mdissel]

citext is an extension. I think we can't assume it's always available. I tried to find a way to the documentschema from https://github.com/JasperFx/marten/blob/920bccbb44a5ffd47198fb50c276a2d6d9c76650/src/Marten/Linq/Parsing/StringComparisonParser.cs#L66 so you could check if for a global case-insensitive query... Any tips?

[phillip-haydon]

citext ships with 9.1 on I believe, and since we only support 9.5 onward, it's just a case of calling create extension citext

It does massively complicate queries tho. I believe you could do it now without any changes to Marten by using Duplicate(...)

.Duplicate(x => x.Name, "citext")

That would copy the Name to its own field of type citext and then all queries would be where name = 'value' and would be a case insensitive check.

[mdissel]

ok, but that's still no global solution. Every text query should be case-insensitive (to help out developers who are switching from sqlserver to postgresql ;) )

[phillip-haydon]

@mdissel it's going to be really hard to achieve. There's no global case-insensitive setting. citext basically calls lower(...) behind the scenes and is only slightly more performant than just calling lower(..) yourself. I'm worried a change to support CI will be hindered by both PostgreSQL and by the Query generation.

:(

[mdissel]

I'm wondering if I'm the only one with CI 'issue', none of our textual queries should be CI.

[phillip-haydon]

When I started using Marten I spent ages trying to figure out how to solve CI issue.

But really I only had 1 problem which was looking up values in the database based on routes.

                var categories = await session.Query<Category>()
                                              .Where(x => x.Name.Equals(cat, StringComparison.OrdinalIgnoreCase))
                                              .ToListAsync(ctx)
                                              .ConfigureAwait(false);

So I used the overload to ignore.

Since then I honestly haven't needed to worry about CI.

I did just add the ability to specify indexes as lower:

_.Schema.For<Login>()
 .GinIndexJsonData()
 .Index(x => x.Identifier, definition =>
 {
     definition.IsUnique = true;
 })
 .Index(x => x.Email, definition =>
 {
     definition.IsUnique = true;
     definition.Casing = ComputedIndex.Casings.Lower;
 });

To ensure things like email are not duplicated by casing.

I can totally understand your point about it being an issue for new comers to PostgreSQL from SQL Server. I don't think you will be the only person with the issue, I just don't know the best way to solve it :(

[mdissel]

But isn't ComputedIndex.Casings.Lower a solution to the issue? Whenever an index is defined as lower, the query engine can automatically lowercase the text value in the sql parameter..

[phillip-haydon]

I'm currently working on a solution for this. Adding it to 1.1 milestone so I can hopefully squeeze this in.

[JohnCampionJr]

Just wondering if this made it into Marten? I'm encountering a situation where a 3rd party tool (KendoUI) is generating the LINQ query so I can't tap into it to force it to lower. I need some way to default contains searches to case insensitive.