Geek Answers Handbook

Fuzzy search by combined full name using NHibernate

I am trying to convert the following SQL to NHibernate:

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

I tried to do something like this, but it does not work:

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 search for the client name in the text box with the value of the example "bob smith" and to search for it in the database using the LIKE expression in SQL above.

If I mistakenly look for the FirstName and LastName columns, please help me with the alternative, but the above SQL query will give me what I need.

Update using two solutions:

So, I found two solutions to this problem. One of them is to use the criteria API. There is an answer in the following post that works fine: https://stackoverflow.com/a/166268/

, , LINQ . 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();
+5
4

NHibernate sql, , c = > c.FirstName + '' + c.LastName. :

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

, , . . , -!

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);
+1

, :

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

Because you are not comparing the correct values ​​the way you are doing right now.

0
source

I can try the following:

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

If it is too complicated with QueryOver or Criteria API, you can use HQL syntax

0
source

More articles:


All Articles