Map pg_trgm extension functions

[quanterion]

Hi!, I'm using the .net core 2.0, I have seen that you recently implement LIKe and ILIKE functions and wonder is it possible to use prominent pg_trgm extension https://www.postgresql.org/docs/9.6/static/pgtrgm.html that provides fuzzy search functionality?

I suppose It is possible to enable this extension and call similarity() function using raw sql query, But I can't understand how to create index like that:

CREATE INDEX customer_names_on_last_name_idx ON customer_names USING GIN(last_name gin_trgm_ops);

Is it possible to do now?

[roji]

The EF Core provider does support specifying which index method (or type) you want. This feature was missing from the docs, I've just added it: see this page.

Also, it should be trivial to add support for calling the pg_trgm without resorting to raw SQL - this would consist of PostgreSQL-specific methods on EF.Functions which would be recognized and translated to their SQL counterparts. I'm going to keep this issue open to track this. If you wish, you can take a look at adding this - it really is quite easy. If you want to try and need some help, don't hesitate to ask.

[quanterion]

Thank you for quick response! Can you clarify a few issues:

1. I understand from docs how to set index type to GIN, but how to add gin_trgm_ops after fields?
2. Does EF.Functions.similarity should provide client side implementation that exactly follows pg_trgm results?

[roji]

@quanterion, ah, I missed that you had an operator class in your CREATE INDEX statement (gin_trgm_ops). There's no way to express this currently in your model, but you can always create the index in raw SQL in your migration files.

Regarding the second option, no, there's no real requirement for EF.Functions.Similarity() to provide a client-side implementation - the function can simply throw NotSupportedException with an informative message. The message would only be triggered when trying to execute against non-PostgreSQL databases. It's a nice-to-have - which is why the EF Core team did this for Like.

[YohDeadfall]

Duplicate of #897.