Home → Help and guidance →

multiple composite Primary Keys

elabor

13 Oct, 2010 07:34 AM via web

Admin Functions

• Edit
• Remove

Hello,

I'm using the current fluent nhibernate trunk builded against current nHibernate 3 Beta trunk (for Linq support).

We have here a Database scheme which mostly has composite PKs, as the application is multi client/mandatable. So I've got most tables with the column "mandator", which is the id of the

current client.

Example tables:

table [Machines]
PK Mandator
PK Machine_id
Typ
Name

table [MachineTypes]
PK Mandator
PK Typ
Description

The Mappings I've so far:

public MachineMap(){

Table("Machines");

CompositeId()
        .KeyProperty(x => x.Mandator, "Mandator")
        .KeyProperty(x => x.ID, "Machine_id");


Reference<MachineType>(x => x.Typ)


Map(x => x.Name);

}

public MachineTypeMap() {

Table("MachineTypes");

        CompositeId()
            .KeyProperty(x => x.Mandator, "Mandator")
            .KeyProperty(x => x.Art, "Art");


        HasMany(x => x.Machines)
          .KeyColumn("Mandator")
          .KeyColumn("Art")
          .Inverse()
          .Cascade.All();

Map(x => x.Description);

}

How should I handle references <--> with other Composite PKs? Any idea how to thread my > 500 tables all with this additional Mandator PK? I'm very new in the nHibernate/fluent world so any help would be appreciated.

1. 2 Posted by nils on 13 Oct, 2010 09:35 AM

   

   Try using:
   

    Table("MachineTypes");
    CompositeId()
       .KeyProperty(x => x.Mandator, "Mandator")
       .KeyProperty(x => x.Art, "Art");
     HasMany(x => x.Machines)
         .Columns.Add("Mandator", "Art")
         .Inverse()
         .Cascade.All();
    Map(x => x.Description);

   Nils

   Admin Functions

   • Edit
   • Split
   • Remove

2. 3 Posted by elabor on 13 Oct, 2010 10:08 AM

   

   Hello Nils,

   Thanks for you answer. I've two problems with you solution:

   1. HasMany doesn't has a Column's collection. So I used instead Key(k => k.Columns
      {

           HasMany(x => x.Machines)
               .Key(k => k.Columns.Add("Mandant", "Art"))
               .Inverse()
               .Cascade.All();

      }

   So, my current error stays:

   Foreign key (FK68F6F4682326DCE4:Machines [Typ])) must have same number of columns as the referenced primary key (MachineTypes [Mandant, Art])

   Note: The MachineTypes.Art Cloumn is in the the table Machines called Machines.Typ. It's ugly but I can't change it.

   So, the real table looks like:

   table [Machines]
   

   PK Mandator
   PK Machine_id
   Typ
   Name

   table [MachineTypes]
   

   PK Mandator
   PK Art  //--> references with Machines.Typ
   Description

   cheers

   Admin Functions

   • Edit
   • Split
   • Remove

3. 4 Posted by nils on 14 Oct, 2010 06:53 AM

   

   I think with this setup you'd need to set PropertyRef in this scerario, but I'm not sure.
   PropertyRef should be available in FNH 1.1, see http://support.fluentnhibernate.org/discussions/help/154-hasmany-an...

   Admin Functions

   • Edit
   • Split
   • Remove

4. 5 Posted by elabor on 14 Oct, 2010 11:58 AM

   

   Where should I apply PropertyRef?

   On the HasMany side or on the Reference side? In my understanding, on the reference side I have the column "Typ" which is the foregin key to the MachineTypes table.

   They should be joned as follows:
   

   Machines.Mandator== MachineTypes.Mandator  (smallint)
   Machines.Typ == MachineTypes.Art  (varchar)

   On the MachinesMap I defined the ref mappiing:

   References(x => x.Typ)
       .Column("Typ")   // --> if I omit this, I get a Typ_id column not found exception!?
       .PropertyRef("Mandator")
       .PropertyRef("Typ")

   and the the MachineTypeMap
   

   HasMany(x => x.Machines)
       .key
       .PropertyRef("Mandator")  //<--- is not accepted, as it says: "Property 'Mandator' on MachineType not found!"
       .PropertyRef("Art")  //<--- is not accepted, as it says: "Property 'Art' on MachineType not found!"

   I really dont get it why it cant find the propertys :/

   Is there any example of how to handle a both side composite PK using it with a foregin key?

   Admin Functions

   • Edit
   • Split
   • Remove

5. 6 Posted by elabor on 14 Oct, 2010 12:01 PM

   

   I'm sorry the ".key" in the HasMany mapping was a typo. In fact, I tried a lot including omitting .Key(... but nothing worked.
   

   Admin Functions

   • Edit
   • Split
   • Remove

