Subqueries

With NHibernate Criteria Builder is possible to get one recorder property as a projection and use it on another property as a restriction - the result is a subquery, In this example I want to get all the the companies from "NORTH_AMERICA" geographical region:

//composing business query
CountryRec countryRec = new CountryRec();
countryRec.Region.SetEq("NORTH_AMERICA");

CompanyRec companyRec = new CompanyRec();
companyRec.Country.Name.SetEq(countryRec.Name.RecordedProjection);

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


SELECT this_.id            AS id3_1_, 
       this_.name          AS name3_1_, 
       this_.city          AS city3_1_, 
       this_.countryid     AS countryid3_1_, 
       root_count1_.id     AS id0_0_, 
       root_count1_.name   AS name0_0_, 
       root_count1_.region AS region0_0_ 
FROM   bsf.companies this_ 
       INNER JOIN bsf.countries root_count1_ 
         ON this_.countryid = root_count1_.id 
WHERE  root_count1_.name = (SELECT this_0_.name AS y0_ 
                            FROM   bsf.countries this_0_ 
                            WHERE  this_0_.region = @p0) 

Last edited Oct 16, 2008 at 8:28 PM by infomechanica, version 2

Comments

No comments yet.