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.