I'm implementing a autocomplete feature where I need to filter SQL table based on User Input.
So, Requirement is to filter for the records where it should begin(sort) the results with StartsWith and then Contains.
Sample Data:
Dotnet Developer
Azure Administrator
Microsoft Azure
Azure DevOps
UX Developer
IOT Azure
If I start typing Azu, then it should show the results in the below order.
Azure Administrator
Azure DevOps
Microsoft Azure
IOT Azure
which means, we are looking for the results which startswith logic and then contains logic.
Currently, I have tried two approaches but those didn't work.
First Approach:
dbContext.dbTable.Select(h => h.FieldName.ToLowerInvariant()).Where(e => e.Contains(text)).OrderBy(s => s).Distinct().Take(count).ToList();
Second Approach:
dbContext.dbTable.Where(e => e.FieldName.Contains(text)).OrderBy(h => h.FieldName.IndexOf(text)).ThenBy(c => c.FieldName.Length).Select(p => p.FieldName).Distinct().Take(count).ToList();
Can anyone please guide me if this can be possible?
dbContext
.dbTable
.Where(x => x.FieldName.Contains(text))
.Distinct()
.OrderByDescending(x => x.FieldName.StartsWith(text))
.Take(count);
.Where(x => x.FieldName.Contains(text))).OrderByDescending(x => x.FieldName.StartsWith(text)))Your main problem is the requirements or the understanding of the requirements.
Filter operation:
Reduce(=filter) the results based on a boolean expresssion where:
true ==> show the result
false ==> don't show.
It is achieved in linq, by the Where(...) clause.
In your case, the requirement is to Filter by the results that contain text.
.Where(e => e.Contains(text))
Sort Operation:
Given a list of results, decide on the order of them.
Your requirement is to see the values that Start by text first.
In other words, to Sort by this rule.
String.StartsWith returns a boolean.
Running linq's OrderBy on booleans, orders them by false and then by true.
This is because OrderBy default sorting is ascending, and false counts as "smaller" than true. (reasonable decision)
So, knowing this, you can achieve what you want by running
.OrderByDescending(s => s.StartsWith(text))
Full Logic:
dbContext.dbTable
.Select(h => h.FieldName.ToLowerInvariant())
.Where(e => e.Contains(text)) // filter
.Distinct() // more filtering
.OrderByDescending(s => s.StartsWith(text)) // sort
.Take(count)
.ToList();
Try like the following
dbContext.dbTable.Where(e => e.FieldName.Contains(text)).OrderBy(h => h.FieldName.ToLower().StartsWith(searchString.ToLower()) ? 0 : 1)
.ThenBy(c => c.FieldName.ToLower().Contains("order")).Select(p => p.FieldName).Distinct().Take(count).ToList();