Merging query results with restrictions

Suppose, we want to get all the employees with salary greater than 8000, but user (currently logged in to our application) has no rights to see any information about managers. In order to face this problem, NHibernate Criteria Builder enables the possibility of recorders merging:

EmployeeRec employeeRec = new EmployeeRec();
employeeRec.Salary.SetGt(8000);

EmployeeRec employeeRestrictionsRec = new EmployeeRec();
employeeRestrictionsRec.Job.Name.SetNotEq("MANAGER");

DetachedCriteria criteria = (employeeRec & employeeRestrictionsRec).BuildCriteria();


..and here is the sql generated 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   EXAMPLESNHCB.DBO.EMPLOYEES THIS_
WHERE  (THIS_.ID IN (SELECT THIS_0_.ID AS Y0_
                     FROM   EXAMPLESNHCB.DBO.EMPLOYEES THIS_0_
                     WHERE  THIS_0_.SALARY > @p0)
        AND THIS_.ID IN (SELECT THIS_0_.ID AS Y0_
                         FROM   EXAMPLESNHCB.DBO.EMPLOYEES THIS_0_
                                INNER JOIN EXAMPLESNHCB.DBO.JOBS ROOT_JOB1_
                                  ON THIS_0_.JOBID = ROOT_JOB1_.ID
                         WHERE  NOT ROOT_JOB1_.NAME = @p1))

It's possible to merge recorders as a conjunction or disjunction (AND/OR)... Whatsmore, having that possibility
you can design one global restriction provider and before you get data from the database you can merge the restrictions with every recorder automagically . That approach keeps the logic of query untouched and makes your code more stabile and understandable.

Last edited Jan 18, 2009 at 4:18 PM by infomechanica, version 6

Comments

No comments yet.