Subquery in Criteria of nHibernate

Question

I need some help with subqueries. My problem seems easy but I can't figure it out.

I have 3 entities: - Companies - Cars - Employees

Employee has a list of Companies (but Company doesnt have a list of Employees). Car has one company.

I need to retrieve all cars from every company some Employee has access to.

Database structure in case it helps.

Company

id   |     Name     

Employee

id   |     Name    

CompanyToEmployee

employee_id   |     company_id    

Car

id   |  Name   |   company_id    

As gmiley sugested, I`m adding some mora info.

What I did so far: Not much.

ICriteria consult = Session.CreateCriteria<Car>();

DetachedCriteria c = DetachedCriteria.For<Employee>()
   .SetProjection(Projections.Property("Companies"))
   .Add(Restrictions.Eq("Id", employee.Id));

consult.Add(Subqueries.PropertyIn("Company.Id", c));

But what it returns is all cars within the company with the same Id 'employee' has. And I can see why. But I cant manage to change it to my needs.

Dumb data to clarify:

There are 2 employees: "Bill Gates" e "Steve Jobs.

Bill Gates works for Google and Microsoft.

Steve Jobs works for Google and Apple.

Google has 2 cars: "Car 1" and "Car 2".

Microsoft has 1 car: "Car 3"

Apple has 3 cars: "Car 4", "Car 5" and "Car 6".

What I need: All cars from all companies Bill Gates works for.

In this case, cars: "Car 1", "Car 2" and "Car 3".

Thank you in advance!

Answer 1

I manage to solve my problem in a very simple way:

        foreach (var company in user.Companies)
        {
            companies.Add(company.Id);
        }

        consult.Add(Expression.In("Company.Id", companies));

Since I already have user companies list, this doesnt give me performance issues.