Cursor pagination for query based full-text search

Question

I need to implement cursor based pagination on an endpoint which accepts a search query. The problem is that since rows will be ranked based on a relevance score derived from a full-text search in natural language, IDs won't be sequential. Does anyone know how I can achieve this in EF Core with Pomelo.EntityFrameworkCore.MySql?

Answer 1

Hi, @iKingNinja . Welcome to Microsoft Q&A. 

After full-text search and sorting by relevance, the ID will be chaotic, but the row number is normal. You could create a view in the database or in the program and add a column to record the row number to achieve ID continuity.

Your final requirement is to do paging query. After getting data from the database through full-text search, the data is generally retained in the List collection. At this time, the index of the List collection is the natural Id (row number). On this basis, you could directly use GetRange(lastIndex, pageSize); or do Linq query to further implement paging. The primary key ID could remain unchanged to ensure other queries later.

Refer to the following code to implement paging query after full-text search.

string searchText = "What you are searching for";
var pageSize = 2;
var lastIndex = 0;
using (var dbContext = new YourDbContext())
{
    string query = @" SELECT Id, Title, Description, LastUpdated, MATCH(Title, Description) AGAINST ({0}) AS Relevance FROM YourEntity ORDER BY Relevance DESC";
    var dbResult = dbContext.Yourentities.FromSqlRaw(query,searchText).ToList();
    var result = dbResult.GetRange(lastIndex, pageSize);
}

You could also use paging directly based on full-text search results.

string searchText = "comprehensive guide";
var pageSize = 2;
var lastIndex = 0;
using (var dbContext = new YourDbContext())
{
    string query = @"SELECT Id, Title, Description, LastUpdated, MATCH(Title, Description) AGAINST ({0}) AS Relevance FROM YourEntity ORDER BY Relevance DESC LIMIT {1} OFFSET {2};";
    var dbResult = dbContext.Yourentities.FromSqlRaw(query,searchText,pageSize,lastIndex).ToList();
}

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.