0

How can a NHibernate query be written to order by likeness?

From this example 

session.Linq<Theater>()
.Where(x => x.Name.StartsWith("test") && x.Name.EndsWith("test");

Source: NHibernate.Linq LIKE

How could the order of the results returned be ordered by 'Likeness'?

i.e.

With a result set of

  • John Doe
  • Jane Doel
  • Janice Mander

If I were to search for 'Doe' I would get the results of (in this order)

  • John Doe
  • Jane Doel

Thanks in advance.

Community
  • 1
  • 1
Jar N
  • 1
  • Try soundex func http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex –  Dec 03 '15 at 19:29

1 Answers1

0

As far as I know, the Where clause occurs first, and the Order By is executed on the result set, so in order to list the items by their "likeness" you would need to do some fancy sequence checking in an expanded query format (not LINQ) or in C# natively after you get the result set by re-ordering it yourself.

It all depends on what your definition of likeness is. By default ascending or descending is how the string is listed in a dictionary. If you wanted to base it on the index of where the LIKE '%word%' occurs, then you still need to specify that criteria in the Order By statement separately.

You could use substrings and drop them into cases and order by those cases.

order by case
when SUBSTRING(field,1,Len(field)) LIKE 'word%' then 1 
when SUBSTRING(field,2,Len(field)) LIKE 'word%' then 2
when SUBSTRING(field,3,Len(field)) LIKE 'word%' then 3
...
end

See here: Mysql: Order by like?

and here: Find all strings that share at least X characters, order by likeness

EDIT: Create Criteria might be helpful for making this work in LINQ, but its easier to use expanded syntax IMO. nhibernate CreateCriteria wildcard Like when

Community
  • 1
  • 1