Converting SQL Query to NHibernate

Question

I have a SQL Query and i am trying to convert it into Nhibernate query and run it.

SQL Query

SELECT 
    A.*
FROM 
    TestTable i
LEFT JOIN 
    TestTable o
ON 
    i.testColumn=o.testcolumn and i.testColumn1='TestColumn1'       
WHERE      o.StartDate <= '2016-10-28' and i.testColumn2 > 3

Nhibertnate Query

ObjectA is a C# object version of TestTable

 ObjectA o = null;
 ObjectA i = null;

 var query = Session.QueryOver(() => o)
            .Left.JoinQueryOver(() => i)
            .Where(() => o.testColumn == i.testColumn)
            .Where(() => i.testColumn1 == "TestColumn1")
            .Where(() => i.testColumn2 == 3
            .Where(() => o.StartDate <= '2016-10-28')
                      return query.Take(100).List();

Mappings

  public ObjectATableMap : ClassMap<ObjectA>
    {
        Schema("[Test]");
        Table("[TestTable]");

        Id(x  => x.Id, "Id").GeneratedBy.Native();
        Map(x => x.TestColumn1, "TestColumn1");
     Map(x => x.TestColumn2, "TestColumn2");
     Map(x => x.StartDate ,"StartDate");

       }

When i run the above query i get the following message "could not resolve property: i of: ObjectA" Could anyone please provide me with the right hibernate Query. Thanks

`JoinQueryOver` expects a expression with the property which has the relation. You can see an example here http://stackoverflow.com/a/5420791/1486443 — Najera, Oct 28 '16 at 15:29
@Sike12 I tried to explain the issue in the answer.. because I expected such mapping. This simply is NOT possible... join not related tables ... — Radim Köhler, Oct 28 '16 at 15:36
@RadimKöhler Its a self join. So i am not sure how is that not possible? — Sike12, Oct 28 '16 at 15:40
@Sike12, I will try it again: Because NHibernate is ORM (object relational mapper) it provides querying API on top of relations. If there is no relation in your mapping (e.g. Parent as my answer suggests) there is no way how to create a JOIN even if you will name it self join. So, Either create a relation and you can query it, or use HQL as I showed.. which supports CROSS JOIN and that could solve your issue... hope now it is clear — Radim Köhler, Oct 28 '16 at 15:43

score 1 · Accepted Answer · edited May 23 '17 at 11:48

An error:

"could not resolve property: i of: ObjectA"

is related to statement

Session.QueryOver(() => o)
        .Left.JoinQueryOver(() => i)

because it expects, that a class ObjectA (o) has reference to class ObjectA (i). Usually parent child

public class ObjectA
{
    public virtual ObjectA Parent { get; set; }
    ...
}

If there is no relation - we cannot use QueryOver API. We can use HQL

It is not about nice C# API as QueryOver has, but it would work.

Converting SQL Query to NHibernate

1 Answers1