Even in SQL, the latter is far more difficult than the former. Given the 'or':
Code:
select p.* from Parents p join Children c on (c.ParentID = p.ID)
where (c.key = 'key1' and c.value = 'value1') or (c.key = 'key2' and c.value = 'value2')
one can see that just replacing the 'or' with an 'and' will always result an empty relation: any given Child can only have one or the other key-value. So, you have to join Children twice, or use an 'in' which I think would be far more efficient:
Code:
select p.* from Parents p
where p.ID in ( select ParentID from Children c where (c.key = 'key1' and c.value = 'value1') )
and p.ID in ( select ParentID from Children c where (c.key = 'key2' and c.value = 'value2') )
as criteria:
Code:
ICriteria criteria = session.CreateCriteria( typeof( Parent ) );
DetachedCriteria child1 = (new DetachedCriteria( typeof( Child ) ).Add( Expression.And( Expression.Eq( "key", "key1" ), Expression.Eq( "value", "value1" ) ) ).Add( Projections.Property( "ParentID" ) );
DetachedCriteria child2 = (new DetachedCriteria( typeof( Child ) ).Add( Expression.And( Expression.Eq( "key", "key2" ), Expression.Eq( "value", "value2" ) ) ).Add( Projections.Property( "ParentID" ) );
criteria.Add( Expression.And( Subqueries.PropertyIn( "ID", child1), Subqueries.PropertyIn( "ID", child2 ) ) );
Getting the ParentID off the children as a property projection might be more complicated than that, like
Code:
.CreateAlias( "Parent", "p" ).Add( Projections.Property( "p.ID" ) );
but there's something to get you started.
Login
FAQ
Search
