Derik Whittaker

Syndication

News


Creating a nested ‘Or’ statement with NHibernate using the Criteria convention

As I pointed in a prior post (here) when trying to learn to harness the power of NHibernate sometimes the ‘simple’ can be a bit daunting at first.  For instance how do you create a query with NHibernate that looks a little like the information below?

Where ....
	AND 
	( 
		( a = x AND b = true )
		or
		( a = y AND b = true )
		or 
		( a = z AND b = true )
	)

This would  be actually pretty easy if all you needed to do was have a single Or statement because there is a helper method in NHibernate (Expresssion.Or) which takes in 2 parts.  However, what do you do if you need to both have more than 2 parts to your Or statement as well as add in a And clause to each one.  Well, you have to break away from the helper methods and go straight to the Restrictions class.

The code below is a snippet which should help to show how you can build a criteria which creates the final sql in the same way as above.

// criteria is an instance of the DetachedCriteria object

var queuedCriterion = new List<AbstractionCriterion>();

// build out each of the 'Or' statements
foreach( var someObject in listOfSomeObjects )
{
	var partOneExpression = Expression.Eq( "A", "x" );
	var partTwoExpression = Expression.Eq( "B", true );
	queuedCriterion.Add( Expression.And( partOneExpression, partTwoExpression ) );
}

// put each of the 'Or' statements into one disjunction.
// this will allow them to be nested on the same level
var orDisjunction = Restrictions.Disjunction();
foreach ( var criterion = queuedCriterion )
{
	orDisjunction.Add( criterion );
}

// finally add the full list 'Or' into the main criteria object
criteria.Add( orDisjunction )

As you can see above  there is a bit of code needed to create this sql syntax when using the criteria, but it is not too bad (IMO).  The more I learn about NHibernate, the more I really start to appreciate its power and flexibility.

Till next time,


Posted 04-21-2009 4:26 PM by Derik Whittaker
Filed under: ,

[Advertisement]

Comments

jbland wrote re: Creating a nested ‘Or’ statement with NHibernate using the Criteria convention
on 04-22-2009 12:47 PM

if im not mistaken, restrictions have operator overloads, so wont the following work

<code>

   var partTwo = Expression.Eq( "B", true );

criteria.Add(

       ( Expressions.Eq("a",x) && partTwo )  

       ||

       (Expressions.Eq("a",y) &&  partTwo)  

       ||  

       (Expressions.Eq("a",z)  &&  partTwo )  

)

</code>

Derik Whittaker wrote re: Creating a nested ‘Or’ statement with NHibernate using the Criteria convention
on 04-22-2009 12:52 PM

@JBland,

I do not know, i will have to give this a shot and find out.

Thanks for the info.

Sergey Shishkin wrote re: Creating a nested ‘Or’ statement with NHibernate using the Criteria convention
on 04-22-2009 4:02 PM

How about this?

(a in [x, y, z]) and (b == true)

Boolean logic rulez ;)

Derik Whittaker wrote re: Creating a nested ‘Or’ statement with NHibernate using the Criteria convention
on 04-22-2009 4:06 PM

@Sergey,

For this code that would work.  In my real code that would not.  I actually did not mean to make them all have b = false

kitchenaid parts wrote re: Creating a nested ‘Or’ statement with NHibernate using the Criteria convention
on 04-23-2009 12:16 AM

This tutorial are so nice. So many thing I learn here.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)