Retrieving records by date using only partial days comparison using nhibernate

Question

I would like to receive all records from a certain day, no matter what time is associated with these records. So far I have a 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); } 

It is obviously more readable and looks cleaner. I was wondering if there is a better way to write the first method using database storage and nhibernate?

Answer 1

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

In earlier versions, 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 the criteria, via SqlFunction. This is more confusing, but allows you to create more dynamic queries:

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

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 ); } 

Answer 3

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

 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 like it is now, I was just trying to give you an idea of ​​what to do.

Then you can use it like this:

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