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?

CodePudding user response：

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));
}

CodePudding user response：

shorter syntax

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

CodePudding user response：

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) '";

CodePudding user response：

 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();