Fluent NHibernate mapping via middle table

Question

I have a scenario that requires a mapping that needs to be done with joins to 2 tables via a middle table (see example) currently it is achieved via a formula which translates to a subquery, but i would like to use joins to do this more efficiently. please help

  Map(x => x.PropertyX).Formula(@"
               (SELECT C.ColumnX
                FROM   TableA A 
                       JOIN TableB B
                         ON A.Id = B.Id 
                       JOIN TableC C
                         ON C.Id = B.Id 
                WHERE  A.ColumnY = 898)");

Answer 1

Solution you've used could be convertied into standard ORM use case. The native or object/entity oriented solution here would be to introduce many-to-one and one-to-many mappings. That would bring lot of benefits (lazy loading == only if needed, querying...)

Small note, I am expecting that the FORMULA snippet in the question is just an example, because A.Id = B.Id = C.Id would mean B is not needed...

So we should introduce entities:

public class A
{
    public virtual B B { get; set; }
}
public class B
{
    public virtual IList<A> As { get; set; }  // not needed
    public virtual C C { get; set; }
}
public class C
{
    public virtual IList<B> Bs { get; set; }  // not needed
    public virtual string ColumnY { get; set; }
}

and their mapping:

public AMap()
{
    References(x => x.B);
}
public BMap()
{
    References(x => x.C);
    HasMany(x => x.As);   // not needed
}
public CMap()
{
    HasMany(x => x.Bs);   // not needed
    Map(x => x.ColumnY);
}

Now we can get the same result with standard query with few JOINs.

In case, that the

• we would like to reduce that chain and
• we know that the FORMULA content is always readonly...

we can create a virtual entity - mapped to a view:

public class A
{
    public virtual BView BView { get; set; }
}
public class BView
{
    public virtual string ColumnY { get; set; }
}

public AMap()
{
    References(x => x.BView)
}
public BMap()
{
    Table("viewName");
}

Where viewName would represent a view on a DB side. In case, we cannot introduce view, we can define it as an inlined SELECT

public BMap()
{
    Subselect(@"
        SELECT B.col1 as COL1,
               C.col2 as COL2,
               ..
        FROM B
         LEFT JOIN C
          ON B.CId = C.Id
        ");
}