Criteria building nhibernate

Question

Hi, I was wondering if someone could help with an example criteria for nhibernate. I think I've got my head around fairly basic things, like finding records in a given table who's field matches a certain value etc. Where I'm currently tripping up is where you have a table with a foreign key for another table, and attempting to find rows from the first table based on some field in the second table.

As an example...

If I have

tblUser
pk int    ID
   int    CompanyID
   string Name

and

tblCompany
pk int     ID
   string CompanyName

How would I build criteria to retrieve all users that belong to a company with a certain name? I think I understand how to build appropriate mapping files/objects, I just can't figure out how to make the criteria look against subproperties of the initial object.

Any examples woudl be awesome.

Answer 1

Criterias can't look at sub-properties directly, so if you want to access a sub-property you need to use an alias

session.CreateCriteria<User>()
    .CreateAlias("Company", "c") //the first argument is the property name from User
    .Add(Restrictions.Eq("c.Name", companyName))
    .List<User>();

Also, the same restriction on nested properties exists for aliases, so if you had User -> Company -> Owner you would need two aliases

.CreateAlias("Company", "c")
.CreateAlias("c.Owner", "o")

Also, if you prefer to use aliases for the whole query you can give the root entity an alias as well when creating the criteria.

session.CreateCriteria<User>("u")
    .CreateAlias("u.Company", "c")
    .Add(Restrictions.Eq("c.Name", companyName))
    .List<User>();

Answer 2

Your solution is great, it works perfectly. I can't vote you as my reputation is still slow. Thank you so much!!