Implement more relevant textual search queries

[myssto]

SQL Full text search

Currently to search by name for tournaments, matches, players, etc we are using EF.Functions.ILike() (ref) which often produces results that are questionably relevant with no way to sort.

I think the most promising and straightforward solution to this would be to start using full text search, which NpgSQL has native support for within EF. This would allow us to perform natural language text searches instead of pattern matching, as well as being able to sort the results by "relevance" (how well the input matches the text vector).

References

• postgresql.org
• npgsql.org

High level task overview

• Configure database tables with full text search indices
  • tournaments should be compound { name, abbreviation }
  • matches should be { name }
  • players should be { username }
• Identify most relevant full text search operation to utilize (likely websearch_to_tsquery from reading a bit of the documentation)
  • Update existing queries that use EF.Functions.ILike() to use full text search operations (i.e. TournamentsRepository.SearchAsync(), IQueryable<Match>.WhereName())