Order by best match of string column in EF Core-CodePudding

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?

CodePudding user response：

Have you tried the LIKE operator?

You may find this useful

Entity framework EF.Functions.Like vs string.Contains

CodePudding user response：

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://docs.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))