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 where they can enter a string separated with spaces. The task is to query from the DB all the Persons that have any property equal to any part of the entered string (the parts are substrings split by spaces). The simplest solution is to OR all possible combinations. But, in my sense, it doesn't seem right. Are there more elegant solutions?
Thanks in advance.

I tried the solution I've mentioned above. I've ORed all the combinations of each property and substring. — Dmitriy Melnik
– Dmitriy Melnik, Commented Mar 11, 2012 at 11:54
can you show us what you have tried? It seems you would only want to OR the combinations together that have been entered using a list of restrictions filtering on fields. — Mike McMahon
– Mike McMahon, Commented Mar 11, 2012 at 18:39
Yes, I do OR my properties and parts of the entered string now. Something like that: foreach (var s in substrings) foreach (var p in properties) criterion = Restrictions.Or(criterion, Restrictions.InsensitiveLike(p, string.Format("%{0}%", s))); And based on the given answer I'm going to stick with this solution. — Dmitriy Melnik
– Dmitriy Melnik, Commented Mar 12, 2012 at 2:52

Rippo · Accepted Answer · 2012-03-11 18:30:35Z

2

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

There are solutions such as:-

Create a computed column on the database [FirstName] + N' ' + [Surname] + N' ' + [SecondName] etc. Then you only need to OR the substrings based on the computed column - Is this elegant/more performant? I for one am not so sure.
Create a FREETEXTABLE See MSDN for more info. Again you only need to OR against each substring (based on SQL server FULL-TEXT sarch).
Implement a LUCENE index on all the columns, see this. However syncing problems with the database needs to be thought about and also you will need to learn a new framework BUT searching is definitely IMO more elegant.
Stay with what you got.

Which you decide is up to you but 1-3 have different issues against something that you currently have, which my not be elegant, but it is simple and most likely works!

answered Mar 11, 2012 at 18:30

Rippo

22.5k14 gold badges83 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitriy Melnik Over a year ago

It seems I'm overcomplicating the matter. I'll use what I already use. Thanks!

Collectives™ on Stack Overflow

NHibernate query to get objects with properties that match string parts

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related