Mapping entity oneToMany with fluent nhibernate

Question

The problem appears to be simple however I'm having so much trouble trying to map this entities. I just can't see what am I doing wrong. Can you guys help me?

I have the class Cliente:

public class Cliente
{    
    public Cliente () { }
    public virtual int ClienteId { get; set; }  
    public IList<Medidor> ListaMedidores { get; set; }   
    public virtual string NumeroMedidor { get; set; }       
}

And class Medidor

public class Medidor
{
    public Medidor() { }
    public virtual string NumeroMedidor { get; set; }
    public virtual string MarcaMedidor { get; set; }
    public virtual Cliente Cliente { get; set; }
}

I tried to map like this

public ClienteMap()
{
    Map(x => x.NumeroMedidor).Column("CORE_NUMERO_MEDIDOR");
    HasMany(x => x.ListaMedidores)
        .KeyColumn("NUMERO_MEDIDOR").Inverse().Cascade.All();
}


public MedidorMap()
{
    Table("medidor");
    LazyLoad();

    Id(x => x.NumeroMedidor).Column("NUMERO_MEDIDOR");
    Map(x => x.TipoMedidor).Column("TIPO_MEDIDOR");
    References(x => x.Cliente).Column("CORE_NUMERO_MEDIDOR");
}

The goal is bring my List of Medidor according to database. So I did:

Session.Query<Cliente>().Fetch(x => x.ListaMedidores).ToList();

And i'm getting the list always empty. Even having data on those tables... I would appreciate any kind of help or suggestion.

Regards

EDIT

My database is like this:

CREATE TABLE CLIENTE
(
  CORE_ID                      NUMBER           NOT NULL,
  CORE_NUMERO_MEDIDOR          VARCHAR2(50 BYTE)
)

CREATE TABLE MEDIDOR
(
  NUMERO_MEDIDOR  VARCHAR2(50 BYTE),
  MARCA_MEDIDOR   VARCHAR2(50 BYTE)
)

Given the sql select * from cliente where core_numero_medidor = '3569371':

CORE_ID CORE_NUMERO_MEDIDOR
123     3569371

and the sql select * from MEDIDOR where numero_medidor = '3569371':

NUMERO_MEDIDOR MARCA_MEDIDOR
3569371        general_motors
3569371        kia
3569371        FIAT

So I'm suppose to get 3 elements on my IList<Medidor> Lista Medidores on Cliente class..

EDIT

I changed to this:

public class Cliente
{    
    public Cliente () { }
    public virtual int ClienteId { get; set; }  
    public IList<Medidor> ListaMedidores { get; set; }   
    public virtual string NumeroMedidor { get; set; }       
}
public class Medidor
{
    public Medidor() { }
    public virtual string NumeroMedidor { get; set; }
    public virtual string MarcaMedidor { get; set; }
}

And changed the map of ClienteMap to:

Map(x => x.NumeroMedidor).Column("NUMERO_MEDIDOR");    
HasMany(x => x.ListaMedid)
            .KeyColumns.Add("NUMERO_MEDIDOR")
            .Table("MEDID")
            .PropertyRef("CoreNumeroCliente")
            .Cascade.All();

and now the list gets the expected number of records but all of them its the same as the first one. ie:

Expected

NUMERO_MEDIDOR MARCA_MEDIDOR
3569371        general_motors
3569371        kia
3569371        FIAT

My result

NUMERO_MEDIDOR MARCA_MEDIDOR
3569371        general_motors
3569371        general_motors
3569371        general_motors

Any suggestions? I would like to thank @Radim Köhler so far for the help.

ANOTHER EDIT

I found the solution!

I was trying to map a non-unique column as a primary key... I just changed the column to a real primary key and worked!

So now here's the solution

public class Cliente
{    
    public Cliente () { }
    public virtual int ClienteId { get; set; }  
    public IList<Medidor> ListaMedidores { get; set; }   
    public virtual string NumeroMedidor { get; set; }       
}
public class Medidor
{
    public Medidor() { }
    public virtual string NumeroMedidor { get; set; }
    public virtual string MarcaMedidor { get; set; }
}

public class ClienteMap : ClassMap<Cliente>
{
    public ClienteMap()
    {
        Map(x => x.NumeroMedidor).Column("NUMERO_MEDIDOR");    
        HasMany(x => x.ListaMedid)
            .KeyColumns.Add("NUMERO_MEDIDOR")
            .Table("MEDID")
            .PropertyRef("CoreNumeroCliente")
            .Cascade.All();
    }
}

public class MedidorMap : ClassMap<Medidor>
{
    public MedidorMap()
    {
        LazyLoad();

        Id(x => x.NumeroMedidor).Column("NUMERO_MEDIDOR");
        Map(x => x.MarcaMedidor).Column("MARCA_MEDIDOR");
        [...] //Other properties
    }
}

And here is my query:

Session.Query<CorteReligacao>()
                .Fetch(x => x.ListaMedid)

I really would like to thanks Radim Köhler for the help. His patience, attention and willingness to helping solve the problem leaves me with lack of way of thanks..I can only wish him all the best in life.

And I really hope that this thread may help people with the same problem.

Regards.

Answer 1

The one-to-many and many-to-one are always related by one column. This is such column, which contains reference ID (foreign key) to the other table / entity.

In our case, it must be column in table of Medidor, and its name would be "CORE_NUMERO_MEDIDOR". The mapping should look like this

