If I remove:

        .Add(Property.ForName("ID").EqProperty("antns.ID"));

I don't get an exception, but the resulting query is wrong:

        ... and exists ( select 1 from customers_antns antns
                              where antns.TN = <val> and antns.AN = <val> )

(it doesn't restrict the sub-query correctly .. )

how can I make this work?

I've also tried creating an alias for the ID of the root customer query, but
that errors w/:

        "ID is not an association"

     // .. sub-query
     .Add(Restrictions.EqProperty("CustomerID", "custID"));
      // add the subquery to the customer query
     tempCustQuery
        .CreateAlias("ID", "custID")
        .CreateCriteria("ANsTNs", "antns")
            .Add(Subqueries.Exists(antnMatchQuery));


------
Joe Brockhaus
[email protected]
------------


On Wed, Sep 21, 2011 at 4:45 PM, Joe Brockhaus <[email protected]> wrote:

> i'm getting a key not found error on
> tempCustQuery.GetExecutableCriteria(session)
>
> i'm using this as a guide:
> http://ayende.com/blog/1900/complex-searching-querying-with-nhibernate
>
> I'm trying to get all customers that have a child with the given 2 column
> values.
>
>     // temp customer query
>     var tempCustQuery =
> DetachedCriteria.For<TechSupportCustomer>("tempCusts")
>         .Add(nhc.Expression.IsNull("BillingInstanceID"))
>         .Add(nhc.Expression.Sql("({alias}.SAVED_ACCOUNT_ID is null)"))
>         .SetResultTransformer(new
> NHibernate.Transform.DistinctRootEntityResultTransformer());
>
>     // create the subquery for the antn matches
>     var antnMatchQuery = DetachedCriteria.For<TechSupportCustomerAnTn>()
>         .Add(Restrictions.Eq("TelephoneNumber", dslPhone))
>         .Add(Restrictions.Eq("AccountNumber", billingPhone))
>         .SetProjection(Projections.Id()) // only query/hydrate the ID in
> the final query
>         .Add(Property.ForName("ID").EqProperty("antns.ID"));
>
>     // add the subquery to the customer query
>     tempCustQuery.CreateCriteria("ANsTNs", "antns")
>         .Add(Subqueries.Exists(antnMatchQuery));
>
>    // get resulting customer entities
>     var results = tempCustQuery.GetExecutableCriteria(session)
>         .List<TechSupportCustomer>();
>
> in SQL:
>
> select * from customers c
> where BILLING_ID is null and SAVED_ID is null
> and exists ( select 1 from customers_antns antns
>                   where antns.cust_id = c.id
>                   and antns.TN = <value> and antns.AN = <value>)
>
> what am I doing wrong?
> (and at this point, why shouldn't I just do it with Expression.Sql??)
>
> ------
> Joe Brockhaus
> [email protected]
> ------------
>

-- 
You received this message because you are subscribed to the Google Groups 
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to 
[email protected].
For more options, visit this group at 
http://groups.google.com/group/nhusers?hl=en.

Reply via email to