CODEWITHSUNDEEP
nhibernatehql
NoviceAndNovice
NoviceAndNovice

Reputation: 725

NHibernate query ( hql vs criteria )

I have an hql query string 

from  MyTable table
where table.StartTime + table.Duration >= : startTime
and   table.MyId = : id

How can I write this without HQL in NHibernate (using criteria)?

Upvotes: 2

Views: 977

Answers (1)

Florian Lim
Florian Lim

Reputation: 5362

This might be of interest regarding the DateTime + TimeSpan issue.

This will work in your case:

QueryOver:

int id = 1;
DateTime startTime = DateTime.Now.AddDays(5.0);

var vacations = session.QueryOver<Vacation>()
    .Where(v => v.Employee.Id == id)
    .And(v => v.StartDate > startTime 
     || (v.StartDate == startTime.Date && v.Duration >= startTime.TimeOfDay))
    .List();

ICriteria:

var vacationsCrit = session.CreateCriteria(typeof(Vacation))
    .Add(Expression.Eq("Employee.Id", id))
    .Add(Expression.Disjunction()
        .Add(Expression.Gt("StartDate", startTime))
        .Add(Expression.Conjunction()
            .Add(Expression.Eq("StartDate", startTime.Date))
            .Add(Expression.Ge("Duration", startTime.TimeOfDay))))
    .List();

Both will output the exact same SQL. It should be mentioned that you cannot do something like this, as described in the link above:

var vacations = session.QueryOver<Vacation>()
    .Where(v => v.Employee.Id == id)
    .And(v => v.StartDate.Add(v.Duration) >= startTime) // <-- this will NOT work
    .List();

Upvotes: 2

Related Questions