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.