Ef Core Trigrams Similarity Distance Implementation - Import Failed in EF Core PostgreSQL / npgsql-CodePudding

I like to use this function- EF.Functions.TrigramsSimilarityDistance(s1, s2) mentioned in

DbFunctions' does not contain a definition for "TrigramsSimilarityDistance' and no accessible extension method TrigramsSimilarity Distance' accepting a first argument of type 'DbFunctions could be found (are you missing a using directive or an assembly reference?)

I am not sure how I can import the pg db extensions. What I have done is-

using static Microsoft.Extensions.DependencyInjection.NpgsqlServiceCollectionExtensions;

But it is not working.

Update-

I am using ASP.Net Core 3.1, so I am using these 2 nuget packages-

Then to configure, I have done this in Startup.cs file-

services.AddDbContext<ApplicationDbContext>(options => {
        options.UseNpgsql(Configuration.GetConnectionString("DevelopConnection"), option => option.UseTrigrams());
});

Then when I am calling like abovementioned way-

string searchValue = "similar text";
var resultList = await _context.Comments.OrderBy(c => EF.Functions.TrigramsSimilarityDistance(c.Name, searchValue))
        .ThenBy(t => t.Name)
        .ToListAsync();

And getting an error like this-

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: text <-> text

  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: operator does not exist: text <-> text
    Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
    Position: 473
    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\parser\parse_oper.c
    Line: 731
    Routine: op_error

Can anyone please help me to solve this problem?

CodePudding user response：

The first issue was because you needed to import the nuget package Npgsql.EntityFrameworkCore.PostgreSQL. It seems you have you have resolved this issue.

The second issue is that the operator <-> (similarity_dist) is missing from your database. This error is thrown by the database and not Microsoft.EntityFrameworkCore.

You can see in this documentation that the operator "text <-> text" should have been installed with pg_trgm as Svyatoslav Danyliv pointed out in the comments.

Here is how to install the additional module pg_trgm

CodePudding user response：

I have solved the problem and written a full post on that. The full post can be found in here-

https://www.linkedin.com/posts/ajahin_sort-by-minimum-edit-distance-levenshtein-activity-6855206159448649729-dfN5/