Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Complex querying with NHibernate

I have this problem: When I try to implement Ayende's complex searching found at: http://ayende.com/Blog/archive/2006/12/07/ComplexSearchingQueryingWithNHibernate.aspx with the object graph: Person: M:1 Address: M:1 Street: M:1 Place: M:1 Country I get the following error: NHibernate.QueryException: Cannot use subqueries on a criteria without a projection. I am doing this:

public List<Person> Find() 
{ 
    DetachedCriteria query = DetachedCriteria.For<Person>(); 
    AddAddressQuery(query); 
    return personRepository.Find(query);
} 

private void AddAddressQuery(DetachedCriteria query) 
{ 
     DetachedCriteria addressQuery = null; 
     if (!String.IsNullOrEmpty(SearchParams.HouseNumer)) 
     { 
         addresaQuery = DetachedCriteria.For<Address>(); 
         addresaQuery.Add(Restrictions.Eq("HouseNumer", 
SearchParams.HouseNumer)); 
     } 
     this.AddStreetQuery(ref addressQuery); 
     if (addressQuery != null) 
     { 
         query.CreateCriteria("Address1", 
"address1").Add(Subqueries.Exists(addressQuery)); 
     } 
} 

private void AddStreetQuery(ref DetachedCriteria query) 
{ 
    DetachedCriteria streetQuery = null; 
    if (this.SearchParams.StreetId.HasValue) 
    { 
        streetQuery = DetachedCriteria.For<Street>(); 
        streetQuery .Add( Restrictions.Eq("Id", 
this.SearchParams.StreetId.Value)); 
    } 
    if (streetQuery != null) 
    { 
        query = query ?? Query.CreateCriteria("Address1"); 
        query.CreateCriteria("Street", 
"street").Add(Subqueries.Exists(streetQuery )); 
    } 
} 

What Am I doing wrong? Please help

like image 306
Luka Avatar asked Aug 24 '10 17:08

Luka


1 Answers

Just like the error message - you need to set a projection for any subqueries.

Your variable addressQuery, a DetachedCriteria, is used as a subquery, but it doesn't have a projection. The relevant portion of the query, when converted to SQL, would look like this:

... EXISTS(SELECT FROM Address WHERE HouseNumber = @HouseNumber)

... which is invalid SQL because no columns (a.k.a projections) have been specified in the select clause.

Use SetProjection to specify the columns.

like image 85
cbp Avatar answered Sep 22 '22 12:09

cbp