I'm trying to use the SQL operator
CONTAINSTABLE to get a list of search results, like this:
SELECT c.*, ccontains.[RANK] FROM Customers c INNER JOIN CONTAINSTABLE(Customers, LastName, @searchTerm) ccontains ON c.Id = ccontains.[KEY]
And calling this function from EF Core 2.1:
var query = DbContext.Customers.FromSql("SELECT * FROM udfSearchCustomers(@searchTerm)", new SqlParameter(@searchTerm, mySearchTerm)); query = query.Include(c => c.Addresses).Take(maxResults);
I want to order my search results descending by
RANK, to get the most relevant results at the top. Adding an
ORDER BY ccontains.[RANK] to my function is not allowed, as my
SELECT * FROM udfSearchCustomers(...) will be wrapped by EF Core:
ORDER BY is not allowed on an inner query. Adding
query.OrderBy(c => c.Rank) is not possible, as
RANK is not on the
I've tried using
System.Linq.Dynamic, as well as other reflection solutions, to do this:
query = query.OrderBy("Rank");
But I got an exception:
"Rank" is not a member of type "Customer"
which is true. Is there any way to order on a column not on an entity, or will I need to create a
MyCustomerSearchQuery query object and use AutoMapper to convert those to
Customer? I'd rather not, as
Customer has many properties and keeping those in sync will be a hassle.
Thanks in advance!
You can create the stored procedure of the query which takes two parameter : @searchKey, @orderByColumn.
CREATE PROCEDURE [dbo].[UdfSearchCustomers] @searchTerm varchar(50), @orderByColumn varchar(50) AS BEGIN DECLARE @sql NVARCHAR(MAX); SET @sql =' SELECT c.*, ccontains.[RANK] FROM Customers c INNER JOIN CONTAINSTABLE(Customers, LastName, ''@searchTerm'') ccontains ON c.Id = ccontains.[KEY] ORDER BY @orderByColumn' SET @sql = REPLACE(@sql, '@orderByColumn', @orderByColumn) SET @sql = REPLACE(@sql, '@searchTerm', @searchTerm) exec sp_executesql @sql END GO
Then you can query the same stored procedure as:
var query = DbContext.Customers.FromSql("exec UdfSearchCustomers @p0, @p1", mySearchTerm, "Rank");
If you want to add join to the address table then you can add the join to the stored procedure. This may give you your desired result.