LIKE query for DateTime in NHibernate
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开发者_C百科 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:00Just 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>();
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"));
}
精彩评论