Exception in NHibernate query when use Contains

Question

I have the following classes:

class Operation
{
    User User_AssignedTo;
    ResourceGroup ResourceGroup;
} 

class ResourceGroup
{
    List<User> UsersCollection;
}

And I have method. It takes user and returns operations. Something like that:

ResourceGroup resourceGroup = null;

query = conn.Session.QueryOver<Operation>()
        .JoinAlias(item => item.ResourceGroup, () => resourceGroup)
        .Where(item => item.User_AssignedTo.Id == user.Id || resourceGroup.UsersCollection.Contains(userDm));

but I have exception

Unrecognised method call: System.Collections.Generic.ICollection`1[[Mapping.Classes.User, Mapping.Classes, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8ab89f53b66a52c3]]:Boolean Contains

I don't NHibernate, but I guess the problem might be that it cannot translate `Contains()` into a SQL query. Could you use something like `resourceGroup.UsersCollection.Any(u => u.ID == userDm.ID)`? — Linus Caldwell, May 21 '13 at 19:55
NHibernate doesn't like the '||' operator. Try this: [http://stackoverflow.com/questions/3299022/nhibernate-or-criteria-query][1] [1]: http://stackoverflow.com/questions/3299022/nhibernate-or-criteria-query — jle, May 21 '13 at 19:59
@Linus: `Any` won't work here. QueryOver is translated into SQL, `.Any` is a method that QueryOver won't know how to convert. — Andrew Whitaker, May 21 '13 at 20:06

score 1 · Accepted Answer · edited May 23 '17 at 12:05

The Contains is a C# function. In SQL we would use the MyProperty IN (Select ...). To achieve that with NHibernate, to have the IN clause and the inner select - we can use the DetachedCriteria:

The documentation: 15.8. Detached queries and subqueries

There are some detailed examples how to use it:

Detailed how to, and advantages of the inner select (e.g. paging)
Inner select with group by cluase (e.g. to filter by select Max(id))

NOTE: I would like to provide you with more details even for your solution, some draft.. But the problem is the snippets in the question. To be able to use IN both parts should have some ID (the 1) ID to selected and 2) the ID to be compared). ResourceGroup is missing ID, hard to understand how the pairing tables behind are designed.

But at least the DetachedCriteria concept should give you correct direction...

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

NHibernate doesn't like the '||' operator. Try this: NHIbernate OR Criteria Query

Another example of how to do an Or operation: NHibernate JoinAlias query with null test not working

edited May 23 '17 at 11:43

Community

1
1

answered May 21 '13 at 20:01

jle

9,316
5
48
67

but how can I use resourceGroup.UsersCollection.Contains(userDm) in this solution? – Maksym May 21 '13 at 20:23
Is UsersCollection another table? if so, you will need another joinalias and join – jle May 22 '13 at 05:34

Exception in NHibernate query when use Contains

2 Answers2