How to use COALESCE in FTS LINQ queries

[CodingGorilla]

I have a full text index as follows:

CREATE INDEX products_fts_index ON "Properties" USING GIN (to_tsvector('english_nostop', "Name" || ' ' || "AddressLine1" || ' ' || COALESCE("AddressLine2", '')  || ' ' || COALESCE("AddressLine3", '') || ' ' || "City" || ' ' || "State" || ' ' || "PostalCode" || ' ' || COALESCE("Phone", '')));

I'm trying to use the new FTS LINQ support to do these FTS queries, but I'm not entirely sure how to build the Where() clause. If I simply do something like:

.Where(p => p.Name + " " + p.AddressLine1 + " " + p.AddressLine2 + " " p.AddressLine3 + " " + p.City + " " + p.City + " " + p.PostalCode + " " + p.Phone)

Wouldn't it stop considering the other fields after the first null field (which is most likely to be p.AddressLine2?

[roji]

@rwasef1830, you may interested in this one.

[rwasef1830]

@CodingGorilla your Where clause is not a boolean condition.

It should look like this:

.Where(p => EF.Functions.ToTsVector(
    "english_nostop", 
    p.Name + " " + p.AddressLine1 + " " // ... all the fields in the same order as the index
    ).Matches(EF.Functions.PlainToTsQuery("Your search query"))
)

This should generate the SQL you expect and utilize the index you created.

[CodingGorilla]

So I tried this code:

var query = DbContext.Properties
                     .Where(p => EF.Functions.ToTsVector("english_nostop",
                          p.Name + " " + p.AddressLine1 + " " + p.AddressLine2 + " " + p.AddressLine3 + " " + p.City + " " + p.State + " " + p.PostalCode + " " + p.Phone)
                      .Matches(EF.Functions.ToTsQuery(searchTerm)));

Which generated this SQL (based on the console output):

SELECT p."Id", p."AddressLine1", p."AddressLine2", p."AddressLine3", p."City", p."Country", p."Name", p."OwnerId", p."Phone", p."PostalCode", p."PropertyGroupId", p."State"
FROM "Properties" AS p
WHERE ((to_tsvector('english_nostop', (((((((((((((((((((((((((((p."Name" || ' ')) || p."AddressLine1")) || ' ')) || p."AddressLine2")) || ' ')) || p."AddressLine3")) || ' ')) || p."City")) || ' ')) || p."State")) || ' ')) || p."PostalCode")) || ' ')) || p."Phone")) @@ to_tsquery(@__searchTerm_2)) = TRUE) AND (p."OwnerId" = @__PrincipalOwnerId_Value_3)

(The additional AND (p."OwnerId" = ...) is another where clause added a little bit after the fact.)

This query returns 0 results, however the following query (based on my original):

SELECT * FROM "Properties"
WHERE to_tsvector('english_nostop', "Name" || ' ' || "AddressLine1" || ' ' || COALESCE("AddressLine2", '')  || ' ' || COALESCE("AddressLine3", '') || ' ' || 
"City" || ' ' || "State" || ' ' || "PostalCode" || ' ' || COALESCE("Phone", '')) @@ to_tsquery('english_nostop', 'hotel') AND ("OwnerId" = 'e23cca8e-eaf0-4b6d-9602-c7d672201d0c')

returns 11 rows, which is what I expect.

I would note that the grouping in the generated query seems a tad odd, although I'm not sure that has any effect, but also it is definitely missing any COALESCE statements which leads me to believe that is why it returns no results.

[rwasef1830]

@CodingGorilla You can achieve coalesce effect in LINQ by using C# null coalesce operator, also make sure you use 'english_nostop' config also in your ToTsQuery call.

var query = DbContext.Properties
    .Where(p => EF.Functions.ToTsVector("english_nostop",
        p.Name + " " + p.AddressLine1 + " " + (p.AddressLine2 ?? "") + " " + (p.AddressLine3 ?? "") + " " + p.City + " " + p.State + " " + p.PostalCode + " " + (p.Phone ?? ""))
           .Matches(EF.Functions.ToTsQuery("english_nostop", searchTerm)));

[CodingGorilla]

That did the trick! Thanks for the reminder about the 'english_nostop' in the ToTsQuery() as well, I totally forgot about that.

[roji]

FYI I opened dotnet/efcore#12673 to track the possibility of performing the null coalescing inside EF Core rather than requiring users to do it explicitly - this is a case where the C# behavior differs from the SQL behavior.