Order by not-selected column

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!

score 0 · Answer 1 · answered Nov 01 '19 at 09:39

0

you can try with

 query = query.OrderBy(x => x.Rank);

OR

 query = query.OrderBy(x => x["Rank"]);

answered Nov 01 '19 at 09:39

Arvind

74
14

1

Alas, `Rank` is not a property on `Customer`, so this does not compile – Rogier van het Schip Nov 01 '19 at 09:47
can you create 1 new modal. this modal inherit customer class and also create Rank property. sett this modal return type – Arvind Nov 01 '19 at 10:45
1

Also tried that :) EF Core rejects this new class, saying query objects and entities may not be in the same inheritance tree. – Rogier van het Schip Nov 01 '19 at 11:13
best way you can call stored procedure and return result – Arvind Nov 01 '19 at 11:36

score 0 · Answer 2 · answered Nov 01 '19 at 12:07

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.

Order by not-selected column

2 Answers2