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 stackoverflow.com/a/5420791/1486443 — Najera
– Najera, Commented Oct 28, 2016 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
– Radim Köhler, Commented Oct 28, 2016 at 15:36
@RadimKöhler Its a self join. So i am not sure how is that not possible? — Sike12
– Sike12, Commented Oct 28, 2016 at 15:40

Community · Accepted Answer · 2017-05-23 11:48:44Z

1

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.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 28, 2016 at 15:35

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sike12 Over a year ago

Thanks @Radim Kohler

Collectives™ on Stack Overflow

Converting SQL Query to NHibernate

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related