Missing operations on ts_query?

[Bouke]

I'd like to be able to write the following SQL: @@ ((websearch_to_tsquery('[user input here]')::text || ':*')::tsquery). Using EF.Functions.WebSearchToTsQuery(q) I can generate the first part, but now I'd like to append :* to that query to perform a prefix search.

I've tried EF.Functions.PhraseToTsQuery(EF.Functions.WebSearchToTsQuery(q).ToString() + ":*"), but that fails with:

System.InvalidOperationException: The LINQ expression 'DbSet<ProductCluster>
    .Where(p => __Functions_0
        .ToTsVector(p.Name)
        .Matches(__Functions_0
            .PhraseToTsQuery(__Functions_0
                .WebSearchToTsQuery(__q_1).ToString() + ":*")) || __Functions_0' 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.

[roji]

Any chance you can post the full query code please, plus the minimal model?

[Bouke]

My model is something like this:

class Person {
    public string Name { get; set; }
}

And the query I'd like to do is to combine user input (e.g. "rojan") and put a prefix match operator (:*) after that, so that the query becomes 'rojan':*:

SELECT * FROM "Person"
WHERE ts_vector('english', "Name") @@ ((websearch_to_tsquery('rojan')::text || ':*')::tsquery)

This fails with the error above:

var q = "rojan";
var rojis = dbContext.Persons
    .Where(p => EF.Functions.PhraseToTsQuery(EF.Functions.WebSearchToTsQuery(q).ToString() + ":*"))
    .ToList();

[roji]

Your code above doesn't compile... I've tried to fix something up based on your error message above (code below), but got a different exception message (which I'll also look into).

But in the meantime... Can you please help me out and send something that builds and produces your exception?

Repro for another exception

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var q = "rojan";
var rojis = ctx.Persons
    .Where(p => EF.Functions.ToTsVector(p.Name).Matches(EF.Functions.PhraseToTsQuery(EF.Functions.WebSearchToTsQuery(q).ToString() + ":*")))
    .ToList();

public class BlogContext : DbContext
{
    public DbSet<Person> Persons { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

[Bouke]

Sorry I have omitted to note my version. As we're running on .NET Framework, we're on version 3.1.11 of the driver. The repro mentioned above gives the following exception with that version:

Message The LINQ expression 'DbSet<Person>
    .Where(p => __Functions_0
        .ToTsVector(p.Name)
        .Matches(__Functions_0
            .PhraseToTsQuery(__Functions_0
                .WebSearchToTsQuery(__q_1).ToString() + ":*")))' 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.

(I just realised that the code I posted initially didn't exactly match the exception, as I'm including additional fields in the actual query. For this issue I cleaned up the code, but omitted cleaning up the exception, sorry.)

[roji]

@Bouke please, spend a few minutes time to put together a simple, runnable console program which produces the exception you're seeing. I simply don't have enough time in my life to reconstruct working code from bits and piece.

[Bouke]

@roji Sure, thank you for your time. Attached is the repro.

Npgsql1724.zip

[roji]

@Bouke thanks.

The problem is that you're converting a string parameter ("rojan") to a TsQuery (with WebSearchToTsQuery), only to try to convert it back to a string for concatenation purposes - that's the unsupported part. What's also odd is that you then re-convert it back to a TsQuery with PhraseToTsQuery - can you explain your reason for these?

In other words, the following works fine, doing the concatenation before converting to a TsQuery (with either PhraseToTsQuery or WebSearchToTsQuery):

var q = "rojan";
var rojis = ctx.Persons
    .Where(p => EF.Functions.ToTsVector(p.Name).Matches(EF.Functions.PhraseToTsQuery(q + ":*")))
    .ToList();

[Bouke]

That doesn't generate the same query, which can be compared like so:

select (websearch_to_tsquery('rojan')::text || ':*')::tsquery, websearch_to_tsquery('rojan:*');

desired	suggestion
'rojan':*	'rojan'

So what I'm trying to do is parse the user's input as a web query using websearch_to_tsquery. Then I want to append the "prefix operator" (:*) so the last entered word by the user only needs to match on the prefix. I cannot append :* to the user's input and pass that into websearch_to_tsquery as that will simply drop that operator (see also the table above). I understand that what I'm doing throws an exception: I'm looking for a way to construct an expression that will yield the query above.

I specifically want to use websearch_to_tsquery as that means I don't have to parse the query in my code. Some examples:

select (websearch_to_tsquery('hello worl')::text || ':*')::tsquery, websearch_to_tsquery('"hello world"');

input	ts query
hello worl	'hello worl':*
"hello world"	'hello <-> world'

[roji]

@Bouke thanks for explaining - I see the issue now. I'll follow up on it.

[roji]

Here's an ugly workaround for you - instead of ToString, you can cast to (string)(object).

            var q = "rojan";
            var rojis = ctx.Persons
                .Where(p => EF.Functions.ToTsVector(p.Name).Matches(EF.Functions.PhraseToTsQuery(((string)(object)EF.Functions.WebSearchToTsQuery(q)) + ":*")))
                .ToList();

We're thinking about what the exact behavior of ToString should be here.

[Bouke]

Thanks! However to be equivalent it needs to be EF.Functions.ToTsQuery instead of EF.Functions.PhraseToTsQuery. Or even more ugly by doing an additional set of casts:

(NpgsqlTypes.NpgsqlTsQuery)(object)((string)(object)EF.Functions.WebSearchToTsQuery(q) + ":*")

Yields:

CAST((websearch_to_tsquery(@__q_1)::text || ':*') AS tsquery)

[roji]

Any reason to not just use EF.Functions.ToTsQuery instead of the complicated cast?

The issue here is specifically with the non-translation of ToString, which can be worked around with (string)(object).

[roji]

Coming back to this after a long time... @Bouke any thoughts on my last comment? I can take a deeper look at all this again if needed.

[Bouke]

ToTsQuery can replace some of the casts, so the query from above becomes:

EF.Functions.ToTsQuery((string)(object)EF.Functions.WebSearchToTsQuery(q) + ":*")

Ideally I wouldn't need to cast NpgsqlTsQuery through object to string just to concatenate the string ":*". Similar to what I can do without casts in sql: websearch_to_tsquery(@__q_1)::text || ':*'.