How write hibernate criteria for specific SQL?

Question

i need write similar SQL by ICriteria:

Tables:

1Lvl -- 2Lvl -- 3Lvl

SQL:

SELECT * FROM 2Lvl
WHERE 2Lvl.1LvlFK in
(
    SELECT 1Lvl.Id

    FROM 3Lvl 

    JOIN 2Lvl ON 3Lvl.2LvlFK = 2Lvl.Id 

    JOIN 1Lvl ON 2Lvl.1LvlFK = 1Lvl.Id  

    WHERE 3Lvl.Id = 123
)

I'm sorry for so specific question, but I inherited project with Hibernate from exemployee and I can't still understand hibernate-criteria.

Answer 1

var subQuery = DetachedCriteria.For<Lvl3>("lvl3")
     .CreateAlias("Lvl2", "sublvl2", JoinType.InnerJoin)
     .CreateAlias("Lvl1", "lvl1", JoinType.InnerJoin)
     .Add(Restrictions.EqProperty("sublvl2.Id", "lvl2.Id")
     .Add(Restrictions.Eq("lvl3.Id", 123)
     .SetProjection(Projections.Property("lvl1.Id"));

Session.CreateCriteria<Lvl2>("lvl2")
     .Add(Subqueries.PropertyIn("Lvl1.Id", subQuery));

Should do the trick. I've made assumptions about what your entities are called since 1Lvl, etc.. are not valid C# identifiers. I've also assumed the primary key column of each table was Id. Also a note that this won't produce the exact SQL you're looking for, but it will get you the same result.