Performing a wildcard search

Question

I have a search where I use LINQ with EF. When ever the search criteria are null or empty I need to return everything. Currently I've used if conditions as a solution. and from that I moved to a solution like this.

data = data
.Where(p => !string.IsNullOrEmpty(searchriteria1)? p.field1.Contains(searchriteria1) : true)
.Where(p => !string.IsNullOrEmpty(searchriteria2)? p.field2.Contains(searchriteria2) : true);

Is there a better way to do this? maybe use an extension or any better approach?

What's the criteria? It matters a lot. Without using full text search indexes such a search will have to scan the entire table, even after removing the needless check inside `Where`. Full text search can work only with full words and sentences, and their various forms though. If you want to retrieve `potato` by searching for `ta`, you needed something different. — Panagiotis Kanavos, Dec 06 '21 at 07:01

score 1 · Answer 1 · answered Dec 06 '21 at 06:58

You could check the search criteria field previously and build up the query this way:

IQueryable<Foo> data  = context.Foo.AsQueryable();

if(!string.IsNullOrEmpty(searchriteria1))
{
    data = data.Where(p => p.field1.Contains(searchriteria1));
}
if (!string.IsNullOrEmpty(searchriteria2))
{
    data = data.Where(p => p.field2.Contains(searchriteria2));
}

Panagiotis Kanavos · Answer 2 · 2021-12-06T14:24:30.793

There are two parts to the question. How to filter dynamically and how to filter efficiently.

Dynamic criteria

For the first question, there's no need for a catch-all query when using LINQ. Catch-all queries result in inefficient execution plans, so it's best to avoid them.

LINQ isn't SQL though. You can construct your query part by part. The final query will be translated to SQL only when you try to enumerate it. This means you can write :

if(!String.IsNullOrEmpty(searchCriteria1))
{
    query=query=.Where(p=>p.Field1.Contains(searchCriteria1);
}

You can chain multiple Where call to get the equivalent of multiple AND criteria.

To generate more complex queries using eg OR you'd have to construct the proper Expression<Func<...,bool>> objects, or use a library like LINQKit to make this bearable.

Efficiency

Whether you can write an efficient query depends on the search criteria. The clause field LIKE '%potato%' can't use any indexes and will end up scanning the entire table.

On the other hand, field LIKE 'potato% can take advantage of an index that covers field because it will be converted to a range search like field >='potato' and field<='potatp.

If you want to implement autocomplete or spell checking though, you often want to find text that has the fewest differences from the criteria.

Full Text Search

You can efficiently search for words, word variations and even full phrases using Full-Text Search indexes and FTS functions like CONTAINS or FREETEXT.

FTS is similar to how Google or ... StackOverflow searches for words or sentences.

Quoting form the docs:

CONTAINS can search for:

A word or phrase.

The prefix of a word or phrase.

A word near another word.

A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

FREETEXT on the other hand is closer to how Google/SO work by searching for an entire phrase, returning close matches, not just exact matches.

Both CONTAINS and FREETEXT are available in EF Core 5 and later, through the DbFunctions.Contains and DbFunctions.FreeText functions.

This means that if you want to search for a word or phrase, you could construct a proper FTS argument and use :

var searchCriteria1="' Mountain OR Road '";
if(!String.IsNullOrEmpty(searchCriteria1))
{
    query=query=.Where(p=>DbFunctions.Contains(p.Field1.Contains(searchCriteria1));
}

That's a lot easier than using LINQKit.

Or search for ride, riding, ridden with :

var searchCriteria1="' FORMSOF (INFLECTIONAL, ride) '";

`query=query=.Where(p=>DbFunctions.Contains(p.Field1.Contains(,searchCriteria1));` doesn't compile — Impostor, Dec 06 '21 at 10:13

Impostor · Answer 3 · 2021-12-06T07:05:16.177

0

shorter syntax

data.Where(p => (string.IsNullOrEmpty(searchriteria1) || p.field1.Contains(searchriteria1)) 
             && (string.IsNullOrEmpty(searchriteria2) || p.field2.Contains(searchriteria2)));

edited Dec 06 '21 at 07:05

answered Dec 06 '21 at 07:02

Impostor

2,080
22
43

The check inside `Where` only results in a bad query execution plan. It's not needed at all with LINQ. This is borderline downvoteable, especially since the correct syntax is already posted as an answer – Panagiotis Kanavos Dec 06 '21 at 07:03
`Where(x => true)` doesn`t cause a bad query execution plan – Impostor Dec 06 '21 at 07:05
That's not what you wrote though. The entire *expression* will be translated to SQL. What you posted will be translated to something like `WHERE @criteria is not null and len(@criteria)>1 and field LIKE @criteria)=1`. Even if this was simplified to just `WHEN (@criteria is not null and field LIKE @criteria)` you'd end up with a bad execution plan half of the time. That's because the execution plan generated by the first call is cached and reused, even if it's not suitable. The [problems with catch-all queries](https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) are well known – Panagiotis Kanavos Dec 06 '21 at 07:11

score 0 · Answer 4 · edited Dec 06 '21 at 07:49

0

 public static List<Test> getAll(Expression<Func<Test, bool>> filter = null)
    { 
        return filter == null ? context.Set<Test>().ToList() : context.Set<Test>().Where(filter).ToList();
      
    }

If you want to filter

var l=getAll(p => p.field1.Contains(searchriteria1)&&p.field2.Contains(searchriteria2));

no filter

var l=getAll();

edited Dec 06 '21 at 07:49

Dharman

30,962
25
85
135

answered Dec 06 '21 at 07:44

B_Cbk

78
11

Performing a wildcard search

4 Answers4