Recently, while writing one of the NHibernate queries, using the NHibernate LINQ provider we faced a very interesting problem.
The issue was the NHibernate LINQ provider was not adding appropriate parenthesis to make the operator precedence more explicit.
The query was simple enough, we wanted to get a list of records based on certain condition. Let's say, for the sake of this post, consider that there is an employees table with ID, Name and Salary columns. The DDL to create the employees tables on SQLServer looks like
NHibernate class against which this table is mapped is called Employee. The Employee class and its mapping file looks like
As you can see there is nothing fancy about the table or the mapping.
Let's talk a bit about the query we want to fire against the employees table. We want to get list of employees whose name equals "Git" OR whose salary is between 1000 and 10000.
The SQL that would do the job for us would look like:
Again nothing remotely fancy about the SQL.
Let's try and write the same query using, NHibernate LINQ provider and lets look at the generated SQL. The NHibernate LINQ query to get similar results would look like:
The where condition is the exact replica of the SQL where condition that we had seen earlier. Only difference this time around is, we are using the LINQ syntax.
So far so good, the problem starts when we look at the SQL that is generated. The simplified version of the SQL generated from the above LINQ query looks like
I have removed all the named parameters that NHibernate adds, but essentially the query looks like the one shown above.
Whats wrong with the query?
Notice that there are no parenthesis to enforce the operator precedence! Because of this, it might get confusing to identify the order in which the operators will be process.
Consider a case when you have a complex query with many conditions and if proper parenthisis are not applied then it could get confusing.
I tried various permution and combination to force the NHibernate LINQ provider to spitout the parenthesis correctly, however I was not successful in doing so.
Finally, I decided to switch to the QueryOver API provided by NHibernate. QueryOver API is a Fluent wrapper over the NHibernate Criteria API. Using the QueryOver API the above code would look like
As you can see the nothing much has changed. The only changes are, .Query() call has been changed to .QueryOver() and instead of .ToList() we have to call .List().
Let's look at the generated SQL Query:
As you can see, it correctly adds the parenthesis to make the operator precedence more explicit!
