This project is read-only.

Mixed queries

In this example I'll mix one-to-many relationship and a subquery - trying to get all the employees whose salary is greater than average salary of employees from "Groogle" company:

// business restrictions of average salary of employee from "Groogle" company
EmployeeRec avgEmployeeRec = new EmployeeRec();
avgEmployeeRec.Company.Name.SetEq("Groogle");

// business restrictions for employees (all companies) whose salary 
// is greater than "average employee salary"
EmployeeRec overAvgSalaryEmployeeRec = new EmployeeRec();
overAvgSalaryEmployeeRec.Salary.SetGe(avgEmployeeRec.Salary.RecordedProjectionAvg);

//and building criteria
DetachedCriteria criteria = overAvgSalaryEmployeeRec.BuildCriteria();


And here is the final SQL query produced by NHibernate:

SELECT THIS_.ID          AS ID2_0_,
       THIS_.FIRSTNAME   AS FIRSTNAME2_0_,
       THIS_.LASTNAME    AS LASTNAME2_0_,
       THIS_.DATEOFBIRTH AS DATEOFBI4_2_0_,
       THIS_.SALARY      AS SALARY2_0_,
       THIS_.COMPANYID   AS COMPANYID2_0_,
       THIS_.JOBID       AS JOBID2_0_
FROM   BSF.EMPLOYEES THIS_
WHERE  THIS_.SALARY >= (SELECT AVG(THIS_0_.SALARY)  AS Y0_
                        FROM   BSF.EMPLOYEES THIS_0_
                               INNER JOIN BSF.COMPANIES ROOT_COMPA1_
                                 ON THIS_0_.COMPANYID = ROOT_COMPA1_.ID
WHERE ROOTCOMPA1.NAME = @p0)

Last edited Oct 16, 2008 at 9:31 PM by infomechanica, version 3

Comments

No comments yet.