namespace TryHibernate.Example
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
 
    public class WorkItem
    {
        public int Id { get; set; }
        public string Description { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
    }
 
    public class Task
    {
        public int Id { get; set; }
        public Employee Assignee { get; set; }
        public WorkItem WorkItem { get; set; }
        public string Details { get; set; }
        public DateTime? StartDateOverride { get; set; }
        public DateTime? EndDateOverride { get; set; }
    }
}

这个想法是每个工作项可能被分配给具有不同细节的多个员工,可能会覆盖工作项本身的开始/结束日期.如果这些覆盖为空,则应该从工作项中取出它们.

现在我想执行一个有效日期限制的查询.我先试过这个：

IList<Task> tasks = db.QueryOver<Task>(() => taskAlias)
    .JoinAlias(() => taskAlias.WorkItem,() => wiAlias)
    .Where(() => taskAlias.StartDateOverride.Coalesce(() => wiAlias.StartDate) <= end)
    .And(() => taskAlias.EndDateOverride.Coalesce(() => wiAlias.EndDate) >= start)
    .List();

不幸的是,它没有编译,因为Coalesce期望一个常量,而不是属性表达式.

好的,我试过这个：

.Where(() => (taskAlias.StartDateOverride == null
                  ? wiAlias.StartDate
                  : taskAlias.StartDateOverride) <= end)
    .And(() => (taskAlias.EndDateOverride == null
                  ? wiAlias.EndDate
                  : taskAlias.EndDateOverride) >= start)

这会抛出NullReferenceException.不知道为什么,但可能是因为NHibernate没有正确翻译那个三元运算符(并尝试实际调用它),或者因为== null不是检查空值的正确方法.无论如何,我甚至没想到它会起作用.

最后,这个工作：

IList<Task> tasks = db.QueryOver<Task>(() => taskAlias)
    .JoinAlias(() => taskAlias.WorkItem,() => wiAlias)
    .Where(Restrictions.LeProperty(
        Projections.sqlFunction("COALESCE",NHibernateUtil.DateTime,Projections.Property(() => taskAlias.StartDateOverride),Projections.Property(() => wiAlias.StartDate)),Projections.Constant(end)))
    .And(Restrictions.GeProperty(
        Projections.sqlFunction("COALESCE",Projections.Property(() => taskAlias.EndDateOverride),Projections.Property(() => wiAlias.EndDate)),Projections.Constant(start)))
    .List();

但我无法称之为干净的代码.也许我可以将某些表达式提取到单独的方法中来清理它,但是使用表达式语法而不是这些丑陋的预测会好得多.有办法吗？ NHibernate背后是否有任何理由不支持Coalesce扩展中的属性表达式？

一个明显的选择是选择所有内容,然后使用Linq或其他任何方式过滤结果.但它可能会成为一个总行数很多的性能问题.

这是完整的代码,以防有人想要尝试：

using (ISessionFactory sessionFactory = Fluently.Configure()
    .Database(sqliteConfiguration.Standard.UsingFile("temp.sqlite").Showsql())
    .Mappings(m => m.AutoMappings.Add(
        AutoMap.AssemblyOf<Employee>(new ExampleConfig())
            .Conventions.Add(DefaultLazy.Never())
            .Conventions.Add(DefaultCascade.All())))
    .ExposeConfiguration(c => new SchemaExport(c).Create(true,true))
    .BuildSessionFactory())
{
    using (ISession db = sessionFactory.OpenSession())
    {
        Employee empl = new Employee() { Name = "Joe" };
        WorkItem wi = new WorkItem()
        {
            Description = "Important work",StartDate = new DateTime(2016,01,01),EndDate = new DateTime(2017,01)
        };
        Task task1 = new Task()
        {
            Assignee = empl,WorkItem = wi,Details = "Do this",};
        db.Save(task1);
        Task task2 = new Task()
        {
            Assignee = empl,Details = "Do that",StartDateOverride = new DateTime(2016,7,1),EndDateOverride = new DateTime(2017,1,};
        db.Save(task2);
        Task taskAlias = null;
        WorkItem wiAlias = null;
        DateTime start = new DateTime(2016,1);
        DateTime end = new DateTime(2016,6,30);
        IList<Task> tasks = db.QueryOver<Task>(() => taskAlias)
            .JoinAlias(() => taskAlias.WorkItem,() => wiAlias)
            // This doesn't compile:
            //.Where(() => taskAlias.StartDateOverride.Coalesce(() => wiAlias.StartDate) <= end)
            //.And(() => taskAlias.EndDateOverride.Coalesce(() => wiAlias.EndDate) >= start)
            // This throws NullReferenceException:
            //.Where(() => (taskAlias.StartDateOverride == null ? wiAlias.StartDate : taskAlias.StartDateOverride) <= end)
            //.And(() => (taskAlias.EndDateOverride == null ? wiAlias.EndDate : taskAlias.EndDateOverride) >= start)
            // This works:
            .Where(Restrictions.LeProperty(
                Projections.sqlFunction("COALESCE",Projections.Constant(end)))
            .And(Restrictions.GeProperty(
                Projections.sqlFunction("COALESCE",Projections.Constant(start)))
            .List();
        foreach (Task t in tasks)
            Console.WriteLine("Found task: {0}",t.Details);
    }
}

配置非常简单：

class ExampleConfig : DefaultAutomappingConfiguration
{
    public override bool ShouldMap(Type type)
    {
        return type.Namespace == "TryHibernate.Example";
    }
}