Querring tables with many-to-many relationships

In this example I want to get all the companies where people with LastName='Smith' are employed. Many-to-many relationship is one of the most complicated one... by the end it always turns into complicated set of joins and subqueries. Using NHibernate Detached Criteria we only need is to connect two recorders together:

// "Employee" restrictions: employee lastname is like 'Smith'
EmployeeRec employeeRec = new EmployeeRec();

// "Company" restrictions: we want only these companies where our Smith-employee works
CompanyRec companyRec = new CompanyRec();

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

SQL query generated by NHibernate:

SELECT this_.id        AS id3_0_, 
       this_.name      AS name3_0_, 
       this_.city      AS city3_0_, 
       this_.countryid AS countryid3_0_ 
FROM   bsf.companies this_ 
WHERE  EXISTS (SELECT this_0_.id AS y0_ 
               FROM   bsf.employees this_0_ 
               WHERE  this_0_.lastname LIKE @p0 
                      AND this_0_.companyid = this_.id) 

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


mindplay Jul 6, 2011 at 9:05 PM 
Although there is a many-to-many relationship between Companies and Jobs through Employees, this isn't a many-to-many mapping - your example schema only has one-to-many and many-to-one mappings, there is no defined many-to-many relationship in your example schema. (e.g. a straight link-table between two tables.)

I don't understand how to query over a many-to-many mapping with this library. Is it possible? If so, could you post an example, please?