How do I create this query using NHibernate Criteria

Question

I'm new to NHibernate, and I'm having trouble figuring out the best way to represent some SQL using NHibernate's Criteria engine. Here's a basic depiction of the object model:

public class Project : EntityBase<Project>
{
    // Properties for a project
    public virtual Company OwnerCompany { get; set; }
    public virtual IList<UserAssignment> UserAssignments { get; set; }
}

public class Company : EntityBase<Company>
{
    // Properties for a company
}

public class User : EntityBase<User>
{
    // Properties for a user
    public virtual Company Company { get; set; }
}

public class UserAssignment : EntityBase<UserAssignment>
{
    // Properties for an assignment
    public virtual User User { get; set; }
}

You can infer from the classes what the underlying tables look like, and all the NH stuff is in the EntityBase abstract class.

Essentially I want to pull all Projects owned by a specific company or having UserAssignments for someone from that company. Here's how I might do it in SQL:

select P.*
from Project P
where P.OwnerCompany_Id = @CompanyId
    or P.Id in (
        select Project_Id
        from UserAssignment UA
            join User U on UA.User_Id = U.Id
        where U.Company_Id = @CompanyId
    )

I'm using DetachedCriteria, and I can't even get it to work just matching a UserAssignment's User to a company, much less both the assignments and the owner. When I tried this:

var criteria = DetachedCriteria
                .For<Project>()
                .CreateCriteria("UserAssignments")
                    .Add(Expression.Eq("User.Company.Id", requestingUser.Company.Id));

I get an error saying "could not resolve property: User.Company.Id of TestProject.Domain.UserAssignment."

Can anyone help?

Answer 1

I ended up having to use a subquery like this:

var userSubquery = DetachedCriteria.For<UserAssignment>()
    .SetProjection(Projections.Property("Project")) // I had to put this reference property in the UserAssignment
    .CreateCriteria("User")
    .CreateCriteria("Company")
    .Add(Restrictions.Eq("Id", requestingUser.Company.Id));

var projectCriteria = DetachedCriteria.For<Project>()
    .Add(Restrictions.Or(
        Restrictions.Eq("OwnerCompany.Id", requestingUser.Company.Id),
        Subqueries.PropertyIn("Id", userSubquery)));

I wanted to avoid adding the Project property to the UserAssignment object since that object can apply to a few other things besides projects, but I couldn't figure out how to force the join using a Criteria without it. I didn't want to use a SQL expression or HQL.

Josh