Incorrect SQL generation for LTree.MatchesLQuery

[temos]

Hello,
I've encountered a weird behavior while using the ltree extension, in particular the Ltree.MatchesLQuery method.

Given the following code

using Microsoft.EntityFrameworkCore;

using var context = new DatabaseContext();

var data = context.Categories.Where(c => c.Path.MatchesLQuery("*." + c.Id));
Console.WriteLine(data.Count()); //PostgresException (0x80004005): 42846: cannot cast type integer to lquery

class DatabaseContext : DbContext
{
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("connection-string-here");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.HasPostgresExtension("ltree");
    }
}

class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public LTree Path { get; set; }
}

the following SQL is generated

SELECT c."Id", c."Name", c."Path"
FROM "Categories" AS c
WHERE c."Path" ~ ('*.' || c."Id"::lquery)

which causes 'Unhandled exception. Npgsql.PostgresException (0x80004005): 42846: cannot cast type integer to lquery' when executed, because only the right side of the concatenation expression inside the MatchesLQuery method call is being cast into lquery.

I would expect the entire concatenation expression inside the MatchesLQuery method call (the string '*.' concatenated with the Id column) to be cast into lquery, not just the right side, producing the following SQL which would work

SELECT c."Id", c."Name", c."Path"
FROM "Categories" AS c
WHERE c."Path" ~ ('*.' || c."Id")::lquery

This only happens when concatenating with a column from the database, not a constant or a parameter. Order also does not matter. If I were to swap the left and right side or put the Id in the middle like this

var data = context.Categories.Where(c => c.Path.MatchesLQuery(c.Id + ".*"));
var dat2 = context.Categories.Where(c => c.Path.MatchesLQuery("*." + c.Id + ".*"));

it would still try to cast the Id to an lquery first and then do the concatenation like this

SELECT c."Id", c."Name", c."Path"
FROM "Categories" AS c
WHERE c."Path" ~ (c."Id"::lquery || '.*');

SELECT c."Id", c."Name", c."Path"
FROM "Categories" AS c
WHERE c."Path" ~ (('*.' || c."Id"::lquery) || '.*');

which of course does not work.

Tested on .NET 6 and 7
Npgsql.EntityFrameworkCore.PostgreSQL 6.0.6 and 7.0.0-preview.7
Npgsql 6.0.6 and 7.0.0-preview.7
Microsoft.EntityFrameworkCore 6.0.8 and 7.0.0-preview.7.22376.2

[roji]

Thanks, definitely seems like a parentheses/precedence issue... Will look into it.