Posts Tagged ‘query’

Howdy ladies and gents out there,

Here we go with another NHibernate common problem solved for you. Recently the question arose whether it is possible or not to execute a query with NHibernate that checks whether a property lies in a given range of values or not. Not much of a problem, we would say, since HQL can do it just directly using its SQL-like syntax. For example, if we recall our example DevJour1 from part 1 of the NHibernate noob series, we could define a query in the Book.hbm.xml file:

   1: <query name="GetAllBooksWithinRange">

   2:   <![CDATA[

   3:     select b from Book b where b.Title in ('It', 'Salem's Lot',                                                  'Langoliers');

   4:   ]]>

   5: </query>

Snippet 1: Excerpt from the Book.hbm.xml file: HQL query returning all books whose title lies within a certain range of values.

Problem solved? Not quite. Another requirement to this query was to provide custom sorting w/ a custom field and a custom sort direction. Sounds familiar, doesn’t it? Remember our first NHibernate troubles post: Custom Sorting. Simply put, using HQL, this is not possible. So we had to use Criteria queries. But how do we implement the range value check for a given property using Criteria queries? It’s simple, really:

   1: public static List<Book> GetAllBooksWithinRange()

   2: {

   3:     String[] titles = { "It", "Salem's Lot", "Langoliers" };

   4:     ISession session = Program.OpenSession();

   5:     List<Book> books = session.CreateCriteria(typeof(Book)).

   6:         Add(Restrictions.In("Title", titles)).

   7:         List<Book>().ToList<Book>();

   8:     return books;

   9: }

Snippet 2: Excerpt from the Book class.

BookGetAllBooksWithinRange()explained (line by line):

3: Definition of our range of values

4: Opening a session, recall from Program class of the Custom Sorting article.

5: Creating criteria for type Book .

6: Crucial part: Adding Restrictions.In (NHibernate.Criterion), passing the property that needs to be checked (Title) and the list of range values.

7-8: Calling the List() method(s) and returning the retrieved list

Important: This works also, when comparing a property of a property with a range of values. Example: Assuming, Book contains a class Author , which in turn contains a property Surname. Then we could formulate such a comparison statement like follows:

.Add(Restrictions.In(“Author.Surname”, names)).

Under the assumption that names is an array containing strings.

That’s it! I’m sure there are more problems to be solved!

See you later!

Best regards,


Read Full Post »