可以说我有一个类如下：
public class Person
{
public int PersonId {get; set;}
public string Firstame {get; set;}
public string Lastname {get; set;}
public datetime Birthdate {get; set;}
}
然后将TSQL作为：
select (Firstname + ' ' + Lastname) as Name,
Birthdate,
case
when (month(cast(Birthdate as date)) > month(getdate())
or (day(cast(Birthdate as date)) > day(getdate()) and month(cast(Birthdate as date)) = month(getdate())))
then datediff(year, cast(Birthdate as date), getdate())-1
else datediff(year,cast(Birthdate as date), getdate())
end as Age
from Person
go
我是NHibernate和QueryOver的新手，我一直在尝试提出一种将以下内容转换为QueryOver的方法。
case
when (month(cast(Birthdate as date)) > month(getdate())
or (day(cast(Birthdate as date)) > day(getdate()) and month(cast(Birthdate as date)) = month(getdate())))
then datediff(year, cast(Birthdate as date), getdate())-1
else datediff(year,cast(Birthdate as date), getdate())
end as Age
关于如何使用QueryOver或什至更好地作为IProjection扩展来实现此目标的任何建议？
以下是我一直在研究的一些材料，但是作为一个初学者，我很难将一些创造物放在一起。
NHibernate QueryOver CASE何时对列值进行计算
http://www.andrewwhitaker.com/blog/2014/08/15/queryover-series-part-7-using-sql-functions/
经过一番挣扎，我得以制作出与我想要存档类似的东西。也许有更好的解决方案，但是就目前而言，这可以按预期进行。
public IQueryOver<Person> GetQuery(ISession session)
{
Person person = null;
DateTime? endDate = DateTime.Today;
SomePersonView dto = null;
IProjection birthDate = Projections.Conditional(
Restrictions.IsNull(Projections.Property(() => person.BirthDate)),
Projections.Constant(endDate, NHibernateUtil.DateTime),
Projections.Property(() => person.BirthDate));
var personQuery = session.QueryOver<Person>(() => person)
.Select(
Projections.Distinct(
Projections.ProjectionList()
.Add(Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property(() => person.FirstName),
Projections.Constant(" "),
Projections.Property(() => person.LastName)).WithAlias(() => dto.Name))
.Add(Projections.Property(() => person.BirthDate).WithAlias(() => dto.BirthDate))
.Add(DateProjections.Age("yy", birthDate, endDate).WithAlias(() => dto.Age))))
.TransformUsing(Transformers.AliasToBean<SomePersonView>());
return personQuery;
}
这是'DateProjections.Age'来自的扩展名。
public static class DateProjections
{
private const string DateDiffFormat = "datediff({0}, ?1, ?2)";
// Maps datepart to an ISQLFunction
private static Dictionary<string, ISQLFunction> DateDiffFunctionCache = new Dictionary<string, ISQLFunction>();
public static IProjection DateDiff(string datepart, IProjection startDate, DateTime? endDate)
{
ISQLFunction sqlFunction = GetDateDiffFunction(datepart);
return Projections.SqlFunction(
sqlFunction,
NHibernateUtil.Int32,
startDate,
Projections.Constant(endDate));
}
//Get exact age of a person as of today
public static IProjection Age(string datepart, IProjection startDate, DateTime? endDate)
{
IProjection myAge = DateDiff("yy",
startDate, endDate);
IProjection ageMinusOne = Projections.SqlFunction(
new VarArgsSQLFunction("(", "-", ")"), NHibernateUtil.Int32, myAge,
Projections.Constant(1));
IProjection datePartMonthBirthdate = Projections.SqlFunction("month", NHibernateUtil.Int32,
startDate);
IProjection datePartDayBirthdate = Projections.SqlFunction("day", NHibernateUtil.Int32,
startDate);
IProjection datePartMonthCurrentDate = Projections.SqlFunction("month", NHibernateUtil.Int32,
Projections.Constant(endDate));
IProjection datePartDayCurrentDate = Projections.SqlFunction("day", NHibernateUtil.Int32,
Projections.Constant(endDate));
IProjection myRealAge = Projections.Conditional(
Restrictions.Or(
Restrictions.GtProperty(datePartMonthBirthdate, datePartMonthCurrentDate),
Restrictions.GtProperty(datePartDayBirthdate, datePartDayCurrentDate)
&& Restrictions.EqProperty(datePartMonthBirthdate, datePartMonthCurrentDate)),
ageMinusOne,
myAge);
return myRealAge;
}
private static ISQLFunction GetDateDiffFunction(string datepart)
{
ISQLFunction sqlFunction;
if (!DateDiffFunctionCache.TryGetValue(datepart, out sqlFunction))
{
string functionTemplate = string.Format(DateDiffFormat, datepart);
sqlFunction = new SQLFunctionTemplate(NHibernateUtil.Int32, functionTemplate);
DateDiffFunctionCache[datepart] = sqlFunction;
}
return sqlFunction;
}
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句