How to get relevance score on full text search with EF Core

[benny-adiwijaya]

Hi, with stored procedure we can get score like this

SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news;

+----+----------------------+
| id | score                |
+----+----------------------+
|  1 | 0.031008131802082062 |
|  2 |  0.25865283608436584 |
|  2 |  0                   |
+----+----------------------+
3 rows in set (0.00 sec)

And with EF Core we can get similiar result using code like this

var result = _db.news
    .Where(p => EF.Functions
    .Match(new string[]
    { p.title,p.content,p.author },
        "traveling to parks", MySqlMatchSearchMode.NaturalLanguage));

return result.ToList();

Is it possible to get relevance score on full text search with EF Core just like in stored procedure?

[TheConstructor]

Probably the main thing to change would be the return type of MySqlDbFunctionsExtensions.Match. As this would be a breaking change, adding an overload with a return type of e.g. double might be another option (I didn't see the result-type in the documentation, but if I just use the function in .Select I get System.InvalidCastException: Unable to cast object of type 'System.Double' to type 'System.Boolean'.).

As a workaround you could try

var result = _db.news
    .Select(p => new
    {
        p.id,
        score = Convert.ToDouble(EF.Functions
            .Match(new string[] {p.title, p.content, p.author},
                "traveling to parks", MySqlMatchSearchMode.NaturalLanguage))
    });

return result.ToList();

which should result in a SQL like

SELECT id, CAST(MATCH (title,content,author) AGAINST ('traveling to parks') as double) as score FROM news;

It's not perfect, but it should also not add a huge overhead.

[mguinness]

Probably the main thing to change would be the return type of MySqlDbFunctionsExtensions.Match.

Yes, it probably should return a numeric value instead of a boolean. It works in the where clause as any non-zero result is true.

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/7ed82ee8102c6c7cb1c84f4927ff36974b87834b/src/EFCore.MySql/Extensions/MySqlDbFunctionsExtensions.cs#L653-L673

From Natural Language Full-Text Searches

Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity.

For Boolean Searches, MATCH() will return 0 or 1 per search term matched. So if you're matching against 3 words, and all words appear at least once it will return 3.