Clever Geek Handbook

NHibernate COALESCE problem

I am trying to express the following SQL query using 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 have the following classes:

  • for object: ExpenseReport
  • for my search in itself a separate class

Snippets of code:

// ----- 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, now assigning search parameters IQueryOver<ExpenseReport> 

var q = SessionProvider.QueryOver<ExpenseReport>();

And I am a bit lost now with NHibernate .... How do I do this now?

q.And( /*** I AM STUCK HERE **/)
+3


source to share


1 answer


The finished 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, first let's create two projections. Then we used the utility Restrictions

to create >=

(GeProperty). The resulting constraint is finally passed to the WHERE ...

+4


source






More articles:


All Articles