NHibernate Formula - Mapping list of entities

Question

I want to create a formula in Fluent NHibernate which has a Subquery which fetches a list of results.

I have a table called node_cable with following columns: |id|node|cable|

The node column describes a node while cable describes the connection between these nodes.

Example:

|id|node|cable|

|1|2000|100|

|2|2001|100|

This means that cable 100 connects node 2000 and 2001.

The following query gives me the desired result:

SELECT * 
FROM node_cable as AA
WHERE AA.node != 3565
AND AA.cable IN
(
    SELECT * FROM
    (
        SELECT BB.cable
        FROM node_cable as BB
        WHERE BB.node = 3565
    ) AS subquery
)

How would i write this in Fluent NHibernate? ive tried to use Map.Formula, but no luck:

Map(x => x.siblings).Formula(@"SELECT AA.node
                        FROM node_cable as AA
                        WHERE AA.node != 3565
                        AND AA.cable IN
                        (
                            SELECT * FROM
                            (
                                SELECT BB.cable
                                FROM node_cable as BB
                                WHERE BB.node = 3565
                            ) AS subquery
                        )");

The sibling propery is defined as:

public virtual IList<NodeEntity> siblings { get; set; }

The error i'm getting is:

{"Could not determine type for: System.Collections.Generic.IList`1[[GOTHAM.Model.NodeEntity, GOTHAM-MODEL, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, for columns: NHibernate.Mapping.Formula( SELECT AA.node\r\n FROM node_cable as AA\r\n WHERE AA.node != 3565\r\n AND AA.cable IN\r\n (\r\n SELECT * FROM\r\n (\r\n SELECT BB.cable\r\n FROM node_cable as BB\r\n WHERE BB.node = 3565\r\n ) AS subquery\r\n ) )"}

Anyone have any idea?

Edit: Picture of query result: http://gyazo.com/0d3152314631500ece81e6c0b2efe3d5

Thanks!

Edit:

Now i have the following Mapping to siblings:

      HasMany(x => x.siblings)
    .Not.LazyLoad() 
    .AsBag()
    .Fetch.Join()
    .Inverse()
    .Table("node_cable")
    .KeyColumn("id")
    .Subselect(
    @"SELECT
    id AS P1,
    node AS P2,
    cable AS P3 
    FROM node_cable as AA
    WHERE AA.node != 3565
    AND AA.cable IN
    (
        SELECT * FROM
        (
            SELECT BB.cable
            FROM node_cable as BB
            WHERE BB.node = 3565
        ) AS subquery
    )"
  );

This however does only return the same node i'm at. I can also just remove the subquery giving the same result.

What am i doing wrong?

Answer 1

Check this overview (second half of the document) how to do collection mapping:

Mapping-by-Code - Set and Bag

I would say, that there is some missunderstanding.

Firstly, .Formula() is replacement of the standard .Column(). The idea behind is, that we can compute some value (rate * amount)... It cannot be used for collection mapping. It is here just to return column

Secondly we try to map collection:

public virtual IList<NodeEntity> siblings { get; set; }

While mapping .Map() is just for value types. So we would need different mapping .HasMany() or even .HasManyToMany() if tehre is a pairing table

// this way we mapp collections
HasMany(x => x.siblings)
   ...

And also, I am not sure about the inner select, the subquery. Usually we map the entity and then for HasMany, table is defined by the entity mapping.

If the relation is one-to-many, we need that our collection item NodeEntity is mapped "standard way" - to be used as reference.

If we need a special select... we can use different approach:

7.2. Collections of dependent objects

<set name="SomeNames" table="some_names" lazy="true">
    <key column="id"/>
    <composite-element class="Eg.Name, Eg"> <!-- class attribute required -->
        <property name="Initial"/>
        <property name="First"/>
        <property name="Last"/>
    </composite-element>
</set>

In this case, we do mapp the element (e.g. NodeEntity) property by property to some select, in the above example table="some_names". But NHibernate goes even further...

We can replace table, with in-lined select (we are coming back to Formula mentioned in the question)

The mighty setting is: <subselect>

<set name="siblings" lazy="true">

    <subselect>
    SELECT AA.node
    property1 AS P1,
    property2 AS P2,
    property3 AS P3,
    FROM node_cable as AA
    WHERE AA.node != 3565
    AND AA.cable IN
    (
        SELECT * FROM
        (
            SELECT BB.cable
            FROM node_cable as BB
            WHERE BB.node = 3565
        ) AS subquery
    )
    </subselect>

    <key column="node"/>
    <composite-element class="NodeEntity">
        ...
    </composite-element>
</set>

Great, and that all could be converted even into fluent:

HasMany(x => x.siblings)
    .Subselect(....