Search code examples
asp.netnhibernatenhibernate-criteria

Getting a row count on joined tables using NHibernate

I have a Many-To-Many relationship consisting of the following 3 tables (non relevant fields removed from brevity), in that an advert may be assigned multiple tags, and a tag can be assigned to multiple adverts.

Adverts 

(PK) Id

AdvertsTags 

(FK) AdvertId
(FK) TagId

Tags 

(PK) Id

I want to create an NHibernate query that counts how many Adverts are assigned a specific Tag (i.e. a specific TagId).

In normal sql I would do something along the lines of:

SELECT COUNT(*)
FROM Adverts A
JOIN AdvertsTags AT ON A.Id = AT.AdvertId
WHERE AT.TagId = @tagId

The property names of my Advert and Tag models are as per the field names in the tables shown above.

I don't know what would be the recommended way of doing this using NHibernate?

Solution

  • Maybe something like this would help:

    var count = session
    .CreateCriteria(typeof(Advert))
    .SetProjection(Projections.RowCount())
    .CreateCriteria("Tags")
    .Add(Expression.Eq("TagId", tagId)
    .UniqueResult<long>();

    More info here: http://www.nhforge.org/doc/nh/en/index.html#querycriteria-associations