Postgres EFCore generating a case insensitive query which uses ILIKE ANY

Question

So I'm using EntityFramework Core with Npgsql provider in my project.

I have the following query, which uses Array.Contains.

var symbols = new[] { "AAPL", "GOOG", "MSFT" };
var instruments = dbContext.Instruments.Where(t => symbols.Contains(t.Symbol)).ToList();

Which produces sql like:

select [columns] from instruments where symbol = ANY(ARRAY["AAPL", "GOOG", "MSFT"])

As you can see, it translates the Array.Contains into an ANY clause, which is case sensitive.

Instead, I want a case insensitive query to be generated using ILIKE ANY. Something like the following:

select [columns] from instruments where symbol ILIKE ANY(ARRAY["AAPL", "GOOG", "MSFT"])

Is this possible to do with Npgsql?

Answer 1

As you can see, it translates the Array.Contains into an ANY clause, which is case sensitive.

ANY isn't case-sensitive or sensitive; the case sensitivity is determined by the column being compared here (symbol). Give the EF docs on collations and case sensitivity a good read and consider switching symbol to a case-insensitive collation.