Apologies for the cross post, but this one's relevant to all hibernate users as well as nhibernate so thought you might be able to help ...
Can anyone suggest any simplification for the following query? I'm looking for people who have an address in paris and an address in london.
Query is currently so complex to avoid searching for 'person with address where town = london and town = new york' which is obviously wrong*
By the way, if you come up with a response, any suggestions on where I could have researched the answer would be appreciated also!
Code:
Junction addressJunction = new Conjunction();
DetachedCriteria addressCriteria = DetachedCriteria.For<Person>()
.SetProjection(Projections.Id())
.CreateCriteria("Addresses")
.Add(Restrictions.Like("Town", "paris"));
addressJunction.Add(Subqueries.PropertyIn("ID", addressCriteria));
addressCriteria = DetachedCriteria.For<Person>()
.SetProjection(Projections.Id())
.CreateCriteria("Addresses")
.Add(Restrictions.Like("Town", "london"));
addressJunction.Add(Subqueries.PropertyIn("ID", addressCriteria));
DetachedCriteria rootPersonQuery = DetachedCriteria.For<Person>("person")
.Add(addressJunction);
I'd ideally like a simple query that looks something like (pseudo-nhibernate)
Code:
Criteria = DetachedCriteria.For<Person>("person")
.ConunctionOf()
.CreateCriteria("Addresses").Add(Restrictions.Like("Town", "london"))
.CreateCriteria("Addresses").Add(Restrictions.Like("Town", "paris"))
Get my drift?
* Note: this returns the wrong 'person with address where town = london and town = new york'
Code:
Junction addressJunction = new Conjunction();
DetachedCriteria addressCriteria = DetachedCriteria.For<Address>("address")
.Add(Restrictions.Like("Town", "paris"))
.SetProjection(Projections.Id());
addressJunction.Add(Subqueries.PropertyIn("ID", addressCriteria));
addressCriteria = DetachedCriteria.For<Address>("address")
.Add(Restrictions.Like("Town", "london"))
.SetProjection(Projections.Id());
addressJunction.Add(Subqueries.PropertyIn("ID", addressCriteria));
DetachedCriteria rootPersonQuery = DetachedCriteria.For<Person>("person")
.CreateCriteria("Addresses")
.Add(addressJunction)
.SetProjection(Projections.Property("Surname"));
Login
FAQ
Search