nHibernate复杂查询

Question

我在使用nHibernate进行查询时遇到问题
我有以下实体：

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;}  
}  

我想使用一些sql进行查询，如下所示：

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  

有谁能帮我吗。

Answer 1

正如我所写的，NHibernate QueryOver的数学运算语法很差。。。现在，如果我们认为Code是int（因为我通常不会将字符串乘以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();

数学运算的技巧取自https://stackoverflow.com/a/10756598/613130

Answer 2

也许你可以检查这个问题Fluent Nhibernate内部加入

或者从这个解释中可以引导你找到正确的答案。http://ayende.com/blog/4023/nhibernate-queries-examples