How can I add OR query in NHibernate between a property and a many to many?

Question

I have a Document class which has a User Property which is the User who owns the document, but also I have a User List. Users which have access to the document. I am trying to create a query which will return documents which either I own or I have access to.

IList<Document> results = UnitOfWork.CurrentSession.CreateCriteria(typeof(Document))
                .CreateCriteria("Owner")
                .CreateCriteria("UserList")
                .Add(nh.Criterion.Restrictions.IdEq(obj.Id))
                .SetFirstResult(pageSize * page)
                .SetMaxResults(pageSize)
                .List<Document>();

This above is what I have tried so far but to no avail. The structure of the Document class, shortened is:

public class Document : DomainObject<Document>{
     public Document(){
          UserList = new List<User>();
     }
     public virtual User Owner{get;set;}
     public virtual IList<User> UserList{get;set;}
}

I appreciate any guidance on this!!

Cheeers,

Andrew

EXAMPLE SQL I AM LOOKING FOR

declare @UserID uniqueidentifier

set @UserID = '37f7a55f-84c9-461b-a5b2-b412fe96932b'

select * from [Document] s
where s.UserID = @UserID
or exists(select * from DocumentUser su where su.userid = @UserID)

Answer 1

Can you please post the SQL that would return what you want ? .

I'm thinking there is a subquery in there - a detached criteria might help in that case. Then adding it to the ICriteria like : Add(Subqueries....)

Answer 2

I did it!

Thanks for your help! That last comment you made and asking me for the sql made me looked at it again clear: Here is the solution:

    var detached = DetachedCriteria.For<Document>()
        .CreateCriteria("UserList")
        .SetProjection(Projections.Id())
        .Add(Restrictions.IdEq(obj.Id));

    IList<Document> results = UnitOfWork.CurrentSession.CreateCriteria(typeof(Document),"s")
        .CreateAlias("s.User","u")
        .Add(Restrictions.Or(Restrictions.Eq("u.Id", obj.Id), Subqueries.Exists(detached)))
        .SetFirstResult(pageSize * page)
        .SetMaxResults(pageSize)
        .List<Document>();