public ClienteMap()
{
    ...
    HasMany(x => x.ListaMedidores)
       //.KeyColumn("NUMERO_MEDIDOR")
       .KeyColumn("CORE_NUMERO_MEDIDOR") // column in other table
       .Inverse().Cascade.All();
}


public MedidorMap()
{
    ...
    References(x => x.Cliente)
        .Column("CORE_NUMERO_MEDIDOR");  // column in this table
}

EXTEND

Based on extended question, when we can see this structure of tables

CREATE TABLE CLIENTE
(
  CORE_ID                      NUMBER           NOT NULL,
  CORE_NUMERO_MEDIDOR          VARCHAR2(50 BYTE)
)

CREATE TABLE MEDIDOR
(
  NUMERO_MEDIDOR  VARCHAR2(50 BYTE),
  MARCA_MEDIDOR   VARCHAR2(50 BYTE)
)

That the DB reference is different then in C#. It seems, like if

table CLIENTE references just one MEDIDOR, while MEDIDOR has many CLIENTEs.

It seems that the objects should look like this:

public class Cliente
{    
    ...
    //public IList<Medidor> ListaMedidores { get; set; }    
    //public Medidor Medidor { get; set; }    
}

public class Medidor
{
    ...
    //public virtual Cliente Cliente { get; set; }
    public virtual IList<Cliente> Clientes { get; set; }
}

and the mapping should be

public ClienteMap()
{
    ...
    References(x => x.Medidor, "CORE_NUMERO_MEDIDOR");
}


public MedidorMap()
{
    ...
    Id(x => x.NumeroMedidor).Column("NUMERO_MEDIDOR")
                               // column in this table to be compared
    HasMany(x => x.Clientes)
       .KeyColumn("CORE_NUMERO_MEDIDOR") // with column in other table
       .Inverse().Cascade.All();
}

ANOTHER EXTEND

Because the second table MEDIDOR is not having its own primary key (column NUMERO_MEDIDOR) but it could contain many same values... coming from CLIENT TABLE... we should use component mapping

public ClienteMap()
{
    ...
    Map(x => x.NumeroMedidor).Column("CORE_NUMERO_MEDIDOR");
    HasMany(x => x.ListaMedidores)
        .Component(com =>
        {
            com.Parent(y => y.Cliente, "NUMERO_MEDIDOR")
               .PropertyRef("NumeroMedidor")
               ;
            com.Map(y => y.MarcaMedidor, "MARCA_MEDIDOR");
        })
        .PropertyRef("NumeroMedidor")
        .Table("MEDIDOR")
       // .Inverse() // NO INVERSE, won't work
       .Cascade.All();

}

Answer 2

After all, with these SQL scripts (adjust for SQL Server in my case)

CREATE TABLE CLIENTE
(
  CORE_ID                      int           NOT NULL,
  CORE_NUMERO_MEDIDOR          VARCHAR(50)
)

CREATE TABLE MEDIDOR
(
  NUMERO_MEDIDOR  VARCHAR(50),
  MARCA_MEDIDOR   VARCHAR(50)
)

With these entities (all properties are virtual)

public class Cliente
{    
    public virtual int ClienteId { get; set; }  
    public virtual IList<Medidor> ListaMedidores { get; set; }   
    public virtual string NumeroMedidor { get; set; }       
}
public class Medidor
{
    public virtual string NumeroMedidor { get; set; }
    public virtual string MarcaMedidor { get; set; }
    public virtual Cliente Cliente { get; set; }
}

and with only this one mapping in place:

public class ClienteMap: ClassMap<Cliente>
{
    public ClienteMap()
    {
        Table("CLIENTE");
        Id(x => x.ClienteId, "CORE_ID");
        Map(x => x.NumeroMedidor).Column("CORE_NUMERO_MEDIDOR");
        HasMany(x => x.ListaMedidores)
            .KeyColumn("NUMERO_MEDIDOR")
            .Component(com =>
            {
                com.ParentReference(y => y.Cliente);
                com.Map(y => y.MarcaMedidor, "MARCA_MEDIDOR");
            })
            .PropertyRef("NumeroMedidor")
            .Table("MEDIDOR")
            // .Inverse() // NO INVERSE, won't work
            .Cascade.All();
    }
}

I can confirm, that this query will work:

var list = session.Query<Cliente>().Fetch(x => x.ListaMedidores).ToList();
var firt = list.First().ListaMedidores.First();
var last = list.First().ListaMedidores.Last();
Assert.IsTrue(firt.MarcaMedidor != last.MarcaMedidor);

BTW, this will be (my preferred) generated xml mapping:

<class xmlns="urn:nhibernate-mapping-2.2" name="Cliente" table="CLIENTE">
    <id name="ClienteId" type="System.Int32">
      <column name="CORE_ID" />
      <generator class="identity" />
    </id>
    <bag cascade="all" name="ListaMedidores" table="MEDIDOR">
      <key property-ref="NumeroMedidor">
        <column name="NUMERO_MEDIDOR" />
      </key>
      <composite-element class="Medidor">
        <parent name="Cliente" />
        <property name="MarcaMedidor" type="System.String">
          <column name="MARCA_MEDIDOR" />
        </property>
      </composite-element>
    </bag>
    <property name="NumeroMedidor" type="System.String">
      <column name="CORE_NUMERO_MEDIDOR" />
    </property>
</class>

For documentation see:

7.2. Collections of dependent objects