projection property in subquery nhibernate criteria

Question

Here's the deal, I got 2 tables:

table (A) with columns ( colA_1  | colA_2 | colA_ID ) <br>
table (B) with columns ( colB )

and I'm using DetachedCriteria.For<AnyEntity>() to query SQL.

The purpose is get an sql senerated like this:

Select A.colA_ID from A 
where (CAST(colA_1 AS VARCHAR(10)) + CAST(colA_2 AS VARCHAR(10))) 
in (select colB from B)

Thanks in advance for any help

Answer 1

I would strongly recommend to create a special property on your A entity:

public class EntityA
{
    ...
    public virtual string ProjectedKey { get; set; } 
}

And map it as readonly with formula

<property name="ProjectedKey" 
          formula="(CAST(colA_1 AS VARCHAR(10)) + CAST(colA_2 AS VARCHAR(10)))" 
          insert="false" update="false" />

And now is easy to use subquery

// subquery to get colB (its id?) from table B
var detachedQuery = DetachedCriteria.For<EntityB>()    
     .SetProjection(Projections.Id()) // or property representing col B

// use subquery with property represented by formula
var rootQuery = session.CreateCriteria<EntityA>()
     .Add(Subqueries.PropertyIn("ProjectedKey", detachedQuery));