NHibernate query to get objects with properties that match string parts

Question

I have a Person class with the following properties:

 public class Person { public string LastName { get; set; } public string FirstName{ get; set; } public string SecondName { get; set; } public string Position { get; set; } } 

The user is presented with a search box in which they can enter a string separated by spaces. The task is to request from the database all Persons who have any property equal to any part of the entered string (the parts are substrings separated by spaces). The simplest solution is OR or all possible combinations. But, in my opinion, this does not seem right. Are there any more elegant solutions?
Thanks in advance.

Answer 1

My answer begins with the definition of What does elegant mean?

There are such solutions as: -

• Create a calculated column in the database [FirstName] + N '' + [Surname] + N '' + [SecondName], etc. Then you only need OR substrings based on the computed column. Is it elegant / more productive? I'm not sure.
• Create a FREETEXTABLE For more information, see MSDN . Again you only need OR for each substring (based on SQL Server FULL-TEXT sarch).
• Embed LUCENE index in all columns, see this . However, you need to trace the synchronization problems with the database, and you will also need to study the new structure, but the search is definitely IMO more elegant.
• Stay with what you have.

What you decide is up to you, but 1-3 have different problems against what you currently have, which I’m not elegant, but it just and most likely works!