I would also settle for instruction on how to access the existing
join alias (Customer has the List<AnTn> mapped inverse, join)
thus producing ..

   where Billing_ID is null and ..
            and ( joinedantns.Telephone_Number = <val>
                    and joinedantns.Account_Number = <val> )


but for now, i've settled with direct sql ..

    var tempCustQuery =
session.CreateCriteria<TechSupportCustomer>("tempCusts")
        .Add(nhc.Expression.IsNull("BillingInstanceID"))
        .Add(nhc.Expression.Sql("({alias}.SAVED_ACCOUNT_ID is null)"))
        .Add(nhc.Expression.Sql(new StringBuilder().Append(
            "exists (select 1 from ts_customers_ans_tns a").Append(
            "    where {alias}.ID = a.CUSTOMER_ID ").Append(string.Format(
            "      and a.TELEPHONE_NUMBER = '{0}'",
dslPhone)).Append(string.Format(
            "      and a.ACCOUNT_NUMBER = '{0}')",
billingPhone)).ToString()))
        .SetResultTransformer(new
NHibernate.Transform.DistinctRootEntityResultTransformer());

    var results = tempCustQuery.List<TechSupportCustomer>();


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


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

> 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