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.

Reference

What am i doing wrong?

"This means that cable 1 connects node 2000 and 2001." Cable 1? Or Cable 100? — Donnelle
Ah, The id should have been incremented. Meaning that Cable 100 connectes 2000 and 2001 — Per Arne Andersen
I would say, that now you sub-select id as P1 ... and also use KeyColumn("id")... this will work like: NHibernate will take the id from current entity Node and pass it into the subselect to matc the id as P1 column. So, if this is not what you want, just change the subselect... or change the KeyColumn... hope it helps. From info here, it is very hard for me to understand the real purpose of your subselect. But as I described in my answer: you need to create such subselect.. which is like if there is a real table. One of its column should serve as a foreign key to root table — Radim Köhler
The problem is that i could do do SELECT * from table in the Subquery and get the same results as when i did SELECT * FROM table WHERE 1 = 2. It seems to ignore the SubQuery (Having my current HasMany settings) — Per Arne Andersen

Radim Köhler Radim Köhler · Accepted Answer · 2015-03-13T07:10:11

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(....

NHibernate Formula - Mapping list of entities

1 Answers

Mapping-by-Code - Set and Bag