2

For a column of type varchar I could write such search query:

public IList<Order> GetByName(string orderName)
{
 using (ISession session = NHibernateHelper.OpenSession())
 {
  return session.CreateCriteria<Order>().
   Add(Restrictions.Like("Name", string.Format("%{0}%", orderName))).
   List<Order>();
 }
}

How do I implement the similar search query for a column that has a DateTime type?

public IList<Order> GetByDateTime(string str)
{
    using (ISession session = NHibernateHelper.OpenSession())
    {
        return session.CreateCriteria<Order>()
            .Add(Restrictions.Like(Projections.Cast(NHibernateUtil.String, Projections.Property("Created")),
                                    '%' + str + '%'))
            .List<Order>();
    }
}

That is, if the method is passed the date and part-time (eg "25.03.2010 19"), then displays all orders are carried out in this period of time:
25.03.2010 19:22:00
25.03.2010 19:44:00
25.03.2010 19:59:00

akrisanov
  • 3,212
  • 6
  • 33
  • 56
  • NHibernate cannot do anything that cannot be done in sql, so you need to create your own date parsing function in C# like Aaronaught suggests – Paco Apr 14 '10 at 19:46

2 Answers2

3

Just check if the date is within your desired range:

DateTime beginDate = new DateTime(2010, 3, 25, 19, 0, 0);
DateTime endDate = new DateTime(2010, 3, 25, 20, 0, 0);
return session.CreateCriteria<Order>()
   .Add(Expression.Between("OrderDate", beginDate, endDate))
   .List<Order>();
Aaronaught
  • 120,909
  • 25
  • 266
  • 342
  • Your response is understandable and logical. But ...The user specifies only the date string like "14.04.2010", "2010", "14.04", "04.2010 22:06", etc. – akrisanov Apr 14 '10 at 18:07
  • @Anry: If `2010` and `14.04` are both valid inputs, how are you disambiguating? `14.04` might mean April 14th, or might mean April 2014. Or it might mean 2:04 PM. What if the input is just `10`, should that be interpreted as 2010 or October? The easiest solution would be to ask the user for a date range instead of an arbitrary text input. The more user-friendly, but exponentially more difficult solution is to parse the fuzzy date into a usable range using date arithmetic and possibly a few regular expressions. – Aaronaught Apr 14 '10 at 19:02
0

I had a similar problem using LINQ and fixed it with the help of the answer posted by vikram nayak on the question asked here: Nhibernate LINQ DateTime.AddDay does not work.

This is how I did it:
I substituted the following classes in vikram's example:

public class ExtendedLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
    public ExtendedLinqtoHqlGeneratorsRegistry()
    {
        this.Merge(new DateTimeToShortDateStringGenerator());
        //I had to use the lines below instead.
        //MethodInfo method = ReflectionHelper.GetMethodDefinition<DateTime>(x => x.ToShortDateString());
        //RegisterGenerator(method, new DateTimeToShortDateStringGenerator());
    }
}

public class DateTimeToShortDateStringGenerator : BaseHqlGeneratorForMethod
{
    public DateTimeToShortDateStringGenerator()
    {
        SupportedMethods = new[]
        {
            ReflectionHelper.GetMethodDefinition<DateTime>(x => x.ToShortDateString())
        };
    }

    public override HqlTreeNode BuildHql(MethodInfo method, System.Linq.Expressions.Expression targetObject, ReadOnlyCollection<System.Linq.Expressions.Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
        return treeBuilder.MethodCall("DateTimeToShortDateString", visitor.Visit(targetObject).AsExpression());
    }
}

public class CustomDialect : MsSql2008Dialect
{
    public CustomDialect()
    {
        RegisterFunction(
            "DateTimeToShortDateString",
            new SQLFunctionTemplate(
            NHibernateUtil.DateTime,
            "CONVERT(NVARCHAR(10), ?1, 105)"
            )
        );
    }
}

You can then use the following syntax:

using (session.BeginTransaction())
{
    var patients = session.Query().Where(p => p.BirthDate.ToShortDateString().Contains("1975"));
}
Community
  • 1
  • 1
Patrick Koorevaar
  • 1,219
  • 15
  • 24