Sorry for the cryptic title..
Could you help me out, on how do a select, based on the count of a property, using Criteria? I have an object (Pool) with a property (PoolItems), and i want to select all the Pools w. more than 5 PoolItems.
+4
A:
Try this:
DetachedCriteria dCriteria = DetachedCriteria.For<PoolItem>("pItem")
.SetProjection(Projections.Count("Id"))
.Add(Restrictions.EqProperty("pItem.PoolID", "pool.Id"));
IList<Post> posts = Session.CreateCriteria<Pool>("pool")
.Add(Subqueries.Gt(5, dCriteria)).List<Pool>();
Assuming the PoolItem table has a PoolID column as a foreign key of the Pool table. The relationship is a one-to-many. If you do not have the PoolID property mapped in the PoolItem class and you just have the many-to-one object mapping named "Pool" then replace in the detached criteria the "pItem.PoolID" with "pItem.Pool.Id".
tolism7
2009-12-02 22:49:50
Ok, that helped a lot, but I'm not quite there yet. The mapping is not really represented in the PoolItem class, only via the foreign key in the database, and the mapping on the Pool class.Should i expose the relationship through a Pool property, or is there another/better way to accomplish this? I know i can do it via SQL, but i would rather learn the Criteria way :o)
hhravn
2009-12-03 08:02:03
Mapping the Pool property in the PoolItem class is not only a good idea regarding the schema representation of the entities it is also a good idea because it allows you to use the relationship to do things like the above solution. If you prefer not having such a property in your class because you want the using code not to have access to the Pool or the PoolID property (you can expose them both if you like just have one as read only - insert="false" update="false") you can have it mapped to private field instead (access="field").
tolism7
2009-12-03 09:38:51