How can I retrieve products from database with Full-Text index in ASP.NET Core 3

Question

I've created a Full-Text index on my Products table in SQL Server. I'm trying to find the best way to retrieve those products (during a search) in order of rank.

The following SQL shows the products in order of rank:

select ID, k.rank, Name from Products st
inner join freetexttable(Products, Name, 'orange fanta') as k on st.ID=k.[key]
order by rank desc

I tried using EntityFramework's method EF.Functions.FreeText() this doesn't order them by rank.

I tried using FromRawSql:

products = _appDbContext.Set<Product>().FromSqlRaw("select ID, k.rank, Name from Products st inner join freetexttable(Products, Name, '{0}') as k on st.ID = k.[key] order by rank desc", currentFilter);

This throws the following error:

SqlException: 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.

Having searched around, the common reason for this error seems to be using order by nested inside another select this is not the case here.

I've tried calling a Stored Procedure that was created using this code:

products = _appDbContext.Set<Product>().FromSqlRaw("FreeTextSearch {0}", currentFilter);

but get this error:

InvalidOperationException: FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.

I tried adding AsEnumerable to the end, but get an error:

InvalidOperationException: The required column 'Active' was not present in the results of a 'FromSql' operation.

'Active' is a bool column on the products table to know whether the product is active and should be shown or not.

What is the best way to go about this?

Answer 1

If you read that last error message, it says that the Active column wasn't present in the results. If that's a column in your table, and so a property in the Product model, then EF will need it (and all other non-nullable properties of the model) to create the Product entities.

Try changing your SQL to start...

select st.* from Products st

...and see if that helps.