Why does Entity Framework Core require "top 200" for ContainsTable() order by?

Question

In SSMS the following query works fine, returning the expected 1 row:

SELECT * 
FROM CONTAINSTABLE(AppUsers, *, 'hick', 200) AS t 
INNER JOIN AppUsers u ON u.Id = t.[KEY] 
ORDER BY t.[RANK] DESC

But when I call the following using Entity Framework, the exact same select:

listUsers = await dbContext.AppUsers.FromSqlInterpolated(
        $@"SELECT * FROM CONTAINSTABLE(AppUsers, *, {Query}, 200) as t INNER JOIN AppUsers u on u.Id = t.[KEY] ORDER BY t.[RANK] desc")
        .ToListAsync();

I get an exception:

Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'LouisHowe.core.Data.NoTrackingDbContext'.

Microsoft.Data.SqlClient.SqlException (0x80131904): The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

at Microsoft.Data.SqlClient.SqlCommand.<>c.b__208_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()

ClientConnectionId:34a279b8-9257-4414-b7d0-b0a3c135a4db
Error Number:1033,State:1,Class:15

Now this works:

listUsers = await dbContext.AppUsers.FromSqlInterpolated(
        $@"SELECT top 200 * FROM CONTAINSTABLE(AppUsers, *, {Query}, 200) as t INNER JOIN AppUsers u on u.Id = t.[KEY] ORDER BY t.[RANK]")
        .ToListAsync();

But why do I have to add top 200 as I'm passing the 200 in to CONTAINSTABLE()?

Answer 1

The answer is here:

Composing with LINQ requires your SQL query to be composable, since EF Core will treat the supplied SQL as a subquery. Composable SQL queries generally begin with the SELECT keyword, and cannot contain SQL features that aren't valid in a subquery, such as:

• On SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause