Querying tables with one-to-many relationships

Querying the database across multiple tables is the main usage of recorders. Let's suppose that we want to get all employees from "Groogle" company on condition that employee salary is greater than 1200. Our business restructions will look like this:

//composing business query
EmployeeRec employeeRec = new EmployeeRec();

//composing criteria
DetachedCriteria criteria = employeeRec.BuildCriteria();

In this example the getter of Company property does the trick and returns new instance of CompanyRecorder.cs so you can write employeeRec.Company.Name.SetEq("Groogle") directly!

You can individually specify your requirements for every recorder and than connect the recorders together:

// "Company" restrictions
CompanyRec companyRec = new CompanyRec();

// "Employee" restrictions
EmployeeRec employeeRec = new EmployeeRec();            
employeeRec.Company = companyRec;

//composing criteria
DetachedCriteria criteria = employeeRec.BuildCriteria();

The query generated by NHibernate will look like this one:

SELECT this_.id               AS id2_1_, 
       this_.firstname        AS firstname2_1_, 
       this_.lastname         AS lastname2_1_, 
       this_.dateofbirth      AS dateofbi4_2_1_, 
       this_.salary           AS salary2_1_, 
       this_.companyid        AS companyid2_1_, 
       this_.jobid            AS jobid2_1_, 
       root_compa1_.id        AS id3_0_, 
       root_compa1_.name      AS name3_0_, 
       root_compa1_.city      AS city3_0_, 
       root_compa1_.countryid AS countryid3_0_ 
FROM   bsf.employees this_ 
       INNER JOIN bsf.companies root_compa1_ 
         ON this_.companyid = root_compa1_.id 
WHERE  root_compa1_.name = @p0 
       AND this_.salary > @p1 

Last edited Oct 16, 2008 at 8:19 PM by infomechanica, version 3


No comments yet.