Implement wildcard search

Question

Any thoughts how to add a wildcard search from the line similar to sql 'like%'. in my case to get the result, exact search gives the result. I need result's which match or like based on the searchkey

List = (from x in employee 
        where x.firstname.ToUpper().Contains(searchKey.ToUpper()) 
        || x.fullname.ToUpper().Contains(searchKey.ToUpper())  
        select x)
        .Take(5);

There is no in-built function for this, so far I know, so you will have to implement it yourself eg. https://stackoverflow.com/questions/42130564/string-comparison-with-wildcard-search-in-c-sharp | https://stackoverflow.com/questions/30299671/matching-strings-with-wildcard | https://stackoverflow.com/questions/33112162/compare-two-strings-with-wildcards — Rand Random, Sep 20 '22 at 09:42
Because if `employee` is a DbSet (i.e. it's a database query), then using `.Contains` will be translated into a SQL Like automatically, and there is no need to use `.ToUpper` because case insensitive search is the default. — Neil, Sep 20 '22 at 09:44
@Neil - `case insensitive search is the default` is it really? for all databases (mysql, ms sql, sqlite...), for all cultures (en-us, de-de, fr-fr....)? — Rand Random, Sep 20 '22 at 09:46
@Neil yes its a dbcontext. in my case e.g full name is john a. bravo, when someone search for john bravo. there are no search result — Veck, Sep 20 '22 at 10:00
Check [this solution](https://stackoverflow.com/a/65410864/10646316) it handles small variation of wildcards but it will work with EF. Extending should be simple. — Svyatoslav Danyliv, Sep 20 '22 at 10:04
@RandRandom Unless you have changed it, the default collation for MySql and MsSql are case insensitive. — Neil, Sep 20 '22 at 12:34
@RandRandom ISO SQL requires a case-insensitive default collation for Latin text - and (AFAIK) _every_ even-barely-ISO-SQL-compliant RDBMS around today also defaults to case-insensitive collation. I've never come across an RDBMS that defaults to case-sensitive collation for Latin text. — Dai, Sep 20 '22 at 12:38
To be clear, LINQ to DB implements `StartsWith`, `EndsWith` and `Contains` using a translation to SQL `LIKE`, adding `%` as appropriate, but you can't add additional wildcards yourself. However, depending on your EF version and database (what are they?) you can use something like `EF.Functions.Like(x.fullname, $"%{searchKey.Replace(' '. '%')}%")` — NetMage, Sep 20 '22 at 17:54

score 0 · Answer 1 · answered Sep 20 '22 at 12:36

0

Unless you have changed the default collation (sorting/searching), it is very likely that SQL will do a case insensitive search by default. Using string.Contains will automatically be converted into LIKE %xxx%.

List = (from x in employee 
    where x.firstname.Contains(searchKey) 
    || x.fullname.Contains(searchKey)  
    select x)
    .Take(5);

string.StartsWith and string.EndsWith will also be converted to LIKE "%XXX" and LIKE "XXX%" respectively.

answered Sep 20 '22 at 12:36

Neil

11,059
3
31
56

Of course, the problem with the `LIKE %` operator it isn't SARGable. The main way to have _fast_ substring queries is with a suffix-trie, but those are a PITA to build in an RDBMS - it's often much easier just to fall-back on the RDBMS' built-in full-text indexing feature, if available. – Dai Sep 20 '22 at 12:41

Implement wildcard search

1 Answers1