How can I do case insensitive and concatenated field lookups with nhibernate / linq?
I have an asp.net-mvc site that uses Fluent Nhibernate Linq / SQL Server. I have a text box where someone can enter a name and generate the following query, which I am currently using to find the Person table:
return Session.Query<Person>()
.Where(r => (r.LastName.Contains(s) || r.FirstName.Contains(s)));
This works as expected in terms of translation into "SQL query"
Select * from Person where FirstName like '%%' or LastName like '%%'
but I have 2 new requirements that I am not sure what nhibernate linq supports.
-
In some cases, people enter a name in upper or lower case, so I want to be able to do case insensitive searches.
-
Since this is one text box, in some cases people print both the first and last name (something like "Smith, Joe" and that can't find a result given that the common string doesn't exist in either the first or last name fields. Apart from breaking the UI into individual fields (which I cannot do for any other reason) is there any suggestion on how I can support the query to include the following user search string combination
- [First] [Last]
- [Last], [First]
in the above search code.
source to share
To solve the mixed top / bottom problem, we can simply convert both sides to .ToLower()
return Session.Query<Person>()
.Where(r => (r.LastName.ToLower().Contains(s.ToLower())
|| r.FirstName.ToLower().Contains(s.ToLower())));
Check out this link to see how NHibernate InsensitiveLikeExpression.cs works ( it does the same for almost every dialect ):
Second part, here is some super simple algorithm (if there is only one)
var first = searched.Split(' ')[0].ToLower();
var last = searched.Split(' ')[1].ToLower();
var emps = session.Query<Person>()
.Where(e =>
(e.FirstName.ToLower().StartsWith(first)
&& e.LastName.ToLower().StartsWith(last))
||
(e.FirstName.ToLower().StartsWith(last)
&& e.LastName.ToLower().StartsWith(first))
)
A very similar solution can be used (and I) to search for a combobox ... Where "Ra Ko" will also find Radim Kohler ...
source to share
Your first point is
1). In some cases, people enter a name in upper or lower case so I want to be able to make the case insensitive.
The answer is already given by Radim Köhler, which converts both sides into .ToLower()
Now for your second point, below might be helpful.
return session.Query<Person>()
.Where(e =>
((e.FirstName.ToLower() + " " + e.LastName.ToLower()).Contains(s))
||
((e.LastName.ToLower() + ", " + e.FirstName.ToLower()).Contains(s))
)
Some syntax error might occur as I haven't tested it in VS.
source to share