C# · 12月 23, 2021

c# – NHibernate QueryOver将属性合并到另一个属性

考虑这个愚蠢的域名: 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”; }}解决方法 让我们从这开始: // This doesn’t compile://.Where(() => taskAlias.StartDateOverride.Coalesce(() => wiAlias.StartDate) <= end)//.And(() => taskAlias.EndDateOverride.Coalesce(() => wiAlias.EndDate) >= start)

并将其修改为:

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

现在它将编译.但在运行时它会生成相同的NullReferenceException.不好.

事实证明,NHibernate确实试图评估Coalesce论证.通过查看ProjectionExtensions类实现可以很容易地看到这一点.以下方法处理Coalesce转换:

internal static IProjection ProcessCoalesce(MethodCallExpression methodCallExpression){ IProjection projection = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection(); object obj = ExpressionProcessor.FindValue(methodCallExpression.Arguments[1]); return Projections.sqlFunction(“coalesce”,(IType) NHibernateUtil.Object,projection,Projections.Constant(obj));}

注意第一个参数(FindMemberExpresion)与第二个参数(FindValue)的不同处理.好吧,FindValue只是试图评估表达式.

现在我们知道造成这个问题的原因了.我不知道为什么会这样实现,所以将集中精力寻找解决方案.

幸运的是,ExpressionProcessor类是公共的,并且还允许您通过RegisterCustomMethodCall / RegisterCustomProjection方法注册自定义方法.这导致我们解决方案:

>创建类似于Coalesce的自定义扩展方法(例如,将它们称为IfNull)
>注册自定义处理器
>使用它们而不是Coalesce

这是实施:

public static class CustomProjections{ static CustomProjections() { ExpressionProcessor.RegisterCustomProjection(() => IfNull(null,””),ProcessIfNull); ExpressionProcessor.RegisterCustomProjection(() => IfNull(null,0),ProcessIfNull); } public static void Register() { } public static T IfNull<T>(this T objectProperty,T replaceValueIfIsNull) { throw new Exception(“Not to be used directly – use inside QueryOver expression”); } public static T? IfNull<T>(this T? objectProperty,T replaceValueIfIsNull) where T : struct { throw new Exception(“Not to be used directly – use inside QueryOver expression”); } private static IProjection ProcessIfNull(MethodCallExpression mce) { var arg0 = ExpressionProcessor.FindMemberProjection(mce.Arguments[0]).AsProjection(); var arg1 = ExpressionProcessor.FindMemberProjection(mce.Arguments[1]).AsProjection(); return Projections.sqlFunction(“coalesce”,NHibernateUtil.Object,arg0,arg1); }}

由于从不调用这些方法,因此需要通过调用Register方法来确保注册自定义处理器.这是一个空方法,只是为了确保调用类的静态构造函数,实际的注册发生在那里.

所以在你的例子中,包括在开头:

CustomProjections.Register();

然后在查询中使用:

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

它将按预期工作.

附:上面的实现适用于常量和表达式参数,因此它实际上是Coalesce的安全替代品.