CODEWITHSUNDEEP
c#sql.netnhibernate
Sike12
Sike12

Reputation: 1262

Converting SQL Query to NHibernate

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

Upvotes: 1

Views: 2076

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123901

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.

Upvotes: 1

Related Questions