Order by not-selected column

c# contains containstable entity-framework-core linq

Question

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 Customer entity.

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!

1
2
11/1/2019 9:35:56 AM

Popular Answer

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.

0
11/1/2019 12:07:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow