Math operations in nHibernate Criteria Query

Question

Dear All,

I am having troubles with a nHibernate query.

I have a db which stores vehicle info, and the user is able to search the db by make, model, type and production dates.

Make, model & type search is fine, works a treat, it is the productions dates I am having issues with. So here goes...

The dates are stored as ints (StartMonth, StartYear, FinishMonth, FinishYear), when the end-user selects a date it is passed to the query as an int eg 2010006 (2010 * 100 + 6).

below is part of the query I am using, FYI I am using Lambda Extensions.

if (_searchCriteria.ProductionStart > 0)
{
    query.Add<Engine>(e => ((e.StartYear * 100) + e.StartMonth) >= _searchCriteria.ProductionStart);
}

if (_searchCriteria.ProductionEnd > 0)
{
    query.Add<Engine>(e => ((e.FinishYear * 100) + e.FinishMonth) <= _searchCriteria.ProductionEnd);
}

But when the query runs I get the following message,

Could not determine member from ((e.StartYear * 100) + e.StartMonth)

Any help would be great,

Regards

Rich

Answer 1

I don't know these linq extensions very well, but I assume that you need to pass the member name before the operation.

int year = Math.Floor(_searchCriteria.ProductionStart / 100);
int month = _searchCriteria.ProductionStart % 100;

query.Add<Engine>(
  (e => e.StartYear >= year);
  || (e => e.StartMonth >= month && e => e.StartYear >= year))

Either the year must be greater or the year is at least equal and the month is greater. The same applies to ProductionEnd with lower than.

I'm not sure if the lambda extensions support "or" (||) operator like this, but there is surely a possibility for "or".

---

From Richards comment:

query.Add(
  Restrictions.Or( 
    Restrictions.And(
      SqlExpression.CriterionFor<Engine>(e => e.StartMonth >= month),   
      SqlExpression.CriterionFor<Engine>(e => e.StartYear >= year)), 
    SqlExpression.CriterionFor<Engine>(e => e.StartYear >= year) ) );

Answer 2

@Stefan, I have tried the following,

if (_searchCriteria.ProductionStart > 0)
{
    int sYear = (int) Math.Floor( (double) _searchCriteria.ProductionStart / 100);
    int sMonth = _searchCriteria.ProductionStart % 100;

    query.Add(
        Restrictions.Or(
            Restrictions.And(
                SqlExpression.CriterionFor<Engine>(e => e.StartMonth >= sMonth),
                SqlExpression.CriterionFor<Engine>(e => e.StartYear >= sYear)
            ),
            SqlExpression.CriterionFor<Engine>(e => e.StartYear >= sYear)
        )
    );
}

if (_searchCriteria.ProductionEnd > 0)
{
    int eYear = (int)Math.Floor((double)_searchCriteria.ProductionEnd / 100);
    int eMonth = _searchCriteria.ProductionEnd % 100;

    query.Add(
        Restrictions.Or(
            Restrictions.And(
                SqlExpression.CriterionFor<Engine>(e => e.FinishMonth <= eMonth),
                SqlExpression.CriterionFor<Engine>(e => e.FinishYear <= eYear)
            ),
            SqlExpression.CriterionFor<Engine>(e => e.FinishYear <= eYear)
        )
    );
}

I check the results with the different AND / OR combinations (i.e. ((a && b ) || a) and (a || (a && b)) ) and a couple of records pop through.

After some further thought I have the following,

if (_searchCriteria.ProductionStart > 0)
{
    query.Add<Engine>(e => e.StartDate >= _searchCriteria.ProductionStart);
}

if (_searchCriteria.ProductionEnd > 0)
{
    query.Add<Engine>(e => e.FinishDate <= _searchCriteria.ProductionEnd);
}

Where StartDate and FinishDate are computed columns in my table and properties on my entity.

I may edit this when our guru returns next week.

Thanks and Regards Rich