EF.Functions.Contains(...) and nested queries

[alienwareone]

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed.

I use a table with a complex type (".OwnsOne(...)").

I think it's because of the "unnecessary" LEFT JOIN:
#18299

[ajcvickers]

@alienwareone Please attach a small, runnable project or post a complete, runnable code listing that reproduces the behavior you are seeing so that we can investigate.

[alienwareone]

https://github.com/alienwareone/EfCoreFulltextSearchOwnedTypeBug

[ajcvickers]

Note for team: I can repro this. When the property is not on an owned type, then we generate this:

SELECT [p].[Id], [p].[SearchText]
FROM [Products] AS [p]
WHERE CONTAINS([p].[SearchText], N'''www*''')

Which works. With the owned entity we generate this:

SELECT [p].[Id], [t].[Id], [t].[Lookup_SearchText]
FROM [Products] AS [p]
LEFT JOIN (
    SELECT [p0].[Id], [p0].[Lookup_SearchText], [p1].[Id] AS [Id0]
    FROM [Products] AS [p0]
    INNER JOIN [Products] AS [p1] ON [p0].[Id] = [p1].[Id]
    WHERE [p0].[Lookup_SearchText] IS NOT NULL
) AS [t] ON [p].[Id] = [t].[Id]
WHERE CONTAINS([t].[Lookup_SearchText], N'''www*''')

Which throws:

Error Number:7601,State:3,Class:16
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot use a CONTAINS or FREETEXT predicate on column 'Lookup_SearchText' because it is not full-text indexed.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EfCoreFulltextSearchOwnedTypeBug.Program.Main(String[] args) in /home/ajcvickers/Downloads/EfCoreFulltextSearchOwnedTypeBug-master/EfCoreFulltextSearchOwnedTypeBug/EfCoreFulltextSearchOwnedTypeBug/Program.cs:line 35

[smitpatel]

Expected error. #18299 should resolve this. Note that Contains does not work in all cases especially if a pushdown is caused on it. (Subquery removes Full text index).

[ajcvickers]

Closing as a duplicate of #18299