6. 7 Posted by nils on 15 Oct, 2010 07:43 AM

   

   My mistake. I think I encountered this once before (In one of my legacy databases actually. See (my question on StackOverflow)[http://stackoverflow.com/questions/3904100/self-reflexive-nm-relati...]) And.. I think this does not work using nhibernate (but I am unsure why - I don't think we're the only ones having a mandatory/company field in all tables...)

   Nils

   Admin Functions

   • Edit
   • Split
   • Remove

7. 8 Posted by elabor on 15 Oct, 2010 09:27 AM

   

   Yes it looks like we have a very similar problem. I could belief that fluent hibernate doesn't support this kind of mapping, but nHibernate should do it (hopefully). Otherwise I've a big problem :)

   If nHibernate is not able to do so, what other OR Mapper can do it? These M$ jerks decided to not support my SQL 2000 Server, so I can't use any OR Mapper provided in VS 2010.

   I'll ask in the nHibernate community and maybe end up in writing the xmls and can't use fluent. But at least I've a solid OR Mapping...

   However, thank you for your answers. I'll post any progress in this here.

   Admin Functions

   • Edit
   • Split
   • Remove

8. 9 Posted by nils on 19 Oct, 2010 02:40 PM

   

   elabor,
   I solved my problem. Maybe the following code can fix yours, too:

   MachineMap
   

   namespace Simple.mappings
   {
   using FluentNHibernate.Mapping;
   
   using Simple.dto;
   
       public sealed class MachineMap : ClassMap<Machine>
       {
           public MachineMap()
           {
               this.Table("Machines");
               CompositeId()
                   .KeyReference(m => m.Type, "Mandator", "Type")
                   .KeyProperty(m => m.MachineId, "Machine_id");
   
               Map(x => x.Name);
           }
       }
   }

   MachineTypeMap
   

   namespace Simple.mappings
   {
   using FluentNHibernate.Mapping;
   
   using Simple.dto;
   
       public sealed class MachineTypeMap : ClassMap<MachineType>
       {
           public MachineTypeMap()
           {
               this.Table("MachineTypes");
               CompositeId()
                   .KeyProperty(x => x.Mandator, "Mandator")
                   .KeyProperty(x => x.Type, "Type");
   
               HasMany(x => x.Machines)
                   .KeyColumns.Add("Mandator", "Type")
                   .Inverse()
                   .Cascade.AllDeleteOrphan()
                    .AsSet();
   
               Map(x => x.Description);
           }
       }
   }

   MachineType:
   

   namespace Simple.dto
   {
       using System.Collections.Generic;
   
       public class MachineType
       {
           public virtual int Mandator { get; set; }
   
           public virtual int Type { get; set; }
   
           public virtual string Description { get; set; }
   
           public virtual ICollection<Machine> Machines { get; set; }
   
           public virtual bool Equals(MachineType other)
           {
               if (ReferenceEquals(null, other))
               {
                   return false;
               }
               if (ReferenceEquals(this, other))
               {
                   return true;
               }
               return other.Mandator == this.Mandator && other.Type == this.Type;
           }
   
           public override bool Equals(object obj)
           {
               if (ReferenceEquals(null, obj))
               {
                   return false;
               }
               if (ReferenceEquals(this, obj))
               {
                   return true;
               }
               if (obj.GetType() != typeof(MachineType))
               {
                   return false;
               }
               return Equals((MachineType)obj);
           }
   
           public override int GetHashCode()
           {
               unchecked
               {
                   return (this.Mandator * 397) ^ this.Type;
               }
           }
       }
   }

   Machine:
   

   namespace Simple.dto
   {
       public class Machine
       {
           public virtual MachineType Type { get; set; }
   
           public virtual int MachineId { get; set; }
   
           public virtual string Name { get; set; }
   
           public virtual bool Equals(Machine other)
           {
               if (ReferenceEquals(null, other))
               {
                   return false;
               }
               if (ReferenceEquals(this, other))
               {
                   return true;
               }
               return Equals(other.Type, this.Type) && other.MachineId == this.MachineId;
           }
   
           public override bool Equals(object obj)
           {
               if (ReferenceEquals(null, obj))
               {
                   return false;
               }
               if (ReferenceEquals(this, obj))
               {
                   return true;
               }
               if (obj.GetType() != typeof(Machine))
               {
                   return false;
               }
               return Equals((Machine)obj);
           }
   
           public override int GetHashCode()
           {
               unchecked
               {
                   return ((this.Type != null ? this.Type.GetHashCode() : 0) * 397) ^ this.MachineId;
               }
           }
       }
   }

   Hope this helps,
   Nils

   Admin Functions

   • Edit
   • Split
   • Remove

9. 10 Posted by elabor on 20 Oct, 2010 05:50 AM

   

   Indeed, it works!

   Thank you very much Nils!

   They Key of this problem/solution is this line:
   

               .KeyReference(m => m.Type, "Mandator", "Type")

   right?

   This makes the type Property to a part from primary key, or I'm wrong?

   Again, thank you :)

   Admin Functions

   • Edit
   • Split
   • Remove

10. 11 Posted by elabor on 20 Oct, 2010 05:59 AM

    

    Hm, It looks like in MachineType the List is always empty.
    However, I get no errors and everything other works.
    

    Admin Functions

    • Edit
    • Split
    • Remove

11. 12 Posted by nils on 20 Oct, 2010 06:06 AM

    

    Yes, that makes the MachineType part of the Key of the Machine.
    Notice that you can no longer access Machine.Mandator, you'll have to access Machine.Type.Mandator.

    If the list is empty but you get no errors check your implementation of Equals (had the same problem - this was my solution...). When using composite keys (I gather) everything stands and falls with the Implementation of Equals. Make sure Equals test exactly the key-equality.

    Nils

    Admin Functions

    • Edit
    • Split
    • Remove