Automatic functional index for string fields

[neumartin]

Hi!

Since PostgreSQL is case sensitive, consider this index:

entityBuilder.HasIndex(e => e.Name);

Can I force the index allways make a functional index like "lower(name)"?
That way, when I use linq like Customers.Where(e=> e.Nombre.ToLower().StartWith("something")) PostgreSQL allways use the index.

Sorry for my poor english!!
Thanks!

[roji]

Duplicate of #119.

First, consider using the PostgreSQL citext extension - it adds a case-insensitive text type which you can use instead of text. This makes sense if you will always do comparisons against your column which are case-insensitive.

Otherwise, if you sometimes need case sensitivity but not in this case, check out the following issues: #119 and dotnet/efcore#3986. Long story short, you can define any index you want by using raw SQL in your migrations, and adding a fluent API (HasIndex()) to support arbitrary expression indices could get very complicated without adding so much value.

[neumartin]

Thanks roji!

Can I use HasAnnotation in entityBuilder.HasIndex(e => e.Name); to define a functional index like lower(name) but only in PostgreSQL?
Becouse I will have migrations for PostgreSQL and SQLServer in the same assembly.

Thanks!

[roji]

I'm not sure I understand... As I wrote above, you cannot use HasIndex() to define an expression index - it's not supported on any database. What you can do is either change the column's type to citext or manually define an expression index with raw SQL in your migration.

[neumartin]

Hi roji,

If I use citext the index will ingnore that field.
I want to define an expression index with raw sql as you say, but I need to execute only if the database is PostgreSQL.
How can I see in the migration if the connection string is for PostgreSQL or SQL Server?

Thanks!!

[roji]

See the EF Docs.

The following should work:

if (migrationBuilder.ActiveProvider == "Npgsql.EntityFrameworkCore.PostgreSQL")
{
    // your code
}

[neumartin]

Thanks!!!

[neumartin]

Last one, if I have entityBuilder.HasIndex(e => e.Name); in my mapping code and the functional index I duplicate the indexes, can I ignore the entityBuilder.HasIndex(e => e.Name); for NPGSQL?
Like .HasAnnotation("Npgsql:.... something to ignore the index creation.
Thanks!

[roji]

When you put entityBuilder.HasIndex(e => e.Name), this causes some lines to be generated in your migration, which you can delete or wrap in a condition as I showed above (to have it work only for other providers).

If I understand you correctly, the healthiest thing is probably to:

• Have entityBuilder.HasIndex(e => e.Name) in your model only for non-Npgsql providers, but wrapping it in a condition. Add a comment explaining that for Npgsql the index will be added manually via raw SQL in the migration.
• Generate the migration for another provider, so that you get the index creation in the migration. Edit it manually, and use a condition to apply the raw SQL index creation for Npgsql only.

[neumartin]

Thanks!