0

I have a simple case with two entities Machine and Product, where a Machine can have zero or one Product. The reverse association (which would be a collection of some kind) is not needed in code.

The problem is in the database model which is legacy and I cannot alter the structure of the Machine table, so I created an extra table MachineProduct with foreign keys to both tables. Basically what you would do for a many-to-many relationship but in practice the MachineId in this table will be unique.

Now I need to map this in NHibernate. What I can do is map it as a many-to-many collection and make sure there's no more than one element in the Machine.Products collection but I feel there should be a more accurate way to deal with this. I guess that's what ORM mapping is all about.

I'm using NHibernate mapping-by-code but an XML mapping example will also do.

Koen
  • 3,626
  • 1
  • 34
  • 55
  • Does the existing Machine (table) have a FK to Product? Like Machine.ParentProductSurrogateKey ? ( I would think if it exists it would be nullable).... – granadaCoder May 07 '14 at 13:31
  • No. If that would be the case I could use a simple many-to-one. The problem is I have no clearance to add that relationship in the database. – Koen May 07 '14 at 14:07
  • Ok, I understand better now. You're trying to add a relationship that doesn't exist, but without modifying the original tables. I don't have an answer, but let me look for something I ran across one time. – granadaCoder May 07 '14 at 14:23

2 Answers2

0

Here is my educated guess.

As you suggest, under normal M:N circumstances, MachineProduct would be a "link table" and repeats could be possible.

I think you can "singularize" the relationships...use the ".Unique()".

I think this is barking up the right tree.

public class MachineProductMap : ClassMap<MachineProductNHEntity>
{
    public MachineProductMap()
    {
        Schema("dbo");
        Table("MachineProduct");

        /* Your surrogate key setup here */
        Id(x => x.MachineProductUUID).GeneratedBy.GuidComb().Index("IX_MachineProduct_MachineProductUUID");

        References<MachineNHEntity>(x => x.ParentMachine)
            .Class(typeof(MachineNHEntity))
            .Not.Nullable()
             .Unique()
            .Column("ParentMachineUUID")
            .Index("IX_MachineProduct_ParentMachineUUID")
            .Cascade.SaveUpdate()
            ;
        ;


       References<ProductNHEntity>(x => x.ParentProduct)
            .Class(typeof(ProductNHEntity))
             .Nullable()
             .Unique()
            .Column("ParentProductUUID")
            .Index("IX_MachineProduct_ParentProductUUID")
            .Cascade.SaveUpdate()
            ;
        ;
    }
}

APPEND:

I think the above works, but you later state you don't want a middle-man entity.

So here is a another attempt using "Join".....

I think you want something like this:

public class Product
{
    public virtual int ProductSurrogateKey { get; set; } /* ID */
}   

public partial class Machine
    {
        public virtual Guid? MachineUUID { get; set; }
        public virtual Product TheProduct { get; set; }
    }

public class MachineMap : ClassMap<Machine>
{
    public MachineMap()
    {

        Table("Machine");

        Id(x => x.MachineUUID).GeneratedBy.GuidComb().Index("IX_Machine_MachineUUID");

        Join("MachineProductArtificialReferenceMaker", join =>
        {
            join.KeyColumn("ParentMachineUUID");
            join.References(prop => prop.TheProduct);
        });

    }
}

This creates a table called "MachineProductArtificialReferenceMaker".

This table has a FK to Machine.MachineUUID. (not null)

And a FK to Product.ProductSurrogateKey (nullable)

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • This is not what I had in mind. Mapping the join table to an entity seems overkill since it doesn't add any other value. – Koen May 08 '14 at 14:07
  • 1
    I added a second answer. It's "out there", but I think it works. – granadaCoder May 12 '14 at 21:05
  • Thanks but this only works partially. It will create and update the MachineProduct record correctly but it will not delete that record when I set Machine.Product to null. The NHibernate Join concept is clearly not designed to cope with my use case. – Koen May 13 '14 at 08:55
  • That's all I got dude. Good luck. Given the options, I'd just live with a benign row in the new table if it were me. – granadaCoder May 13 '14 at 13:36
0

The easiest way i presume regular Many to Many should do the work, make sure dont reference the many to many relationship from Product.

<class name="Machine">
  <id ....>
  <bag name="Competencies" table="MachineProduct">
    <key column="machine_id"/>
   <many-to-many class="Product" column="product_id"/>
  </bag>
</class>

Or regular One To Many works to.

<set name="MachineProduct" table="MachineProduct" cascade="all-delete-orphan" inverse="true">
  <key column="machine_id"/>
  <one-to-many class="MachineProduct"/>
</set>
Najera
  • 2,869
  • 3
  • 28
  • 52
  • The first solution is sort of what I have now but I feel there should be some better way since I don't actually need a collection. But as said in the question, I can add some code that manages the usage of the collection and expose it publicly as a regular association. – Koen May 08 '14 at 14:10
  • Im not sure what you need... what about one to one relationship? – Najera May 08 '14 at 15:43
  • Logically it is a many-to-one. Usually you can do this with just a foreign key in the database, but since we're not allowed to change the schema that contains the table Machine I'm using a join table (as with many-to-many relationships). What I'm looking for is an NHibernate mechanism that allows for a many-to-one mapping via a join table... – Koen May 09 '14 at 07:09