NHibernate的COALESCE问题 [英] NHibernate COALESCE issue
问题描述
我想表达与NHibernate下面的SQL查询
定义@date DATETIME = NULL;
选择
ER.Id
,ER.DocumentDate
起价
ExpenseReport ER
,其中
ER.PeriodFrom> = COALESCE(@date,ER.PeriodFrom)
或ER.PeriodTo< = COALESCE(@date,ER.PeriodTo);
所以,在C#部分我有以下类:
- 的实体:ExpenseReport
- 我的搜索本身就是一个单独的类
代码片段:
// -----实体类。
公共部分类ExpenseReport
{
公众可空<&System.DateTime的GT; PeriodFrom {搞定;组; }
//许多其他属性
}
// -----搜索参数类。
公共类SearchParameters
{
公众可空<&System.DateTime的GT; DateFrom {搞定;组; }
//许多其他属性
}
所以,现在分配搜索参数 IQueryOver< ExpenseReport>
变种q = SessionProvider.QueryOver< ExpenseReport>();
和我有点现在与NHibernate失去了....我现在该如何办呢?
q.And(/ ***我被困这里** /)
一个起草的代码应该是这样的:
//左侧
无功左= Projections.Property< ExpenseReport>(TI => ti.PeriodFrom);
//右侧
无功权= Projections.SqlFunction(COALESCE
,NHibernateUtil.DateTime
,Projections.Constant(search.DateFrom,NHibernateUtil.DateTime)
,Projections.Property< ExpenseReport>(TI => ti.PeriodFrom)
);
//使用GeProperty限制,以两个IProjections
变种限制= Restrictions.GeProperty(左,右);
//最后 - 我们的查询获得其WHERE
q.Where(限制);
所以,我们首先创建两个预测。然后,我们使用了限制
实用程序设置来创建> =
的(GeProperty)的。从而限制最终传递到WHERE子句...
I am trying to express the following SQL query with NHibernate
DECLARE @date DATETIME = NULL;
SELECT
ER.Id
, ER.DocumentDate
FROM
ExpenseReport ER
WHERE
ER.PeriodFrom >= COALESCE(@date, ER.PeriodFrom)
OR ER.PeriodTo <= COALESCE(@date, ER.PeriodTo);
So, in the C# part I do have the following classes:
- for the entity : ExpenseReport
- for my search itself a separate class
Code snippets:
// ----- Entity class.
public partial class ExpenseReport
{
public Nullable<System.DateTime> PeriodFrom { get; set; }
// many other properties
}
// ----- Search parameter class.
public class SearchParameters
{
public Nullable<System.DateTime> DateFrom { get; set; }
// many other properties
}
So, assigning now the search parameters to IQueryOver<ExpenseReport>
var q = SessionProvider.QueryOver<ExpenseReport>();
And I am a bit lost now with NHibernate .... How do I do it now?
q.And( /*** I AM STUCK HERE **/)
A drafted code should look like this:
// left side
var left = Projections.Property<ExpenseReport>(ti => ti.PeriodFrom);
// right side
var right = Projections.SqlFunction("COALESCE"
, NHibernateUtil.DateTime
, Projections.Constant(search.DateFrom, NHibernateUtil.DateTime)
, Projections.Property<ExpenseReport>(ti => ti.PeriodFrom)
);
// the restriction using the GeProperty, taking two IProjections
var restriction = Restrictions.GeProperty(left, right);
// finally - our query get its WHERE
q.Where(restriction);
So, we firstly create two projections. Then we used the Restrictions
utility set to create the >=
(GeProperty). Resulting restriction is finally passed into WHERE clause...
这篇关于NHibernate的COALESCE问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!