Repository navigation
Impossible to query a function using a full text index from FromSql #8895
Description
I am trying to call a function using a full text index, however whatever I try, I always get an exception The full-text query parameter for Fulltext Query String is not valid.
I tried to add a cast surrounding the parameter within the strSQL and to change the SQL function to accept varchar(4000), but nothing works (I just need varchar(50) ).
I haven't tried to pass a DbParameter with the correct type instead of a string, but I read that DbParameter were not yet supported.
The call from the DBContext class is the following:
var strSQL = $"SELECT * FROM dbo.GetClosestPlaces(@p0, @p1, @p2, @p3)";
await this.Set<Places>().FromSql(strSQL, lat, lng, maxResults, search)The SQL request is quite simple
SELECT top(@maxResults)* FROM Places
WHERE ([geography]::Point(@lat,@lng,(4326)))
.STDistance(Coordinates)
< @maxRadius
AND CONTAINS(Tags, @search) The full-text query parameter for Fulltext Query String is not valid.
fail: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[1]
An exception occurred in the database while iterating the results of a query.
System.Data.SqlClient.SqlException: The full-text query parameter for Fulltext Query String is not valid.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__107_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__26.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<BufferlessMoveNext>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__6`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.<MoveNext>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
ClientConnectionId:04f2416c-a42a-4181-88c8-
Error Number:30020,State:101,Class:16
Further technical details
EF Core version: 1.1.2
Database Provider: SQL Server / Default EFCore provider to Azure database
Activity
ErikEJ commentedon Jun 19, 2017
What does GetClosestPlaces look like?
JeanCollas commentedon Jun 19, 2017
It is just a select, ordering by distance, and having
AND CONTAINS(Tags, @search)(Updated in the first post.)
ajcvickers commentedon Jun 19, 2017
@JeanCollas You should be able to create a SqlParameter object and pass that as the parameter to the FromSql call. If this doesn't work, then please re-open with the full stack trace and message of the failure.
I have also added a note to #4978 to consider this case and I'm closing this as a duplicate of that.
JeanCollas commentedon Jun 20, 2017
Indeed SqlParameter with the right type worked.
smitpatel commentedon Sep 1, 2018
The
CONTAINScall is insideFromSqlquery string. We don't process the string. Hence it is not possible for us to infer any kind of type mapping based on parameters. (Also there couldn't be unknown columns). FurtherCONTAINSrequires that the parameter of type string to be used but it should NOT be ofnvarchar(max). For string that is our default type mapping. The only thing we (could) do is based on CLR type of parameter pick the default type mapping. But if the SQL requires parameter of certain type then use should useDbParameterwith appropriate type set.Re-opening so we can discuss in triage again.