ToTSVector Document/Column Name Serialized With Single Quotes

[realmvpisme]

Hi! I'm trying to perform full text searches on a single table column using the EF.Functions.ToTsVector() & ToTsQuery() extension methods.

The issue I'm seeing is that the document/column name entered in ToTsVector() is always serialized to a string value with single quotes while the to_tsvector() function in PostgreSQL expects a double quoted column reference.

For example: Here's an example of the extension method usage:

dbContext.Addresses.Where(x => EF.Functions.ToTsVector("english", "Formatted").Matches(EF.Functions.ToTsQuery("english", '4404 & Jellystone Parkw:*'));

And here's the generated SQL. Note that the "Formatted" column is in single quotes.

-- @__ToString_1='4404 & Jellystone & Parkw:*'
-- @__p_2='10'

SELECT a."Id", a."StateCode", a."Building", a."City", a."Country", a."CountryCode", a."Created", a."Deleted", a."Floor", a."Formatted", a."HouseNumber", a."Latitude", a."Longitude", a."Source", a."SourcePlaceId", a."State", a."Street", a."Type", a."Unit", a."Updated", a."ZipCode"
FROM addresses AS a
WHERE NOT (a."Deleted") AND to_tsvector('english', 'Formatted') @@ to_tsquery('english', @__ToString_2)
LIMIT @__p_2

This query does not return any results but if I replace the single quotes around 'Formatted' with double quotes it works as expected.

[roji]

It sounds like Formatted is a column in your table; if that's the case, you need to reference the .NET property that's mapped to it:

dbContext.Addresses.Where(x => EF.Functions.ToTsVector("english", x.Formatted).Matches(EF.Functions.ToTsQuery("english", '4404 & Jellystone Parkw:*'));

Otherwise, EF is simply translating your LINQ query directly; since "Formatted" is a string literal in your .NET LINQ query, it's translated to a string literal in SQL ('Formatted').

[realmvpisme]

The problem with that approach is that I'm calling ToTsVector() from within a generic extension method. The entity and property types aren't known up front. Is it even possible to pass the column name to ToTsVector() as a string?

[roji]

EF generally isn't well-suited for fully dynamic scenarios where entity types and properties aren't known up-front. However, you should be able to specify the column name as a string via EF.Property:

dbContext.Addresses.Where(x => EF.Functions.ToTsVector("english", EF.Property(x, "Formatted")).Matches(EF.Functions.ToTsQuery("english", '4404 & Jellystone Parkw:*'));

[realmvpisme]

@roji Using EF.Property() worked. Thank you!