CreateSqlQuesry

public IList<ResourceAssign> GetAssignedResourceByProjectAndFiscalYearAndDepartment(long projectId, long fiscalYearId, long depId)
{
StringBuilder sb=new StringBuilder();

//sb.Append("SELECT RESOURCE_ASSIGN.* FROM RESOURCE_ASSIGN INNER JOIN ");
//sb.Append(" EFFORT_ESTIMATION ON RESOURCE_ASSIGN.FK_EFFORT_ESTIMATION_ID = EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID");
//sb.Append(" INNER JOIN BUDGET ON EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID");
//sb.Append(" INNER JOIN PROJECT ON BUDGET.FK_PROJECT_ID = PROJECT.PROJECT_ID");


sb.Append("SELECT RESOURCE_ASSIGN.* FROM RESOURCE_ASSIGN, EFFORT_ESTIMATION, BUDGET ,PROJECT,MEMBERRESOURCE,TEAM ");
sb.Append(" WHERE RESOURCE_ASSIGN.FK_EFFORT_ESTIMATION_ID = EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID");
sb.Append(" AND EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID");
sb.Append(" AND BUDGET.FK_PROJECT_ID = PROJECT.PROJECT_ID");
sb.Append(" AND RESOURCE_ASSIGN.FK_MEMBERRESOURCE_ID = MEMBERRESOURCE.MEMBERRESOURCE_ID ");
sb.Append(" AND MEMBERRESOURCE.FK_TEAM_ID = TEAM.TEAM_ID");
//sb.Append(" AND TEAM.FK_DIVISION_ID = DIVISION.DIVISION_ID");

sb.Append(" AND (PROJECT.PROJECT_ID = " + projectId + ") AND (EFFORT_ESTIMATION.FK_FISCALYEAR_ID =" + fiscalYearId + " ) AND (BUDGET.IS_IN_PROCESS = 1)");
if (depId != 0)
{
sb.Append(" AND (TEAM.FK_DIVISION_ID=" + depId + ")");
}

sb.Append(" order by EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID,MEMBERRESOURCE.MEMBERRESOURCE_ID");

IList<ResourceAssign> ra = NHibernateSessionManager.Instance.GetSession().CreateSQLQuery(sb.ToString()).AddEntity(typeof(ResourceAssign))
//.CreateCriteria(typeof(ResourceAssign),"ra")
//.CreateAlias("TargetEffortEstimation","ee")
//.CreateAlias("ee.FiscalYear","fy")
//.CreateAlias("ee.TargetBudget","b")
//.CreateAlias("b.TargetProject","p")
//.CreateAlias("ee.Department","d")
//.Add(Expression.Eq("p.Id",projectId))
//.Add(Expression.Eq("fy.Id", fiscalYearId))
//.Add(Expression.Eq("b.bInProcess",true))
//.Add(Expression.Eq("d.Id", depId))
.List<ResourceAssign>();

return ra;

}