efcore.pg: TrigramsAreSimilar could not be translated BUG

Using the following versions:

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Trigrams" Version="3.1.4" />

I keep having an exception when trying to use the trigram extension methods. The following code

.Where((a) =>
                 EF.Functions.TrigramsAreSimilar(a.GivenName, person.GivenName) 
                 && EF.Functions.TrigramsAreSimilar(a.FamilyName, person.FamilyName))
                .ToListAsync();

results in:

System.InvalidOperationException: The LINQ expression 'DbSet<TPerson>
    .Where(t => __Functions_0
        .TrigramsAreSimilar(
            _: t.GivenName, 
            source: __person_GivenName_1) && __Functions_0
        .TrigramsAreSimilar(
            _: t.FamilyName, 
            source: __person_FamilyName_2))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
...

I haven’t found much documentation apart from ‘https://www.npgsql.org/efcore/api/Microsoft.EntityFrameworkCore.NpgsqlTrigramsDbFunctionsExtensions.html’ where there aren’t any examples.

Am I missing something or using it wrongly?

I would expect my code to just produce ’ where source % target AND source2 % target2’

Many thanks in advance.

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 21 (9 by maintainers)

Most upvoted comments

Thanks @taspeotis, I’ve added a new documentation section for Trigrams, hopefully that’s sufficient.

I have seen this recently and the application was missing a call to UseTrigrams on the NpgsqlDbContextOptionsBuilder.

As best I can tell the documentation does not explain how to use the EF.Functions.Trigrams* functions. UseTrigrams is (correctly) documented as Enable pg_trgm module methods and operators but you need to know UseTrigrams exists first.