{{ message }}
Repository navigation
The LINQ expression could not be translated with OData and PostgreSQL: matchesPattern filter #2818
Closed as not planned
Description
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.*$’)"
}
Activity
[-]The LINQ expression could not be translated with matchesPattern and PostgreSQL[/-][+]The LINQ expression could not be translated with OData and PostgreSQL: matchesPattern filter[/+]roji commentedon Jul 20, 2023
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 commentedon Jul 20, 2023
@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 commentedon Jul 20, 2023
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 commentedon Jan 5, 2024
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 commentedon Jan 16, 2024
@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.