nHibernate - Complicated many-to-many class mapping

I'm new to nhibernate, and I'm sorry if this is answered elsewhere, but I've been looking for the last couple of hours, and can't find a solution that works.

A bit of background: I'm trying to write an Admin area where there are users and sites, and a user can have access to multiple sites - but at various permission levels for each site.

Ideally I would like my classes look like this.

namespace MyApp.Users
{
   public class User
   {
      public virtual int Id { get; set; }
      public virtual string Name { get; set; }
      public virtual string Password { get; set; }
      public virtual IList<AdminUserSite> Sites { get; set; }
   }

   public class AdminUserSite
   {
      public virtual int UserTypeId { get; set; }
      public virtual Site AdminSite { get; set; }
      public virtual IList<Permission> Permissions { get; set; }
   }

   public class Permission
   {
      public virtual int Id { get; set; }
      public virtual int AreaID { get; set; }
      public virtual bool CanView { get; set }
      public virtual bool CanEdit { get; set }
   }
}

namespace MyApp.Sites
{
   public class Site
   {
      public virtual int Id { get; set; }
      public virtual string Title { get; set; }
   }
}

And my database schema looks like this

f_user
{
   f_user_id (int, PK)
   name (nvarchar(50))
   password (nvarchar(25))
}

f_user_site
{
   f_user_id (int, PK)
   f_site_id (int, PK)
   d_user_type_id (int)
}

f_perm
{
   f_perm_id (int, PK)
   f_site_id (int)
   f_user_id (int)
   d_area_id (int)
   can_read (bit)
   can_write (bit)
}

f_site
{
   f_site_id (int, PK)
   title (nvarchar(50))
}

And the hibernate mapping files currently look like: Users.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
   assembly="MyApp"
   namespace="MyApp.Users"
   default-lazy="true">

   <class name="User" table="f_user">
      <id name="Id" column="f_user_id">
         <generator class="identity" />
      </id>
      <property name="Name" column="name" />
      <property name="Password" column="password" />

      <bag name="Sites" table="f_user_site" inverse="true" cascade="all-delete-orphan">
         <key column="f_user_id"/>
         <one-to-many class="AdminUserSite"/>
      </bag>
   </class>

   <class name="Permission" table="f_perm">
      <id name="Id" column="f_perm_id">
         <generator class="identity" />
      </id>
      <property name="AreaId" column="d_area_id" />
      <property name="CanView" column="can_read" />
      <property name="CanEdit" column="can_write" />
   </class>

   <class name="AdminUserSite" table="f_user_site">
      <property name="UserTypeId" column="d_user_type_id" />
      <many-to-one name="Site" class="MyApp.Sites.Site, MyApp.Sites" foreign-key="f_site_id"></many-to-one>
   </class>

</hibernate-mapping>

and Sites.hbm.xml is

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
   assembly="MyApp"
   namespace="MyApp.Sites"
   default-lazy="true">

   <class name="Site" table="f_site">
      <id name="Id" column="f_site_id">
         <generator class="identity" />
      </id>
      <property name="Title" column="title" />
   </class>

</hibernate-mapping>

Individually the User, Permission and Site classes all map fine - but i just can't figure out what AdminUserSite should be, and I haven't even attempted to put the permissions list in there yet.

Does anyone have a开发者_运维百科ny ideas?

Any help would be very appreciated. Saan

---

I think you need to work on the database schema a bit. I think the AdminUserSite table is your problem.

Firstly, work out your entities (these should be simple nouns, which is why AdminUserSite seems out of place to me).

Entities: User, Permission, Site, SiteArea

Next, work out the relationships:

• 1 User has many Permissions

• 1 SiteArea has many Permissions

• 1 Site has many SiteAreas

(hope I got that right :) )

After that, your hbm and tables should flow more naturally.

Remember that normally you will have 1 table per entity, unless you have a many-many relationship (in which case you will need a joining table).

---

Generally a relation table mapping look like this :

<class name="AdminUserSite" table="f_user_site">
  <composite-id >
    <key-many-to-one name="Site" column="f_site_id" class="Site" />
    <key-many-to-one name="User" column="f_user_id" class="User"/>
  </composite-id>

</class>

In that way you can access either Site or User by its relation object or load relation object by criteria based on Site or User.

By the way, if your Permission object contains reference to user and site you may not need the AdminUserSite relation, the permission mapping already do it.

Edit about your comment Kind of replicating the same information in two spots.

Since NHibernate -like all ORM- has a first level cache you don't have to matter if your object can be accessed in two ways. You just have to ensure that your mappings are usefull to optimize and design your application well.

In this case, it's not a 'replication' but a reference. The object will be the same in the two spots if loaded by/retrieved from the same NHibernate session. Accessing an object in two ways if they have a logic (ie. direct way and cross-relation way) is not an heresy.

This is most a data layer work to achieve to provide right methods to access the right objects in the right way :)