How do I use Fluent NHibernate to configure the following joins?

Question

This is based on a legacy system.

I have the following tables:

CREATE TABLE a
  id int

CREATE TABLE b
  a_id int,
  c_id int
  relationshipid int -- must be IN (1, 2, 3)

CREATE TABLE c
  id int

I want the following domain models

public class A
{
  public int Id { get; set; }

  public C entityc { get ; set; }
}

public class C
{
  public int Id { get; set; }
}

Table b is set up so that for a particular defined relationshipid there is (well, should only be) one pair of ids. For other relationships, that one to one mapping through B doesn't hold true. Relationshipid can be one of a small number of values.

How do I get entity C into class A from the relationship where the relationshipid is 1 using fluent NHIbernate?

As a side question, is there a name for what I am trying to do here? The original approach was trying use a HasOne with a Join table and Filter the results, but obviously that failed miserably.

EDIT: Clarified RelationshipID and purpose.

Do the tables A and C have the same primary key (Is A.Id = C.Id)? — Cole W, Apr 14 '11 at 15:58
No. I've just seen the annoying typo which changes everything though. b_id should have been c_id — Simon Gill, Apr 14 '11 at 16:12
I'm assuming that relationshipid is the primary key on table b? — Cole W, Apr 14 '11 at 17:02
Unfortunately not, there's no primary key on table b. Yes, this is definitely a legacy db. — Simon Gill, Apr 15 '11 at 08:58

score 0 · Accepted Answer · edited May 23 '17 at 11:47

I think the easiest way to map this would be to make your table b an entity and have references to both A and C within that entity and RelationshipId as the id. So your mappings would look something like this:

public class A
{
    public int Id { get; set; }

    public IList<B> bEntities { get; set; }
}

public class ClassAMap : ClassMap<A>
{
    public AMap()
    {
        Table("A");
        Id(x => x.Id);

        HasMany(x => x.bEntities)
            .KeyColumns.Add("a_id");
    }
}

public class B
{
    public virtual int RelationshipId { get; set; }
    public virtual A InstanceA { get; set; }
    public virtual C InstanceC { get; set; }
}

public class ClassBMap : ClassMap<B>
{
    public BMap()
    {
        Table("B");
        Id(x => x.RelationshipId , "relationshipid");

        References(x => x.InstanceA);
        References(x => x.InstanceC);
    }
}

Edit:

If your wanting to filter these results for the collection of B entities in your A entity to only ones matching RelationshipId = 1 then you should take a look at this post:

Fluent NHibernate and filtering one-to-many relationship on query requiring multiple joins?

You could also do something like this in your class A:

public class A
{
    public int Id { get; set; }

    public IList<B> bEntities { get; set; }

    public C InstanceC
    {
       get { return bEntities.First<B>(x => x.RelationshipId == 1).InstanceC; }
    }
}

Thanks for helping me learn how to ask questions properly Cole. Unfortunately the important thing is that I want a field "public C entityC" which picks up the reference to entityC where the relationshipid is a particular value. Using the extra join entity seems to be the only way to do it though. Show me how that's done and I'll accept your answer :) — Simon Gill, Apr 15 '11 at 09:10
I've edited my solution above to reflect what you were asking. — Cole W, Apr 15 '11 at 12:10

How do I use Fluent NHibernate to configure the following joins?

1 Answers1