Reference an entity with a formula in Fluent NHibe

Question

I have a schema with an N:1 parent-child relationship that is stored in another table and is selected by a formula. Is it possible to map this entity to the parent using a formula?

public class ParentEntity {
    public virtual int ParentId { get; set; }
    public virtual ChildEntity Child{ get; set; }
}


public class ParentMapping : ClassMap<ParentEntity> {
    public ParentMapping() {
        Table("ParentTable");

        Id(x => x.ParentId).Column("ParentId").GeneratedBy.Assigned().Not.Nullable();
        References<ChildEntity>(x => x.Child).Formula(
            @"(
                SELECT TOP 1 ChildTable.ChildId
                FROM ChildTable
                WHERE ChildTable.ParentId = ParentId
            )"
        );
    }
}

The SQL that this mapping generates looks like this:

SELECT
    this_.ParentId,
    this_.ChildEntity_id
FROM ParentTable this_ 

This is not what I'm looking for.

How can I reference this child entity and use, instead of ChildId in the parent table, a formula that selects ChildId from a formula?

Answer 1

I won't anyhow discuss the correctness of this approach, just try to answer. What you are trying to do: should work. I've checked the correctness of the formula in a test scenario. So, yes formula could be used exactly this way.

But because it is not working, I would a bit guess. Let's start with SQL generated in my test case, which is working.

SELECT this_.ParentId as ParentId3_0_
, (SELECT TOP 1 Child.ChildId
     FROM Child
     WHERE Child.ParentId = this_.ParentId) as formula1_0_ 
FROM Parent this_

Possible issues

I see two possible issues

1. Different Child ID column names

First of all in your snippet:

References<ChildEntity>(x => x.Child).Formula(
            @"(
                SELECT TOP 1 ChildTable.ChildId
                FROM ChildTable
                WHERE ChildTable.ParentId = ParentId
            )"

is column name of child primary key: ChildId while in SQL snippet is the ChildEntity_id:

SELECT
    this_.ParentId,
    this_.ChildEntity_id
FROM ParentTable this_ 

2. SQL Snippet does not match

Secondly, you mentioned that the (SQL Statement just above) is what was generated. But it is more like a statement of this mapping:

References<ChildEntity>(x => x.Child).Column("ChildEntity_id")

So couldn't be there some older/other mapping, which is in fact used?

SUMMARY I wanted to say, that this way of mapping is working. So you are on the correct track, but the devil is hidden in details ;)