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 stackoverflow.com/questions/4828552/… — xanatos
– xanatos, Commented Aug 29, 2013 at 10:17
And I hope your Code(s) are int, otherwise I don't know how you could multiply it. — xanatos
– xanatos, Commented Aug 29, 2013 at 10:28

Community · Accepted Answer · 2017-05-23 11:58:05Z

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

Community · Accepted Answer · 2017-05-23 12:22:07Z

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, 2017 at 12:22

CommunityBot

11 silver badge

answered Aug 29, 2013 at 9:57

Moch Lutfi

55210 silver badges27 bronze badges

Collectives™ on Stack Overflow

nHibernate complex Query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related