NHibernate QueryOver将一个属性合并到另一个属性
本文关键字:属性 一个 合并 另一个 QueryOver NHibernate | 更新日期: 2023-09-27 17:58:03
考虑一下这个愚蠢的域:
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; }
}
}
其想法是,每个工作项可以分配给具有不同详细信息的多个员工,这可能会覆盖工作项本身的开始/结束日期。如果这些重写为null,则应改为从工作项中获取它们。
现在,我想执行一个对有效日期有限制的查询。我第一次尝试过:
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
不是检查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", NHibernateUtil.DateTime,
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, 01)
};
Task task1 = new Task()
{
Assignee = empl,
WorkItem = wi,
Details = "Do this",
};
db.Save(task1);
Task task2 = new Task()
{
Assignee = empl,
WorkItem = wi,
Details = "Do that",
StartDateOverride = new DateTime(2016, 7, 1),
EndDateOverride = new DateTime(2017, 1, 1),
};
db.Save(task2);
Task taskAlias = null;
WorkItem wiAlias = null;
DateTime start = new DateTime(2016, 1, 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", NHibernateUtil.DateTime,
Projections.Property(() => taskAlias.StartDateOverride),
Projections.Property(() => wiAlias.StartDate)),
Projections.Constant(end)))
.And(Restrictions.GeProperty(
Projections.SqlFunction("COALESCE", NHibernateUtil.DateTime,
Projections.Property(() => taskAlias.EndDateOverride),
Projections.Property(() => wiAlias.EndDate)),
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
的安全替代品。