0

I want to join 2 tables. They do not have foreign keys, but they have a column which have the same content. I cant change the schema and here is a simplified example:

public class Person{
  public virtual string Name{get;set;}
  public virtual int Id{get;set;}
  public virtual char Gender{get;set;} #this one I want to connect
}

public class PersonMapping : ClassMapping<Person>{
  public PersonMapping()
  {
     Id(x=>x.Id);
     Property(x=>x.Name);
     Property(x=>x.Gender);
  }
}

public class Gender{
  public virtual char ShortName{get;set;} #to this. So they are the same. When They are connected I want to access the Other properties of this class
  public virtual int Id{get;set;}
  public virtual string LongName{get;set;} 
}
public class GenderMapping: ClassMapping<Gender>{
  public GenderMapping()
  {
     Id(x=>x.Id);
     Property(x=>x.ShortName);
     Property(x=>x.LongName);
  }
}

Now I want to have all persons with the corresponding Gender. So that I can find the long name. I think the solution is something like this but it is for fluent nhibernate. I would also be fine to do that with criterias or similar.

Community
  • 1
  • 1
Iron
  • 477
  • 4
  • 19

1 Answers1

0

You are almost there. But the Property Gender must be mapped as so called many-to-one and we have to use a feature called property-ref. See:

The property-ref attribute should only be used for mapping legacy data where a foreign key refers to a unique key of the associated table other than the primary key. This is an ugly relational model. For example, suppose the Product class had a unique serial number, that is not the primary key. (The unique attribute controls NHibernate's DDL generation with the SchemaExport tool.)

<property name="serialNumber" unique="true" type="string" column="SERIAL_NUMBER"/>

Then the mapping for OrderItem might use:

<many-to-one name="product" property-ref="serialNumber" column="PRODUCT_SERIAL_NUMBER"/>

Firstly, we have to introduce the Reference instead of char:

public class Person
{
  public virtual string Name{get;set;}
  public virtual int Id{get;set;}
  //public virtual char Gender{get;set;} #this one I want to connect
  public virtual Gender Gedner { get; set; }
}

So the mapping should be like this:

public class PersonMapping : ClassMapping<Person>
{
  public PersonMapping()
  {
     Id(x=>x.Id);
     Property(x=>x.Name);
     //Property(x=>x.Gender);
     ManyToOne(x => x.Gender, x =>
        {
            x.PropertyRef("ShortName"); // this is the property of a Gender class!
            x.Column("Gender");    // to be mapped with the Pesons's column Gender
        });
  }
}

There is a very nice description of mapping by code:

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I think that this is what I am searching for, but I cant get it to work. He could not find the referenced entity 'System.String' on .... Column(Code). I don't know what to put in for the Code. So when I want as example the whole Gender Object for that reference, I do that how? – Iron Jul 15 '14 at 12:32
  • Wow sorry, I guess it should be **ShortName** – Radim Köhler Jul 15 '14 at 12:34
  • Other words, it should be the name of property (other then its ID) which contains the same value as the column "Gender" (if there is gender column...). what is the name of column in Person table containing the char Gender? is it "Gender"? – Radim Köhler Jul 15 '14 at 12:41
  • yes I understand that. 'person.gender' is the same like 'Gender.shortname'. But that didnt work for me if I comment the GenderProperty out. So I cant get a mapping from the person to the corresponding gender.longname – Iron Jul 15 '14 at 12:45
  • could you explicitly show me the column names of both tables? we will manage to make it running ;) – Radim Köhler Jul 15 '14 at 12:46
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57326/discussion-between-iron-and-radim-kohler). – Iron Jul 15 '14 at 12:48