title: NHibernate Formula - Mapping list of entities
body: <p>I want to create a formula in Fluent NHibernate which has a Subquery which fetches a list of results.</p>
  <p>I have a table called node_cable with following columns:
  |id|node|cable|</p>
  <p>The node column describes a <strong>node</strong> while cable describes the connection between these nodes.</p>
  <p>Example:</p>
  <blockquote>
  <p>|id|node|cable|</p>
  <p>|1|2000|100|</p>
  <p>|2|2001|100|</p>
  </blockquote>
  <p>This means that cable 100 connects node 2000 and 2001.</p>
  <p>The following query gives me the desired result:</p>
  <pre><code>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
  )
  </code></pre>
  <p>How would i write this in Fluent NHibernate? ive tried to use Map.Formula, but no luck:</p>
  <pre><code>Map(x =&gt; x.siblings).Formula(@&quot;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
                          )&quot;);
  </code></pre>
  <p>The sibling propery is defined as:</p>
  <pre><code>public virtual IList&lt;NodeEntity&gt; siblings { get; set; }
  </code></pre>
  <p>The error i'm getting is:</p>
  <blockquote>
  <p>{&quot;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                            ) )&quot;}</p>
  </blockquote>
  <p>Anyone have any idea?</p>
  <p>Edit:
  Picture of query result:
  <a href="http://gyazo.com/0d3152314631500ece81e6c0b2efe3d5" rel="nofollow noreferrer">http://gyazo.com/0d3152314631500ece81e6c0b2efe3d5</a></p>
  <p>Thanks!</p>
  <p>Edit:</p>
  <p>Now i have the following Mapping to siblings:</p>
  <pre><code>      HasMany(x =&gt; x.siblings)
      .Not.LazyLoad() 
      .AsBag()
      .Fetch.Join()
      .Inverse()
      .Table(&quot;node_cable&quot;)
      .KeyColumn(&quot;id&quot;)
      .Subselect(
      @&quot;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
      )&quot;
    );
  </code></pre>
  <p>This however does only return the same node i'm at. I can also just remove the subquery giving the same result.</p>
  <p><img src="https://i.stack.imgur.com/7N67c.png" alt="Reference" /></p>
  <p>What am i doing wrong?</p>