nHibernate join query

i have two tables (graph representation - one table is nodes, other is link between nodes), and i want to write this query in nHibernate:

SELECT 
  distinct(t.id),  t.NodeName, e.Fk_linkOne, e.Fk_linkTwo, e.RelationName
FROM Nodes t
INNER JOIN NodeRelation e ON t.Id=e.Fk_linkOne OR t.Id=e.Fk_linkTwo
where (e.Fk_linkOne =84 OR e.Fk_linkTwo=84 ) AND t.Id!=84

I did not find how to connect two tables wiht join, that have OR in it..

ICriteria criteriaSelect = Session
  .CreateCriteria(typeof(NodeRelation ), "nodeRelations")                  
  .CreateCriteria("n开发者_JAVA百科odeRelations.Node", "node",
       NHibernate.SqlCommand.JoinType.InnerJoin)

You can only define your joins as per the associations you have defined in your mappings. As far as I know you can't define OR style relationships in Nhibernate. Consider using a self referential style graph representation.

public class Node
{
    public IList<Node> Parents { get; set; }
    public IList<Node> Children { get; set; }
}

<bag name="Parents" table="Node_Relation">
   <key column="ChildId" />
   <many-to-many class="Node" column="ParentId" />
</bag>
<bag name="Children" table="Node_Relation">
   <key column="ParentId" />
   <many-to-many class="Node" column="ChildId" />
</bag>

You should be using DetachedCriteria to get the same done. I am not sure about your query but I will jsut give a shot.

var dc1= DetachedCriteria.For(typeof( NodeRelation )).Add(Restrictions.Eq("Fk_linkOne", 84))
.SetProjection(Projections.Property("Fk_linkOne"));

var dc2= DetachedCriteria.For(typeof( NodeRelation )).Add(Restrictions.Eq("Fk_linkTwo", 84))
.SetProjection(Projections.Property("Fk_linkTwo"));

Session.CreateCriteria(typeof(Nodes))
.Add(Subqueries.PropertyIn("Id", dc1))
.Add(Subqueries.PropertyIn("Id", dc2))
.Add(Restrictions.Eq("Id", 84)).List<Nodes>;

Hope the above query is corrrect. please let me know if you cant get it to work after obs trying smethings and let me know wat u tried.

It always depends on your classes and not so much on your tables. Remember, you are using a ORM and you are working with a class model.

Assumed that your classes look like this:

class Node
{
  List<Node> Relations { get; private set; }
  List<Node> InverseRelations { get; private set; }
}

You may map it like this:

<class name="Node">
<!-- .... -->
  <bag name="Relations" table="NodeRelation">
    <key name="Fk_linkOne">
    <many-to-many class="Node" column="Fk_linkTwo"/>
  </bag>
  <bag name="InverseRelations" table="NodeRelation" inverse="true">
    <key name="Fk_linkTwo">
    <many-to-many class="Node" column="Fk_linkOne"/>
  </bag>

</class>

This way you get asymetrical relations (this means: when Node A relates to Node B, B isn't necessarily related to A, except of the InverseRelation of course). I don't know what you actually need, so this is an assumption based on your database design.

Your query may look like this:

from Node n
where 
  :x in elements(n.Relations)
  or :x in elements(n.InverseRelations)

Note: x is an entity type, not just an id (you need to load it using session.Load<Node>(84)),

Another way for the samy query:

select distinct n
from Node n 
  inner join n.Relations e1 
  inner join n.InverseRelations e2
where e1.id = 84 or e2.id = 84

Or another way without the use of the inverse relations:

select n
from Node n, Node n2 inner join n.Relations e
where
  (n = n2 and e.id = 84)
  OR (n = e and n2.id = 84)

In criteria I would take the second solution and write it like this:

session.CreateCriteria<Node>("n")
  .SetProjection(Projections.Distinct("n"))
  .CreateCriteria("Relations", "e1")
  .CreateCriteria("InverseRelations", "e2")
  .Add(Expression.Or(
    Expression.Eq("e1.id", 84),
    Expression.Eq("e2.id", 84));