Full Text Search: Unleashing the Power of PostgreSQL and Entity Framework Core
Effective information retrieval and searching are essential for every application to succeed in the data-driven world of today. When looking at enormous datasets or tackling difficult search requirements, traditional applications use the LIKE operator or regular expression. Additionally, we disregard being close to the user-inputted result when dealing with complex queries. Our result doesn’t know which is most relevant to our search key when we use the Like operator to search a sentence in a relational database.
Core Concept of Full Text Search
Full-text searches look for a search word in all documents and return results based on how similar they are. Because the application’s user constantly wants to know the most appropriate result for what they supply as a search query, ranking in the search result gives search astonishing power.
By the way, if you try to understand full Text Search, you will see the Document concepts. Document refers to the grouping of information needed to carry out the search. The document may consist of a single table column or several table columns.
By their content, documents are converted to lexemes. In the preceding SQL query, convert one simple sentence to a lexeme via English configuration.
The result of this query is;
"'articl':10 'hello':1'satisfi':6 'type':8 'yusuf':4"You noticed that while building lexemes, SQL doesn’t give a damn about stop words and only accepts required words.
Basic Text Matching via vector and query
We saw the vector for PostgreSQL. It splits the document, keeping the lexeme and the number of vectors. But here we have explained the significance of the query. The main responsibility of a query is to provide search terms to Tsvector with AND, OR, NOT, and FOLLOWED BY conditions.
For example, when you execute previous raw sql in one query you will be giving “true” result. Because our tsquery worked with OR (|) condition.
Here we have to discuss configuration in PostgreSQL. To give full power of full text search, You have to work with configuration. Because configuration skips some words and doesn’t include them in the search. And your search will be satisfied if it removes these stop words. For example, the result of the following query is “articl’:10 ‘hello’:1'satisfi’:6 ‘type’:8 ‘yusuf’:4”. As we can see, feed configuration as english helped us remove stop words like “am”, “to”, etc. And
After a brief introduction, let's take a look at how we can use the entity framework and full-text search.
There are two main ways to use these powerful tools together. One-way conversion of one or multiple columns directly to the TSVector via the Entity Framework Function.
- Directly Convert TSVector One Or Multiple Column
In the previous code, we used searchTerm to search for our product name. But here you need to be careful; if your search doesn’t have normalized letters, it will throw an error. When we take a look at line 4, we will see we converted one column, which is called name, to the vector directly via query. At line 8, we ordered our result according to which had the most relevance to our search term. By the way, we can show our consumers the ordered list. It will satisfy our consumers to see this result.
If you want, you can search your query directly via Rank value. The preceding code scope gives you full control over your search result.
2. Setting TSVector Inside Entity Model
This method will observe your columns that need to be converted to the lexeme of a vector and keep itself updated as soon as this column changes. And you can't search in this vectored column anymore. This is the best practice and the suggested way.
Now that we have created a new property inside our Product class, in the onModelCreating of the database context, you need to make some extra settings to decide your configuration and columns. With this method, you will create an index to get your results more quickly from PostgreSQL.
At the last point, We can change our code to search our term in the indexed vector.
Summary
Full text search gives you the ability to search, and you can create efficient searchable content, by the way. The Entity Framework lets you use these functions without breaking your model, and you can combine your columns into the document and search via search terms.
