Monday, May 19, 2014

NHibernate logging in Visual Studio output window

NHibernate 'show_sql=true' does not log the generated queries to the output window of Visual Studio when working in ASP.NET MVC 4.
I made a simple implementation for logging NHibernate queries in Visual Studio's output window, without use of third party loggers.

Create an interceptor class, and override one method
public class NHLogger : EmptyInterceptor, IInterceptor
{
    public override NHibernate.SqlCommand.SqlString 
       OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        System.Diagnostics.Debug.WriteLine("NHIBERNATE: " +  sql);
        return base.OnPrepareStatement(sql);
    }
}

Then create an instance of it in the OpenSession of the Factory.
SessionFactory.OpenSession(new NHLogger());

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.