Nhibernate ICriteria and Using Lambda Expressions in queries

Question

hi i am new in NHibernate and i am a little confused.

Suppose we have a product table. Let the product table have 2 columns price1 and price2.

then i may query mapped product entities via HQL as follows:

string queryString = @"from product p
where p.price1 = p.price2 + 100 ";
IList result = session.CreateQuery(queryString).List();

How can i achieve that via ICriteria API.

i know it's absurd but i am trying sth like that:

session.CreateCriteria(typeof(product))
   .Add(Expression.Eq("price1", "price2" + 100))
   .List()

or more appropriately sth like that (using lambda extensions):

session.CreateCriteria(typeof(product))
   .Add<product>(p => p.price1 == (p.price2 + 100))
   .List()

in fact i downloaded lambda extensions project from googlecode and i extended it to recusively process Binary and Unary expressions to implement expresssions like:

session.CreateCriteria(typeof(product))
   .Add<product>(p => 
               (!(p.price1 > 29007) && (p.price1 > 19009)) 
            || (p.price2 == 29009));

i am currently processing queries like above but the arithmethic clauses are annoying me since i can't return the appropriate Restriction to obtain the Criterion i need.

mpffh i m tired of trying to explain it in a comprehensive way. i hope it worked. Any help is appreciated.

Answer 1

You could try this:

ISQLFunction sqlAdd = new VarArgsSQLFunction("(", "+", ")");
var products = session
    .CreateCriteria<product>()
    .Add(
        Expression.EqProperty(
            "price1", 
            Projections.SqlFunction(
                sqlAdd, 
                // TODO: replace this with appropriate type
                NHibernateUtil.Double,
                Projections.Property("price2"),
                Projections.Constant(100) 
            )
        )
    )
    .List<product>();