Nhibernate many to many mapping with parameters inside the junction table

Question

I'm new into programming and I have a C# Visual studio project to do for school and we choosed to implements Nhibernate.

I believe that we are close to get something working, but there is this many to many association Mail that bothers me.

This is my user class:

public class Utilisateur
{
    public virtual int numUtilsateur { get; set; }
    public virtual String nomUtilisateur { get; set; }
    public virtual String prenomUtilisateur { get; set; }
    public virtual String adresseUtilisateur { get; set; }
    public virtual String cpUtilisateur { get; set; }
    public virtual String villeUtilisateur { get; set; }
    public virtual String telUtilisateur { get; set; }
    public virtual String mailUtilisateur { get; set; }
    public virtual float distanceParcourueSemaine { get; set; }
    public virtual String loginUtilisateur { get; set; }
    public virtual String passwordUtilisateur { get; set; }
    public virtual DateTime dateDernierLogin { get; set; }
    public virtual int nbTentatives { get; set; }

    public virtual TypeUtilisateur typeUtilisateur { get; set; }
    public virtual Planning planning { get; set; }
    public virtual PorteFeuille porteFeuille { get; set; }
    public virtual ISet<Conge> lesConges { get; set; }
    public virtual ISet<Interlocuteur> lesMails { get; set; }

    public Utilisateur()
    {

    }

    // ToString 
    public override string ToString()
    {
        return string.Format(
            "[{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}]",
            nomUtilisateur, prenomUtilisateur, adresseUtilisateur, cpUtilisateur, 
            villeUtilisateur, telUtilisateur, mailUtilisateur, loginUtilisateur, 
            passwordUtilisateur, dateDernierLogin, nbTentatives, 
            distanceParcourueSemaine, typeUtilisateur, planning, porteFeuille);
    }


}

and this is my client class:

public class Interlocuteur
{
    public virtual int idInterlocuteur { get; set; }
    public virtual string nomInterlocuteur { get; set; }
    public virtual string prenomInterlocuteur { get; set; }
    public virtual string telInterlocuteur { get; set; }
    public virtual string mailInterlocuteur { get; set; }
    public virtual Individu individu { get; set; } 
    public virtual Structure structure { get; set; }
    public virtual PorteFeuille portefeuille { get; set; }
    public virtual ISet<Utilisateur> lesMails { get; set; }
    public virtual IList<RendezVous> lesRendezVous { get; set; }

    public Interlocuteur()
    {

    }

    // ToString 
    public override string ToString()
    {
        return string.Format("[{0}|{1}|{2}|{3}|{4}|{5}]", 
            nomInterlocuteur, prenomInterlocuteur, telInterlocuteur, mailInterlocuteur, 
            individu, structure, portefeuille);
    }
}

The users can send mails to the clients through the software, and we are keeping track of its with a Mail class:

public class Mail
{
    // propriétés automatiques 
    public virtual int numMail { get; set; }
    public virtual string contenuMail { get; set; }
    public virtual string objetMail { get; set; }
    public virtual Utilisateur utilisateur { get; set; }
    public virtual Interlocuteur interlocuteur { get; set; }

    // constructeurs 
    public Mail()
    {
    }
    // ToString 
    public override string ToString()
    {
        return string.Format("[{0}|{1}|{2}|{3}]", 
            contenuMail, objetMail, utilisateur, interlocuteur);
    }
}

What I need to do is to retrieve all the mails sent by an user in a list with something like:

utilisateur.lesMails

I tried all sort of mapping Bags, Sets but nothing worked. There is another many to many association in the software and I did a bag mapping on both sides and it works like a charm but here this is different because I need to get a Mail object (the join class) and not directly the opposite class...

Someone help please :) !

Sorry for my bad english, I can give more info if needed Here is the MCD if you need it:

MCD

Edit:

This is my mail mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Maquette_Belle_Table_Final" assembly="Maquette_Belle_Table_Final">
  <class name="Mail" table="mail">
    <id name="numMail" column="numMail" />
    <property name="contenuMail" column="contenuMail"/>
    <property name="objetMail" column="objetMail"/>  
    <many-to-one name="utilisateur" column="numUtilisateur" />
    <many-to-one name="interlocuteur"  column="idInterlocuteur" />
  </class>
</hibernate-mapping>

My user mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Maquette_Belle_Table_Final" assembly="Maquette_Belle_Table_Final">
  <class name="Utilisateur" table="utilisateur">
    <id name="numUtilisateur" column="numUtilisateur" unsaved-value="0">
      <generator class="native" />
    </id>
    <property name="nomUtilisateur" column="nomUtilisateur"/>
    <property name="adresseUtilisateur" column="adresseUtilisateur"/>
    <property name="cpUtilisateur" column="cpUtilisateur"/>
    <property name="villeUtilisateur" column="villeUtilisateur"/>
    <property name="telUtilisateur" column="telUtilisateur"/>
    <property name="mailUtilisateur" column="mailUtilisateur"/>
    <property name="prenomUtilisateur" column="prenomUtilisateur"/>
    <property name="loginUtilisateur" column="loginUtilisateur"/>
    <property name="passwordUtilisateur" column="passwordUtilisateur"/>
    <property name="dateDernierLogin" column="dateDernierLogin"/>
    <property name="nbTentatives" column="nbTentatives"/>
    <property name="distanceParcourueSemaine" column="distanceParcourueSemaine"/>
    <many-to-one name="typeUtilisateur" column="codeTypeUtilisateur" 
        cascade="all" lazy="false"/>
    <one-to-one name="planning" constrained="true" foreign-key="none" 
        class="Planning" />
    <one-to-one name="porteFeuille" constrained="true" foreign-key="none" 
        class="PorteFeuille" />

    <set name="lesConges" table="conges_utilisateur" lazy="true">
      <key>
        <column name="numUtilisateur" not-null="true"/>
      </key>
      <many-to-many class="Conge">
        <column name="numConge" not-null="true"/>
      </many-to-many>
    </set>

    <bag name="lesMails">
      <key column="numMail" />
      <one-to-many class="Mail"/>
    </bag>

  </class>
