4

I am having trouble working out how to get the following sql into an NHibernate ICriteria:

select * from Contract contract_outer
where exists (
    select 1 from RequiredDocRules
    where not exists (
        select 1 from Contract contract_inner
        inner join ContractDocs contractDocs on cDocs.ContractId = c_inner.Id
        inner join Doc doc doc.Id = contractDocs.DocId
        where contract_inner.Id = contract_outer.Id
        and doc.Type = RequiredDocRules.DocType)
    )
    and RequiredDocRules.ContractType = contract_outer.Type
)

Basically, the query is saying "Show me contracts that are missing required documents, based on the rules for this type of contract".

The CreateCriteria looks like this:

var subqueryB = DetachedCriteria.For<Contract>("contract_inner")
    .CreateAlias("contract_inner.Docs", "doc")
    .Add(Restrictions.EqProperty("doc.Type", "rule.DocType"))
    .Add(Restrictions.EqProperty("contract_inner.Id", "contract_outer.Id")
    .SetProjection(Projections.Id());        

var subqueryA = DetachedCriteria.For<RequiredDocRule>("rule")
    .Add(Restriction.EqProperty("rule.ContractType", "contract_outer.Type"))
    .Add(Subqueries.NotExists(subqueryB))
    .SetProjection(Projections.Id());

return Session.CreateCriteria<Contract>("contract_outer")
    .Add(Subqueries.Exists(subqueryA))
    .List<Contract>();

This above code throws the following obscure exception:

Could not find a matching criteria info provider to: contract_inner.Id = contract_outer.Id and doc.Type = rule.DocType

I've narrowed the issue down to the "contract_inner.Id = contract_outer.Id" restriction. Maybe it is having trouble traversing up through two layers of subqueries?

I am using NHibernate 2.1 btw.

I seem to be the only person getting this exception - if you google for the exception message another one of my Stackoverflow posts comes up number one. This question is similar, but different. So frustrating!

cbp
  • 25,252
  • 29
  • 125
  • 205
  • 1
    any particular reason to use Criteria and not HQL here? – Diego Mijelshon Jan 24 '11 at 14:27
  • Just for consistency across the repository. Its a pain getting to the end of writing the query then finding it doesn't work and having to convert it all over to HQL. – cbp Jan 25 '11 at 00:52
  • have you considered using Linq2Nhibernate ? take a look at this post :http://stackoverflow.com/questions/624609/linq-to-nhibernate – Beatles1692 Feb 17 '12 at 09:19

0 Answers0