CODEWITHSUNDEEP
c#sqlnhibernate
Suja Shyam
Suja Shyam

Reputation: 971

NHibernate Query for a Sql Query

Let me explain my scenario. I have a table HSPartList which has partName, description, issueNo etc. When search is to made, I have a criteria which should list all the parts with latest issueNo. ie. If the table data is as follows:

|PartName| PartDesc| IssueNo|

TestPart1  TestPart1  1
TestPart1  TestPart1  2
TestPart2  TestPart2  1
TestPart2  TestPart2  2

In this example when I search for latest issues It should return only 

|PartName| PartDesc| IssueNo|

TestPart1  TestPart1  2
TestPart2  TestPart2  2

I have sql query for that. I need to convert that to a nHibernate query.

Sql query is:

select * from HS_PartList HS where HS.IsLive = 1 and HS.PartName Like '%test%' and IssueNo=(select max(issueNo) from HS_PartList where HS_PartList.PartName=HS.PartName)

NHibernate version is 2.1.2.4000

Upvotes: 1

Views: 332

Answers (2)

Sreenath Plakkat
Sreenath Plakkat

Reputation: 1785

  Using Hql it Works fine 
string SearchTerm = "TestPart2";
string search = "and (partdetail.PartName like '%" + SearchTerm + "%') ";
var hql = string.Format(@"select partdetail  from HSPartList as partdetail  where  IssueNo =(select Max(IssueNo)  from HSPartList)") + search;
var context = _session.CreateQuery(hql).List<HSPartList>();

Upvotes: 2

Firo
Firo

Reputation: 30813

var subquery = DetachedCriteria.For<Part>()
    .Add(Restrictions.EqProperty("Name", "part.Name"))  <-- part is the alias of the main query
    .SetProjection(Projections.Max("IssueNo"));

var results = session.CreateCriteria<Part>("part")
    .Add(Restrictions.Eq("IsLive", true))
    .Add(Restrictions.Like("Name", teststring, MatchMode.Anywhere))
    .Add(Subqueries.Eq("IssueNo", subquery))
    .List();

Upvotes: 2

Related Questions