Use Fluent nHibernate to join two tables with different column names

Question

I am using fluent nhibernate 3.1 to map to a legacy database. I have two classes and would like to join B to A. The database structure does not have foreign keys.

AModel.OccurrenceNumber and BModel.OccurrenceNumber have same data(i.e. claim # 1234 in one and the other), just different column names. Is it possible to join during the mapping stage? If need be I can write a linq statement to join them, but would like to know if it can be done here. Thanks in advance.

public class AMap : ClassMap<AModel>
{
    public AMap()
    {
        Table("ATable");

        Id(x => x.Id).GeneratedBy.Increment();
        Map(x => x.OccurrenceNumber).Column("OCCUR"); //Same Data
    }
}

public class BMap : ClassMap<BModel>
{
    public BMap()
    {
        Table("BTable");

        Id(x => x.Id).GeneratedBy.Increment();
        Map(x => x.OccurrenceNumber).Column("B69_CLAIM_OCCUR"); // Same Data
    }
}

is this column unique in any of the tables and you want to use it as a foreign key? — mykola, Aug 01 '12 at 19:02
Yes, the data held in these columns is unique. I have tried setting up as a foreign key but without success( more than likely an ID10T error). — MetRay, Aug 02 '12 at 12:17
As of this moment, I only see the need for a one-to-one. I have in the past set up foreign key relationships, however they had a common column name, and maybe I am over thinking this(not hard for me to do). — MetRay, Aug 02 '12 at 12:39
you should be abe to use References or HasOne mapping and specify column name in the other class. Your A and B classes should have member properties referencing each other. Then you'll be able to do in class A smth like References(x=>x.B).Class(typeof(B)).Fetch.Join().Column("B69_CLAIM_OCCUR"); or HasOne(x=>x.B).Constrained().ForeignKey("B69_CLAIM_OCCUR"); — mykola, Aug 02 '12 at 12:53
@mykola, thank you for your help! I try a few tests to see if it works. — MetRay, Aug 02 '12 at 13:05

score 0 · Answer 1 · answered Aug 29 '13 at 17:12

You can declare following reference in your AMap or BMap class

References<AModel>(x => x.AModels).Column("B69_CLAIM_OCCUR").PropertyRef("OccurrenceNumber").ReadOnly();

Where AModeldata data has been defined in your BModel class as

public virtual IList<AModel> AModels{ get; set; }

So when you write queries joining these two tables it will be automatically joined on B69_CLAIM_OCCUR and OCCUR.

I have assumed you have One to Many relationship here betwen A and B entities.

Use Fluent nHibernate to join two tables with different column names

1 Answers1