XML mapping file for many-to-many relation with NHIbernate

I have a table User, fields : Id, Username

I have a table UserGroup, fields : Id, UsergroupName, Code

I'd like assign several Usergroup by user, that's mean, I have to have a third table with : UserId and UsergroupId, these table is named UsersGroups

I tried this code :

User table, XML :

<bag name="UsersGroups" generic="true" cascade="all" inverse="false" table="UsersGroups" >
    <key column="UserId" />
    <many-to-many column="GroupId" class="LookupUserGroup" />
</bag>

User table, C# :

public virtual IList<UserGroup> UsersGroups { get; set; }

Group table, C# (nothing in XML mapping):

public virtual User User { get; set; }

To add a group to a User, I use AddGroup in the User class, I do this :

public virtual void AddGroup(UserGroup userGroup)
{
    userGroup.User = this;
    UsersGroups.Add(userGroup); 
}

When I make a query on User, I'd like us开发者_如何学编程e linq to make some check on the "Code" field like this :

var res = (from c in MyUserList where c.UserGroup.Code == 1 select c); but USergroup field are not available in the intellisense.

Any idea ?

Thanks,

---

First, the design of your C# classes seems to be wrong. They do not reflect your intention of having a many-to-many relationship, but a one-to-many relationship. Since it usually does not make sense to allow only one user per group, you should change the UserGroup class to

public class UserGroup
{
    [...]

    public virtual IList<User> Users { get; set; }
}

and also change the mapping:

<bag name="Users" table="UsersGroups" lazy="true" cascade="all" inverse="true" >
    <key column="GroupId"></key>
    <many-to-many column="UserId" class="User" />
</bag>

Is the LookupUserGroup class correct? You should only have two classes for three tables in this simple case of a many-to-many relationship. If you have a User class and a UserGroup class, get rid of the LookupUserGroup class and replace that in the mapping in the User.hbm.xml.

The AddGroup method in your User class would then look a little like this:

public virtual void AddGroup(UserGroup userGroup)
{
    if(!this.UsersGroups.Contains(userGroup))
    {
         this.UsersGroups.Add(userGroup);
         userGroup.Users.Add(this); 
    }
}

The reason intellisense does not offer a UserGroup field is simply that you do not have such a field. The field is called UsersGroups (should be UserGroups) and is an IList<UserGroup>.

Unfortunately the current Linq provider in NHibernate 3.0 does not really support the kind of query you are trying to do here. Instead, you can try using QueryOver. In your case it would look like this (adapted from NHibernate QueryOver with ManytoMany)

var result = Session.QueryOver<User>()
    .Right.JoinQueryOver<UserGroup>(x => x.UserGroups) // the .Right. might not be required
    .Where(c => c.Code == 1)
    .List();

I did not implement or test any of this, so there may be minor errors.

---

Kris, a short note about your design decision. If you really want to have a real many-to-many relationship, then you don't need a third entity (UserGroup). You only need users and groups entities. Nhibernate will handle the creation of the intermediate table by itself. But, I never use this approach. The approach you have taken (with the usergroup entity) is what I like much more because it gives you an additional flexibility. You never know when it will be necessary to have additional information in the relationship between users and groups (the date a user has been added to a group, who did create this relationship, etc.) So I would use the UsersGroups entity, but the way you create the relationship is a many-to-one between User and UserGroups and also a many-to-one between Group and UserGroups. This way you get what you want with the additional flexibility.