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.*$’)"
}

[roji]

Any chance you can reproduce this in a direct EF LINQ query without OData? I'm not sure exactly what query shape OData is producing there.

[derek-wong12]

@roji I can reproduce the error with this LINQ query:
IQueryable<StoreLocations> brokenLinq = _dbContext.StoreLocations.Where(r => Regex.IsMatch(r.LocationName, "^D.*$", RegexOptions.ECMAScript));

[derek-wong12]

A direct LINQ query with an AsEnumerable() before the Where statement is working:
IQueryable<StoreLocations> workingLinq = _dbContext.StoreLocations.AsEnumerable().Where(r => Regex.IsMatch(r.LocationName, "^D.*$", RegexOptions.ECMAScript));

[WhatzGames]

According to the source it does make sense for your query to fail, as the ECMAScript option is unsupported by postgres.
@roji would this be a reason to close this issue?
Perhaps moving the discussion over to the odata side?

[roji]

@WhatzGames indeed - the ECMAScript option isn't currently supported, because back when I implemented the regex translation support, PostgreSQL didn't seem to support it; I'm pretty blurry on what ECMAScript mode actually does, so if this is important and someone wants to dive into it, please do so (I'd accept a PR adding support for it if it makes sense).

In the meantime, I'm closing this issue as by-design.