nHibernate complex Query

Question

I have a problem querying using nHibernate.
I have the following entities:

public class CostAmount  
{  
    public virtual int Id {get;set;}  
    public virtual Year Year{get; set;}  
    public virtual Month Month{get; set;}  
}  

public class Year  
{  
    public virtual int Id {get;set;}  
    public virtual string Code {get;set;}  
}  

public class Month  
{  
    public virtual int Id {get;set;}  
    public virtual string Code {get;set;}  
}

I want to query using some sql like the following:

select * from CostAmount ca  
inner join Year y on ca.YearID = y.ID  
inner join Month m on ca.MonthID = m.ID  
where y.Code *100+m.Code between 9107 and 9207

Can any one help me please.

Sadly for you, the QueryOver syntax doesn't have support for math operations. You'll have to do horrible things like http://stackoverflow.com/questions/4828552/are-there-any-arithmetic-operation-projections-in-nhibernate — xanatos, Aug 29 '13 at 10:17
And I hope your Code(s) are `int`, otherwise I don't know how you could multiply it. — xanatos, Aug 29 '13 at 10:28

score 2 · Answer 1 · edited May 23 '17 at 11:58

As I've written, NHibernate QueryOver has poor syntax for math operations... Now, if we consider Code to be an int (because I normally don't multiply strings by 100):

// Aliases
CostAmount costAmount = null;
Year year = null;
Month month = null;

// Projections for the math operations

// y.Code * 100
var proj1 = Projections.SqlFunction(
    new VarArgsSQLFunction("(", "*", ")"),
    NHibernateUtil.Int32,
    Projections.Property(() => year.Code),
    Projections.Constant(100)
);

// proj1 + m.Code 
var proj2 = Projections.SqlFunction(
    new VarArgsSQLFunction("(", "+", ")"),
    NHibernateUtil.Int32,
    proj1,
    Projections.Property(() => month.Code)
);

// The query

var query = Session.QueryOver(() => costAmount)
                   .JoinAlias(() => costAmount.Year, () => year)
                   .JoinAlias(() => costAmount.Month, () => month)
                   .Where(Restrictions.Between(proj2, 9107, 9207));

var res = query.List();

The trick for math operations was taken from https://stackoverflow.com/a/10756598/613130

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

Maybe you can check this question Fluent Nhibernate inner join

Or maybe from this explanation can guide you to find the right answer. http://ayende.com/blog/4023/nhibernate-queries-examples

edited May 23 '17 at 12:22

Community

1
1

answered Aug 29 '13 at 09:57

Moch Lutfi

552
10
27

nHibernate complex Query

2 Answers2