nHibernate Many-to-Many query using Criteria API

Question

Hello,

Before asking I have looked at all relevant posts on this topic

I have also read this blog post: http://ayende.com/Blog/archive/2007/12/23/NHiberante-Querying-Many-To-Many-associations-using-the-Criteria-API.aspx

I have Teams and I have Members, there is many-to-many relationship between them

Basically: Member -> MemberTeam <- Team

With my query I try to get all members that belong to same team as queried member (including queried member)

I have created my tables using Following FluentHibernate:

TeamMap code:

Id(x => x.ID).GeneratedBy.GuidComb().UnsavedValue("00000000-0000-0000-0000-000000000000");

HasManyToMany(x => x.Members)
    .Table("MemberTeam")
    .ChildKeyColumn("TeamID")
    .ParentKeyColumn("MemberID");

MemberMap code:

Id(x => x.ID).GeneratedBy.GuidComb().UnsavedValue("00000000-0000-0000-0000-000000000000");

HasManyToMany(x => x.Teams)
    .Table("MemberTeam")
    .ChildKeyColumn("MemberID")
    .ParentKeyColumn("TeamID");

The code I do my query with is:

DetachedCriteria dCriteria = DetachedCriteria.For(typeof(Team), "team")
    .SetProjection(Projections.Id())
    .Add(Property.ForName("team.ID").EqProperty("mt.ID"));

ICriteria criteria = Session.CreateCriteria(typeof (Member), "member")
    .CreateAlias("Teams", "mt")
    .Add(Subqueries.Exists(dCriteria))
    .Add(Restrictions.Eq("mt.MemberID", new Guid(memberID)));

IList<Member> list = criteria.List<Member>();

I know that I'm doing something wrong, but I can not understand what it its

Any help would be appreciated

Thank You very much!

P.S. My map seams to be fine, I can save objects just fine!

Answer 1

By virtue of calling CreateAlias for the Teams path, NHibernate will join the appropriate tables for you. There's no need to do the subquery on member IDs:

var members = session
    .CreateCriteria<Member>("member")
    .CreateAlias("Teams", "mt")
    .List<Member>();

However, you're not even using the alias, so you might just prefer to eagerly get the teams:

var members = session
    .CreateCriteria<Member>("member")
    .SetFetchMode("Teams", FetchMode.Eager)
    .List<Member>();

Doing so will ensure you do not hit the database again when accessing each Member's Teams collection.

HTH,
Kent