How to convert linq query to Nhibernate's Criteria?

Question

I am tying to rewrite a module which interacts with NHibernate. The buisness logic becomes more sofisticated and we decided to change our linq queries to Nhibernate's Criteria. Here is the old code:

nhSession.Query<User>().Where(
    u => u.Roles.Contains(query.Role)
)

And the new code:

var criteria = nhSession.CreateCriteria<User>("user");
criteria.Add(/* contains? */);

And mapping:

<class name="User" table="users">
    <id name="Id" column="id">
        <generator class="hilo">
            <param name="table">hilo</param>
            <param name="column">user_id</param>
            <param name="max_lo">10</param>
        </generator>
    </id>

    <property name="Password" column="password" />

    <bag name="Roles" table="user_roles">
        <key column="user_id" />
        <element column="role" />
    </bag>
</class>

Where Roles is an enum.

How to make the query with Criteria behave like the Linq query?

score 1 · Accepted Answer · answered Nov 15 '13 at 09:51

1

var criteria = nhSession.CreateCriteria<User>("user");
var roleCriteria = criteria.CreateCriteria("Roles","roles");
roleCriteria.Add(Expression.Eq("role",Role.YourRole);

answered Nov 15 '13 at 09:51

Anand

717
6
20

Thank you. After that, do I get results from the criteria or the roleCriteria? – x2bool Nov 15 '13 at 11:17
You will get result for User.If you want result of select fields from User and Role use ResultSet Transformers(http://stackoverflow.com/questions/2207329/projections-in-nhibernate). – Anand Nov 15 '13 at 13:01

score 1 · Answer 2 · edited May 23 '17 at 11:59

Assuming the user_roles table is mapped to a UserRole Class, and from this question, you may try something like :

DetachedCriteria dCriteria = DetachedCriteria.For<UserRole>("ur")
    .SetProjection(Projections.Property("ur.UserId"))
    .Add(Restrictions.EqProperty("ur.UserId", "user.Id"))     
    .Add(Restrictions.Eq("ur.Role", query.Role));

var criteria = nhSession.CreateCriteria<User>("user")
    .Add(Subqueries.Exists(dCriteria)).List<User>();

How to convert linq query to Nhibernate's Criteria?

2 Answers2