Friday, January 24, 2014

NHibernate.Linq and PredicateBuilder

Using Linq with NHibernate makes it hard to create custom "OR" clauses in where queries. However there is a small class, PredicateBuilder, which makes is possible.
In the example below persons are fetched based on a search of 'John London', this will return persons with the name 'John' AND city 'London' or vice versa.
// search string
var search="John London";
// split in two criteria
var needles = search.Split(' ');
// create main predicate (AND)
var pbMain = PredicateBuilder.True();
// dynamic add OR predicates
foreach (var needle in needles)
 // create sub predicate (OR)
 var pb = PredicateBuilder.False();
 pb = pb.Or(p => p.Name.Contains(needle));
 pb = pb.Or(p => p.Address.City.Contains(needle));
 // add the sub predicate to the main predicate
 pbMain = pbMain.And(pb);
// pbMain contains "WHERE (NAME = 'John' OR CITY = 'John') AND (NAME = 'London' OR CITY = 'London')"
// run the predicate against NHibernate.Linq Query
var persons = Session.Query().Where(pbMain).ToList();
The sub predicate can be extended by more OR predicates (e.g. PostalCode, Email, Country, etc), which enables a google-like search functionality.