The LINQ expression could not be translated with OData and PostgreSQL: matchesPattern filter

[derek-wong12]

Odata Version: 8.2.0 PostgreSQL Version: 13

When using a filter like $filter=matchesPattern(LocationName,‘^.D.$’) to filter a column of type “character varying(100)” in an endpoint that uses ODataQueryOptions.ApplyTo it will always fail with error:

The LINQ expression 'DbSet<StoreLocations>()
    .Where(r => Regex.IsMatch(
        input: r.LocationName, 
        pattern: __TypedProperty_0, 
        options: ECMAScript))' could not be translated. Additional information: Translation of method 'System.Text.RegularExpressions.Regex.IsMatch' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)

PostgreSql Table:

CREATE COLLATION IF NOT EXISTS collation_case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE TABLE IF NOT EXISTS public."StoreLocations"
        (
          "LocationName" character varying(100) COLLATE "collation_case_insensitive"
        )

C#:

public Task<IQueryable<StoreLocations>> GetAllLocations(ODataQueryOptions options)
       {
           return Task.FromResult(options.ApplyTo(_dbContext. StoreLocations.AsNoTracking(), ignoreQueryOptions).Cast<StoreLocations >());
       }

Request URL: https://localhost:8443/storelocationssearch/$query

Payload:

{
    "$FILTER":"matchesPattern(LocationName,‘^.*D.*$’)"
}

[julealgon]

@roji do you happen to know why this is not working? From the docs, Regex.IsMatch should be supported in the EFCore provider:

• https://www.npgsql.org/efcore/mapping/translations.html#:~:text=Regex.IsMatch(stringValue%2C%20%22%5EA%2B%22%2C%20regexOptions)

@derek-wong12

PostgreSQL Version: 13

What version of the PostgreSQL EFCore provider are you using?

[derek-wong12]

@julealgon I am currently using Npgsql.EntityFrameworkCore.PostgreSQL version 6.0.6

[roji]

FYI this has been cross-posted as https://github.com/npgsql/efcore.pg/issues/2818#issuecomment-1644521503 - I'll take a look.

[WhatzGames]

Case closed on npgsql-side. IsMatch is supported in general, but some RegexOptions are not supported. Mostly because postgres itself does not cover them. The ECMAScript option is one of them.