How to create a criteria in NHibernate that represents an OR between two EXISTS?

Question

Hi,

This one has been making my head hurt (which is easy since I'm a NHibernate newbie): how can I represent the following query (T-SQL) through the Criteria API?

DECLARE @pcode VARCHAR(8)
SET @pcode = 'somecode'

SELECT d.*
FROM document d
WHERE EXISTS (
          SELECT 1 
          FROM project p 
          WHERE p.id = d.projectId AND p.code = @pcode)
      OR EXISTS (
          SELECT 1 
          FROM job j INNER JOIN project p ON p.id = j.projectId
          WHERE j.id = d.jobId AND p.code = @pcode)

(A Document has two possible associations, Project or Job. Only one of them has a value at a given time; the other has null.)

The goal is to load all Documents that are directly associated with a given Project or indirectly through a Job.

Thanks.

Answer 1

I believe this could do the trick:

DetachedCriteria dCriteria1 = DetachedCriteria.For<Project>("project")
     .SetProjection(Projections.Property("project.Id"))
     .Add(Restrictions.EqProperty("doc.projectId", "project.Id"));

DetachedCriteria dCriteria2 = DetachedCriteria.For<Job>("job")
           .SetProjection(Projections.Property("job.Id"))   
           .CreateCriteria("Projects", "p")
           .Add(Restrictions.EqProperty("doc.jobId", "job.Id"))
           .Add(Restrictions.Eq("p.code", "somecode"));

var documents = NHibernateSessionManager.Session.CreateCriteria<Document>("doc")
     .Add(Restrictions.Or(
            Subqueries.Exists(dCriteria1),
         Subqueries.Exists(dCriteria2))).List<Document>();

The above code is derived from the sample query that you provided. If it is not entirely accurate you can change it a bit to make it workable in your solution.