Entity Framework Where() ignored with FromSqlRaw(FreeTextTable)

Question

This is with Entity Framework Core 8. I have the following query:

var fullTextQuery = $"SELECT top {maxRows} * FROM FreeTextTable(Organizations, *, {{0}}, {maxRows}) as t INNER JOIN Organizations u on u.Id = t.[KEY]";

var query = dbContext.Organizations
    .FromSqlRaw(fullTextQuery, queryText);
    .Where(org => org.Name.Contains(itemName))
    .ToListAsync();

This is returning objects that the FreeTextTable() should return, but that the Where() clause should exclude. Is the Where() clause ignored? Or do I need to add it literally into the FromRawSql()? Or something else?

Answer 1

It should work, though there are limits, mainly around stored procedures. That said you should be using FromSql not FromSqlRaw unless absolutely necessary. (FromSqlRaw is needed here for parameterizing SQL statements.)

On a hunch I would consider restructuring the query a little:

var fullTextQuery = $"SELECT u.* FROM Organizations u INNER JOIN FreeTextTable(Organizations, *, {{0}}, {maxRows}) as t ON u.Id = t.[KEY]";

var query = await dbContext.Organizations
    .FromSqlRaw(fullTextQuery, queryText); 
    .Where(org => org.Name.Contains(itemName))
    .ToListAsync();

The "TOP(maxRows)" is covered by the inner join to the top results from the free text search. I would expect this may be enough to get it happy to allow the where clause if EF is satisfied that the query is going to the Organizations table initially.

Otherwise if the query does run but still does not seem to take into account the Where clause, run a profiler to capture the SQL actually used and this might shed some light on some funniness remaining in the query parsing.