Linq Query For AutoComplete Match With Multiple Columns C#

Question

I am implementing search feature on my blog site ,each blog may contains title,slug,post content etc ,which should work like this ,

Goal

When user type a keyword it should search for that string contains in any of 3 column in Sql Server in efficient way.

Inspiration Source

Source

What I am Using

 db.postTBs.Where(a => a.isApproved == true && a.isShow == true && a.pageKeyword.Contains(query) || a.pageDescribtion.Contains(query) || a.pageTitle.Contains(query) || a.postContent.Contains(query)).Select(a => a.title).ToList();

but I know this is not good way to get the result,This query searching upto specific string length of these 3 columns but when I have long string like

Only buy from reputable dealers. Any reputable dealer will only buy directly from the company or from a trusted distributor. Check the price. Does the price look too good to be true? It probably is. While there are many honest dealers on these sites, Amazon and eBay sellers are the biggest culprits when it comes to selling fake strings. Check the seller’s Amazon or eBay stores to verify who they are. If someone’s price is drastically lower than what you’ve been seeing, there’s a good chance this is not a legitimate string being sold. Buy your instrument from a reputable dealer too! Many cheap in

and I search for

Amazon and eBay

the result does not contain the actual post

but this one matches

On

I have also applied Non cluster key on both 3 columns

Please help how to make search efficient and accurate using linq query c#

Answer 1

If you ready to use third party dll in your project then please visit here

Just download nuget package Install-Package NinjaNye.SearchExtensions

From the link you can use

1) Containing: Search for a single search term within multiple properties

var result = queryableData.Search(x => x.Property1,
                                  x => x.Property2,
                                  x => x.Property3)
                          .Containing("searchTerm");

2) ContainingAll: Search where all search terms exist across multiple properties

var result = queryableData.Search(x => x.Property1,
                                  x => x.Property2,
                                  x => x.Property3)
                          .ContainingAll("search", "term");

3) EqualTo: Search where any one of multiple properties is equal to a single search term

var result = queryableData.Search(x => x.Property1,
                                  x => x.Property2,
                                  x => x.Property3)
                          .EqualTo("searchTerm");

4) StartsWith: Search where any one of multiple properties starts with to a single search term

var result = queryableData.Search(x => x.Property1,
                                  x => x.Property2,
                                  x => x.Property3)
                          .StartsWith("searchTerm");

5) EndsWith: Search where any one of multiple properties ends with a single search term

var result = queryableData.Search(x => x.Property1, 
                                  x => x.Property2,
                                  x => x.Property3)
                          .EndsWith("searchTerm");