0

I am implementing a search feature for the users in our api. The search will query our db to fetch the first N users matching the search term. I want to order the user after "best match" so the most relevant user is on top.

What I'd like to do is something like:

var users = await _dbContext.Users
   .IncludeUserData()
   .Where(u => u.Name.Contains(searchTerm))
   .OrderBy(u => u.Name.IndexOf(searchTerm)) <- This row is not possible
   .ToListAsync();

Where basically a name that contains the search term early is ordered before a user whose name contains the term late.

E.g. Simon Carlsson should come before Carl Simonsson if the searchTerm is "Simon"

Using SQL Server as the DB provider

How would I achieve an order by query where users with names better matching the searchTerm are sorted higher up in the list?

Inua
  • 51
  • 5

2 Answers2

1

After some more searching this method of importing functions from the DB provider was found:

[DbFunction("CHARINDEX", IsBuiltIn = true)]
public static long CHARINDEX(string substring, string str)
{
    throw new NotImplementedException();
}

Put this in user dbContext class. It will bind to the CHARINDEX function in SQL Server. https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping

Then use it to sort the query:

.sortBy(u => DbContext.CHARINDEX(searchTerm, u.name))
Inua
  • 51
  • 5
0

Have you tried the LIKE operator?

You may find this useful

Entity framework EF.Functions.Like vs string.Contains

Jr. Mediocre Coder
  • 84
  • 5