</hibernate-mapping>

And my client mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Maquette_Belle_Table_Final" assembly="Maquette_Belle_Table_Final">
  <class name="Interlocuteur" table="interlocuteur">
    <id name="idInterlocuteur" column="idInterlocuteur" unsaved-value="0">
      <generator class="native" />
    </id>

    <property name="nomInterlocuteur" column="nomInterlocuteur"/>
    <property name="prenomInterlocuteur" column="prenomInterlocuteur"/>
    <property name="telInterlocuteur" column="telInterlocuteur"/>
    <property name="mailInterlocuteur" column="mailInterlocuteur"/>
    <one-to-one name="individu" constrained="true" foreign-key="none" 
        class="Individu" />
    <one-to-one name="structure" constrained="true" foreign-key="none" 
        class="Structure" />
    <one-to-one name="portefeuille" constrained="true" foreign-key="none" 
        class="PorteFeuille" />

    <bag name="lesRendezVous">
      <key column="idRdv" />
      <one-to-many class="RendezVous"/>
    </bag>

    <bag name="lesMails">
      <key column="numMail" />
      <one-to-many class="Mail"/>
    </bag>

  </class>
</hibernate-mapping>

For now I'm retrieving the mail list that concern a user with a custom query like this:

List<Mail> lesMails= session
    .CreateQuery("select * from utilisateur p where p.numUtilisateur = :num")
    .SetInt16("num", utilisateur.numUtilisateur);

What I would like to do is nhibernate to load directly the Mail list like this:

utlisateur.lesMails 

(If it's possible of course.)

Cheers !

Edit 1 :

I already tried the following :

Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?

It don't suits me because it gives me a list of the opposite class and not a list of the joining class.

Edit 2 :

My mail class mapping is now :

   <?xml version="1.0" encoding="utf-8" ?>
   <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Maquette_Belle_Table_Final"     assembly="Maquette_Belle_Table_Final">
 <class name="Mail" table="mail">
 <id name="numMail" column="numMail" unsaved-value="0">
 <generator class="native" />
 </id>
<property name="contenuMail" column="contenuMail"/>
<property name="objetMail" column="objetMail"/>  
<many-to-one name="utilisateur" column="numUtilisateur" />
<many-to-one name="interlocuteur"  column="idInterlocuteur" />
</class>
</hibernate-mapping>

i was forgetting <id name="numMail" column="numMail" unsaved-value="0"> <generator class="native" /> </id> but it still don't work.

Edit 3 :

it seems that's when using utilisateur.lesMails when logging with userId = 5 it loads the mail with id = 5 , when logging with userId = 2 it load the mail with id = 2, and so on. And if we log with userId=7 and there is no mailId = 7, it loads no mail even if a mail row got numUtilisateur=7 as a foreign key.

Any clues ?

Answer 1

If your Utilisateur and Interlocuteur classes are still coded as illustrated in your question, you have to change them.

They both still attempt to directly maps lesMails as a many-to-many ISet<Interlocuteur>/ISet<Utilisateur> property, short-circuiting indeed Mail. It should be changed in Utilisateur and Interlocuteur for:

public virtual ISet<Mail> lesMails { get; set; }

And change bag for set in your mappings, semantically it would be more sound. (A bag accept duplicated entries. You do not want to associate multiple times the same mail to the same user, don't you?)

The key property must refer the foreign key to parent inside child table. You are referencing the primary key of child instead, thus the behavior you witness.

In Utilisateur, change your mail collection mapping to:

<set name="lesMails">
  <key column="numUtilisateur" />
  <one-to-many class="Mail"/>
</set>

And in Interlocuteur:

<set name="lesMails">
  <key column="idInterlocuteur" />
  <one-to-many class="Mail"/>
</set>

Your Mail mapping does not specify a generator for its id. It is documented as mandatory, so please specify one.

Notes:

Your example query should be:

List<Mail> lesMails = session
    .CreateQuery("select p.lesMail from utilisateur p where p.numUtilisateur = :num")
    .SetInt32("num", utilisateur.numUtilisateur)
    .List<Mail>();

Your numerous one-to-one are not a sign of good design. Usually there are more many-to-one/one-to-many relations than one-to-one relations. The former are usually easier to manage than the later. The later should be reserved for cases where it strongly matters to enforce one-to-one relationship.

Your naming convention looks Java like. With .Net, visible members are to be written using PascalCase instead of camelCase.

If you receive errors, give their details on Stack Overflow. (Give at least the error message. "It does not work" just tell nothing.)