U don`t use your subCriteria you need to add criteria.Add(Subqueries.Exists(subCriteria));
On 30 Gru, 20:20, BZaino <[email protected]> wrote: > Hi, > I have a database with a Person table and a PersonAddress table. > The Person table has a many to one relationship with the PersonAddress > table, which means that my Person entity has a collection of > PersonAddress records in it, amongst other properties. Currently I > have 2 records in the PersonAddress table, one with a state of MA, and > another with a state of CT. > > I want to return all person records that have a Person.CustomerId = 1 > and a PersonAddress.State = "MA". I expect to get back one Person > record with one PersonAddress record in my collection. However, when > I browse the collection, I have 2 records in the PersonAddress table, > one with a state of MA, and another with a state of CT. In addition, > I can verify that a second fetch to the DB is performed when I view > the collection, basically doing a SELECT * from the PersonAddress > table. > > What is confusing to me is, if I run my search using HQL and browse > the PersonAddress collection within my Person entity, I see only 1 > address. However, if I use ICriteria to do my search and browse the > PersonAddress collection, I see both PersonAddress records. Is there > any reason why this is so? Is it possible to filter records based on > both parent and child criteria? Is my code not building out the > ICriteria correctly? What do I need to do to link my DetachedCriteria > object with the ICriteria object? My sample code: > > ICriteria criteria = Session.CreateCriteria(typeof > (Person), "pers") > .Add(Expression.Eq("pers.Customerid", 1));; > > DetachedCriteria subCriteria = > DetachedCriteria.For<PersonAddress>("persAddr"); > subCriteria.Add(Expression.Conjunction() > .Add(Expression.Like("persAddr.State", "MA")) > .Add(Expression.EqProperty > ("persAddr.PersonID","pers.PersonID"))); > > subCriteria.SetProjection(Projections.Id()); > > criteria.SetResultTransformer(new > NHibernate.Transform.DistinctRootEntityResultTransformer()); > > retObject = criteria.List<Person>() as List<Person>; > > Many thanks in advance -- 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.
