Relationship mapping without relationship entity class

[pedrofernandesfilho]

Considering 3 tables in database, they are referenced in a many to many relationship and the intermediate table (TB_PRODUCT_FIELD) have an extra column.

TB_PRODUCT ( ID, Name )

TB_FIELD ( ID, Name )

TB_PRODUCT_FIELD (  --> Intermediate table, many-to-many
  ProductID, 
  FieldID, 
  Order --> Extra column
) 

I have 2 classes:

class Product {
  public virtual long Id { get; set; }
  public virtual string Name { get; set; }
  public virtual IList<Field> Fields { get; set; }
}
class Field {
  public virtual long Id { get; set; }
  public virtual string Name { get; set; }
  public virtual short Order { get; set; }
}

I would like to create this relationship without creating the relationship entity class itself (TB_PRODUCT_FIELD).

I got this working before adding this new column named "Order" but now I couldn't get the value from that in Field.Order property.

Is it possible to do that? If so, how?

Thanks in advance!

[fredericDelaporte]

If that is a true many-to-many, then modeling it with theses classes looks like an error: a field could have different orders, one for each product it is bound to.
Otherwise you may use a formula for mapping your Order column, something like formula="(select top 1 pf.Order from TB_PRODUCT_FIELD pf where pf.FieldID = ID)".

[pedrofernandesfilho]

Using Formula is it possible to read and write to a column?

I created a repository with the scenario I mentioned.
The main branch is without the extra column (Order).
And in the extra-column branch the extra column (Order) is implemented.

https://github.com/pedrofernandesfilho/nhibernate-map
https://github.com/pedrofernandesfilho/nhibernate-map/tree/extra-column

In the repository there is a docker-compose file to start the database.
And also 2 integration tests. One to save Product without Fields and one to save Products With Fields.

In the mapping of the Field there are some commented codes that were used to try to make the mapping work allowing the reading and writing of the extra column (Order).

The Fields must already exist and when inserting or updating the Product, a Field must not be created in the TB_FIELD table.

Do you have any suggestions on how it could be done based on this implementation of the scenario?

Thanks!

[fredericDelaporte]

You cannot write to a property mapped as a formula. I do not think you can do a read-write mapping with your db schema and your class model.

[pedrofernandesfilho]

From what I have studied it seems to me that a good solution is to map the table TB_PRODUCT_FIELD to a class (ProductField for example).

Do you have an idea of a more suitable solution?

[fredericDelaporte]

No better solution on my side, that is how I would do it.

[pedrofernandesfilho]

Ok. Thanks a lot!