Weird "Cannot use a CONTAINS or FREETEXT predicate on column 'Name' because it is not full-text indexed." error

Question

I have followed the article here to implement Full text search with entity framework.

I have 2 search boxes in my form. One for search can be used for name and number and the other search box is used to search for address.

Some a weird error message I get from the sql server as below:

Cannot use a CONTAINS or FREETEXT predicate on column 'Name' because it is not full-text indexed.

But this error is misleading. This is not the real issue. I have already created full text search and added Name, Number, and Address in the catalog.

Then I have run Sql profiler to get the actual SQL query that is generated by EF as below:

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name], 
[Project1].[Number] AS [Number],
[Project1].[Address] AS [Address]
FROM ( SELECT 
    [Filter1].[Id1] AS [Id], 
    [Filter1].[Name] AS [Name], 
    [Filter1].[Number] AS [Number],
    [Filter1].[Address] AS [Address]
    FROM (SELECT 
    [Extent1].[Id] AS [Id1],
    [Extent1].[Name] AS [Name],
    [Extent1].[Number] AS [Number],
    [Extent1].[Address] AS [Address] FROM [dbo].[Company] AS [Extent1]
        INNER JOIN [dbo].[Status] AS [Extent2] ON [Extent1].[CompanyStatusId] = [Extent2].[Id]
        INNER JOIN [dbo].[Category] AS [Extent4] ON [Extent1].[CategoryId] = [Extent4].[Id]
        WHERE [Extent1].[CategoryId] IN (1) ) AS [Filter1]

    WHERE ((contains([Name],'test')) OR (contains([Number], 'test'))) AND (contains([Address], 'blah'))
)  AS [Project1]
ORDER BY [Project1].[Number] ASC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 

When I run the query I get the same error. I cannot figure out where the actual issue is with this query.

I am not sure why EF is generating very complex query and sub queries which seems to me redundant but beside that I cannot figure out what the the underline issue with this query.

Any idea?

Thanks

Answer 1

I encountered this error when using that FtsInterceptor code you linked to, but only when using a query that joined enough tables to make the contains clause refer to a [Filter] column, as in (contains([Filter1].[Title], @p__linq__0). Your query doesn't include the column name in the contains function for some reason, but from the nesting it does look like it's probably inferring the [Filter1] alias.

Anyway, what seemed to fix the problem for me was moving the Contains clause, i.e. query.Where(s => s.Title.Contains(fts)), to the very top of the query so that it was the first clause. This changed it to use the [Extent1] table, as in (contains([Extent1].[Title], @p__linq__0), which worked.