Fetching records by date with only day part comparison using nhibernate

Question

I would like to fetch all records from particular day, no matter what time is associated with those records. So far I have method like this:

public IQueryable<Record> QueryByDay(DateTime day)
{
    DateTime from = day.Date;
    DateTime to = day.Date.AddDays(1);

    return repository.Table
        .Where(t => t.MyDate >= from && t.MyDate < to);
    }

But in linq-to-object we can do (assuming Table is now some collection):

public IEnumerable<Record> QueryByDay(DateTime day)
{
    return repository.Table
        .Where(t => t.MyDate.Date == day.Date);
}

Which is obviously more readable and feels more clean. I was wondering if there is better way to write the first method using database storage and nhibernate?

Kirven

I believe the form you used for L2O works just fine with NH. – Bonedry

@Diego Mijelshon Can you tell me which verison of NHibernate you are using that it works? At project I am working at we are using v. 2.1 and this is throwing NHibernate.QueryException. – Kirven

2.1 is 3 years old and unsupported. 3.3 is the current stable release. – Bonedry

It's huge project and I don't have the authority to change NHibernate version. I know that it's not the latest one, but the question is: If someone can confirm that the L2O approach is working with NHibernate 3.3? – Kirven

Yes, I can confirm it works in 3.3 – Bonedry

Answer 1

As said in the comments, your LINQ query works fine with NH 3.3.

In earlier releases, you can use HQL:

return session.CreateQuery("from MyClass where date(MyDate) = :day")
              .SetParameter("day", day.Date)
              .List<MyClass>(); //executes

You can also use the date function from Criteria, via SqlFunction. This is more convoluted, but allows building more dynamic queries:

return session.CreateCriteria<Foo>()
              .Add(Restrictions.Eq(
                       Projections.SqlFunction("date",
                                               NHibernateUtil.Date,
                                               Projections.Property("MyDate")),
                       day.Date))
                .List<MyClass>(); //executes

Bonedry

Answer 2

public IEnumerable<Record> QueryByDay(DateTime day)
{
    return repository.Table
        .Where(t => t.MyDate.Day == day.Day && t.MyDate.Month == day.Month && t.MyDate.Year == day.Year );
}

Pampas

Can you tell me which verison of NHibernate you are using that it works? At project I am working at we are using v. 2.1 and this is throwing NHibernate.QueryException. – Kirven

Oh! v2.1 doesn't support Linq but there's a branch with the Linq provider, a one the didn't work out. Version 3+ includes a native Linq provider. As @Diego Mihelshon pointed out, "2.1 is 3 years old and unsupported. 3.3 is the current stable release." – Pampas

Ok, i give this +1 though if SomeDateAttr.Date == OtherDate.Date works in NH 3+ then it's better approach in my opinion. – Kirven

Of course, I thought it didn't work because of your question :) – Pampas

Answer 3

It depends on the LINQ provider. I'm not sure if NHibernate LINQ provider supports the item.SomeDateProperty.Date == x syntax and I'm doubtful it does. But, you can make your own extension method like so:

public static IQueryable<T> FilterByDate(this IQueryable<T> This, Expression<Func<T, DateTime>> getProperty, DateTime date)
{
  DateTime from = day.Date;
  DateTime to = day.Date.AddDays(1);
  return This.Where(x=> 
    Expression.And(
      Expression.GreaterThan(getProperty, Expression.Variable(from)),
      Expression.LessThan(getProperty, Expression.Variable(to)));

}

This is NOT going to build the way it is now - I was just trying to give you an idea of what to do.

You can then use it like so:

var result = repository.Table.FilterByDate(x=>x.MyDate, new DateTime(2012, 6,6));

Malpighiaceous

+1, I like the idea, though looks like more up to date NHibernate supports easiest solutions. – Kirven