NHibernate - Multiple table mapping?

Question

I'm working on a project which handles Users and Projects:

A user may be assigned to zero or X projects.
A user has read or write rights on a project.

In my database, I have a table named Users_Projects_Rights, which is used to associate a user to a certain number of projects, as well as specify their access rights on them (it's simply a boolean value, true for write rights, false for read rights).

The Users_Projects_Rights table columns are:

id_User
id_Project
CanWrite (boolean)

My User class currently has this collections:

    /// <summary>
    /// Gets or sets a user's accessible projects, with the value being 
    /// the name of the project.
    /// </summary>
    public virtual IDictionary<int, string> AssignedProjects { get; set; }

Taking in consideration that I have a Projects table, which has a Name column:

How can I map my AssignedProjects dictionnary, in my User class, in order to have the project Id as the key (using a ternary mapping with the Users_Projects_Rights table) and the project name (taken from the Projects table) as the value ?

Here is what I came up with, but I have no idea how to map the value of the dictionary:

<map name="AssignedProjects" table="Users_Projects_Rights">
  <key column="id_User"/>
  <map-key column="id_Project" type="Int32"/>
  (How do I map the value... ?)
</map>

score 0 · Answer 1 · answered Dec 20 '11 at 07:25

0

Your design is wrong.Because a user can have many project so

public virtual IDictionary AssignedProjects { get; set; }

will throw key already added exception if mapped.So design as user have list of projects which are assigned to him

public virtual List AssignedProjects { get; set; }

answered Dec 20 '11 at 07:25

Anand

717
6
20

Why would it throw that exception..? You mean if a user is assigned twice to the same project..? – Hussein Khalil Dec 20 '11 at 07:29
no..current user can assigned to multiple project.Bcoz current user id is a key in our mapping – Anand Dec 20 '11 at 07:37
No, the key is the project Id and the value is the project name. – Hussein Khalil Dec 20 '11 at 07:40

score 0 · Accepted Answer · answered Dec 20 '11 at 07:47

I got it, if someone has a more efficient answer please let me know:

<map name="AssignedProjects" table="Users_Projects_Rights">
  <key column="id_User"/>
  <index column="id_Project" type="System.Int32"/>
  <element formula="(SELECT Projects.name FROM Projects WHERE Projects.id = id_Project)"/>
</map>

NHibernate - Multiple table mapping?

2 Answers2