CODEWITHSUNDEEP
c#linqentity-framework
CallumVass
CallumVass

Reputation: 11456

Search multiple database columns using one search box

I was wondering what the best approach would be to search a database via multiple columns by using only 1 search box. Scenario below:

I want to be able to search Customers by inputting their details into a search box. This could be either their name, post code or id. I can also perform 1 search by using more than 1 search criteria. For example I could search by name and id (Bob Smith cus345).

A first approach I took was to split the search term, seperating each space into a seperate word but this has its down fall as a customer name may contain 3-4 words. This can lead to a tonne of conditional statements but this is inefficient so I was wondering if there was a better way of doing this.

I find this a difficult task to do because the user could search in any order (ie search by post code, then name and then id, or name and id and so forth)

Upvotes: 1

Views: 2044

Answers (2)

Adrian Iftode
Adrian Iftode

Reputation: 15683

I used full text search on a project with similar requirements, just instead of EF, I was using linq to sql, but I'm confident the specific full text search functions can be mapped, via table functions, with EF.

Upvotes: 0

Digbyswift
Digbyswift

Reputation: 10410

You could use regular expressions to detect specific terms like post codes but you are right in saying that either way you will end up with a load of conditions.

I would highly recommend using Lucene.net. It is not difficult to implement but it provides ranked results by default, is lightning fast (faster than a stored proc would be against a multi-termed query) and will cater easily for the kind of search you are describing. You can also weight/bias certain fields, so a partial post code match has more weight than a first name match.

With regards to splitting a search, I usually run a check for reserved characters like * and (, or even strip them out completely if I don't want the user to be able to perform complex searches themselves. I then split upon word boundarys and perform a AND search.

As far as I know there is also a Lucene to Linq provider too.

Upvotes: 2

Related Questions