如何计算年龄给定的出生日期使用NHibernate QueryOver

本文关键字：出生日期 QueryOver NHibernate 何计算 计算 | 更新日期: 2023-09-27 18:08:50

假设我有一个如下的类:

public class Person
{
    public int PersonId {get; set;}
    public string Firstame {get; set;}
    public string Lastname {get; set;}
    public datetime Birthdate {get; set;}
}

Then TSQL as:

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/

如何计算年龄给定的出生日期使用NHibernate QueryOver

经过一番努力，我终于能够制作出类似于我想要存档的东西。也许会有更好的解决方案，但就目前而言，这是预期的。

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。

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;
    }
}
相关文章：