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

score 0 · Accepted Answer · answered Nov 17 '15 at 06:20

0

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));

answered Nov 17 '15 at 06:20

Radim Köhler

122,561
47
239
335

Thanks Radim, the solution works for the simple problem i have posted, but the real problem is more complex than i have wrote. I was trying to adapt this to my situation but i'm not getting there. imagine that in (CAST(colA_1 AS VARCHAR(10)) + CAST(colA_2 AS VARCHAR(10))) i don't want colA_2 but i want colC_2 from table C. This is because i have a composite key in EntityA as you defined in your solution – Pedro Tainha Nov 17 '15 at 10:30
This is too complex. Such scenarios exceed ability of the ORM tools. It is not designed to solve NON standard keys. Even from DB perspective, such queries are the most uneffective. I wish to be able to give you genral solution with NHibernate here... but the only way is to fix the DB design. Seriously. You can do it now or later, but my experiences says: no other way... sorry. Good luck with NHibernate anyhow ;) – Radim Köhler Nov 17 '15 at 12:02
Thanks again Radim, i managed to use formula parameter to achive what i want, example here [http://stackoverflow.com/questions/13114619/using-a-property-mapping-with-a-formula-in-nhibernate](http://stackoverflow.com/questions/13114619/using-a-property-mapping-with-a-formula-in-nhibernate) but the drawback is the mixing of pure sql that will break the advantage of integrity between the c# model and DB. – Pedro Tainha Nov 17 '15 at 14:06

projection property in subquery nhibernate criteria

1 Answers1