The official Fatica Labs Blog! RSS 2.0
# Tuesday, September 27, 2011

NHibernate QueryOver is a new API ( from version 3.0 )  that allow to create queries the ICriteria way, but with lambda expressions instead of raw strings. With QueryOver we have the benefit of intellisense and the code is refactoring friendly. At the moment one reference can be found on this blog post. Something not so obvious to do is a query over a portion of a date property, for instance all the entities in a certain month, and so on. Let’s have an example considering this simple entity:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"  assembly="MyAssembly" namespace="MyAssembly.MyNamespace">
  <class name="Item" table="Items">
    
    <id name="Id"  type="Int64">
      <generator class="native"/>
    </id>
        
    <property type="AnsiString" name="Code" length="32"></property>
    <property type="DateTime" name="StartDate"></property>
    
  </class>
</hibernate-mapping>

And suppose we want to fetch all the entities having start date in the year 2011, with QueryOver we write:

var result = session.QueryOver<Item>()
                    .Where(
                    Restrictions.Eq(
                    Projections.SqlFunction("year"
                    , NHibernateUtil.Int32
                    , Projections.Property<Item>(item => item.StartDate)
                    ) 
                    ,2011)).List();

 

that yield the following query ( with MSSQL 2005 dialect ):

SELECT
        this_.Id as Id21_0_,
        this_.Code as Code21_0_,
        this_.StartDate as StartDate21_0_
    FROM
        Items this_
    WHERE
        datepart(year, this_.StartDate) = @p0;
    @p0 = 2011 [Type: Int32 (0)]

 

obviously we can select a projection with a portion of the date to, suppose we want to fetch the day of all item in a certain date range, we can write:

var session = NHHelper.Instance.CurrentSession;
               var result = session.QueryOver<Item>()
              .WhereRestrictionOn(k => k.StartDate).IsBetween(dlow)
              .And(dup)
              .Select(
                       Projections.SqlFunction("day"
                                   , NHibernateUtil.Int32
                                   , Projections.Property<Item>(p => p.StartDate)
                                   ))
              .List<int>();

 

obtaining the following query:

SELECT
        datepart(day,
        this_.StartDate) as y0_
    FROM
        Items this_
    WHERE
        this_.StartDate between @p0 and @p1;
    @p0 = 01/09/2011 00:00:00 [Type: DateTime (0)], @p1 = 29/09/2011 00:00:00 [Type: DateTime (0)]

 

These are really common situation when we have to fill-up gui elements, and with these tricks we can write optimal and fast queries.

Addition:

As pointed by Vahid ( Thanks a lot ! ) in the comment, with the latest version ( NH 3.2 ) things are easier since there is a shortcut for dateparts as projection extension, so the first sample became:

session.QueryOver<Item>()
                 .Where(
                    p=>p.StartDate.YearPart()==2011
                   
                    ).List();

 

and the second one:

 

session.QueryOver<Item>()
                .Where(
                   p=>p.StartDate.IsBetween(dlow).And(dup)
                   )
                   .Select(k=>k.StartDate.DayPart() )
                   .List();
surely better, and this is the way to go with newer NH versions, and producing of course the same DB queries.
Tuesday, September 27, 2011 11:48:57 AM (GMT Daylight Time, UTC+01:00)  #    Comments [2] - Trackback
NHibernate | QueryOver

My Stack Overflow
Contacts

Send mail to the author(s) E-mail

Tags
profile for Felice Pollano at Stack Overflow, Q&A for professional and enthusiast programmers
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2014
Felice Pollano
Sign In
Statistics
Total Posts: 157
This Year: 0
This Month: 0
This Week: 0
Comments: 123
This blog visits
All Content © 2014, Felice Pollano
DasBlog theme 'Business' created by Christoph De Baene (delarou) and modified by Felice Pollano