Fuzzy Search on a Concatenated Full Name using NHibernate

Question

I am trying to convert the following SQL into NHibernate:

SELECT * FROM dbo.Customer
WHERE FirstName + ' ' + LastName LIKE '%' + 'bob smith' + '%'

I was trying to do something like this but it is not working:

name = "%" + name + "%";

var customers = _session.QueryOver<Customer>()
            .Where(NHibernate.Criterion.Restrictions.On<Customer>(c => c.FirstName + ' ' + c.LastName).IsLike(name))
            .List();

What I'm basically trying to do is be able to search for a customer's name in a text box with the example value of "bob smith" and for it to search the database using the LIKE expression in the SQL above.

If I'm going about searching the FirstName and LastName columns wrongly, please help me out with an alternative but the above SQL query gets me what I need.

Update with 2 solutions:

So I've now found two solutions to this problem. One is to use the Criteria API. The following post has an answer that works great: https://stackoverflow.com/a/2937100/670028

The other solution I found thanks to one of my helpful coworkers who suggested using a LINQ projection and anonymous types. Here's a solution using LINQ:

var customers = session.Query<Customer>()
    .Select( x => new { FullName = x.FirstName + " " + x.LastName, Customer = x } )
    .Where( x => x.FullName.Contains( "Bob Smith" ) )
    .Select( x => x.Customer )
    .ToList();

You'd have to use `Restrictions.Like` on a Projection using a concat sqlfunction. I don't think NH is smart enough to build the string concat projection for you. — dotjoe, Jul 15 '11 at 16:04
Update: I found the NHibernate code to be just too messy in this case and simply went with a stored procedure. I really try to avoid stored procedures as much as possible but sometimes it's just easier to crank out the sql yourself. — Randy Burden, Aug 23 '11 at 00:39

Peter · Accepted Answer · 2012-09-17T13:47:44.753

11

NHibernate is not able to translate the expression into a sql statement because is does not know what to do with c => c.FirstName + ' ' + c.LastName. A solution can be rewriting this to something like this:

Session.CreateCriteria<Customer>()
    .Add(Restrictions.Like(
    Projections.SqlFunction("concat",
                            NHibernateUtil.String,
                            Projections.Property("FirstName"),
                            Projections.Constant(" "),
                            Projections.Property("LastName")),
    "Bob Whiley",
    MatchMode.Anywhere))

edited Sep 17 '12 at 13:47

answered Jul 15 '11 at 18:37

Peter

27,590
8
64
84

I revisited this post and tried this code and although it didn't work correctly it got me on the right track to look up other solutions. The following solution from another StackOverflow answer worked perfectly, just thought I'd share: http://stackoverflow.com/a/2937100/670028 – Randy Burden Aug 31 '12 at 16:45
hi randy, if you got it working you can edit the post to correct the answer and then mark it as right answer to share it with the world. – Peter Sep 01 '12 at 09:50

Reaction21 · Answer 2 · 2013-03-20T16:48:47.997

If you would like to keep your code as strongly-typed as possible, here is how I achieved it. I needed to use it in a Disjunction. Hope this helps someone!

var disjunction = new Disjunction();
var fullNameProjection = Projections.SqlFunction(
    "concat",
    NHibernateUtil.String,
    Projections.Property<UserProfile>(x => x.FirstName),
    Projections.Constant(" "),
    Projections.Property<UserProfile>(x => x.LastName)
    );
var fullNameRestriction = Restrictions.Like(fullNameProjection, searchText, MatchMode.Anywhere);
disjunction.Add(fullNameRestriction);

score 0 · Answer 3 · answered Jul 15 '11 at 15:51

0

I would think it would something like this:

.On(c => c.IsLike(c.FirstName + ' ' + c.LastName))

Because you aren't comparing the right values the way you have it right now.

answered Jul 15 '11 at 15:51

BAKeele

297
1
5

score 0 · Answer 4 · answered Jul 15 '11 at 17:10

0

I can try this :

query.Where(Restrictions.On<MyType>(x => x.Field).IsLike(StringToSearch))

If it's too complexe with QueryOver or Criteria API you can use HQL syntax

answered Jul 15 '11 at 17:10

TheBoubou

19,487
54
148
236

Fuzzy Search on a Concatenated Full Name using NHibernate

4 Answers4

Linked