Correlated Subquery
Sub query in the where clause acts upon Collection properties. Collection properties are not exempted in the Rapid Query language syntax. You can issue basic aggregates on correlated subqueries. The following are samples for correlated subquery:

1. Check if count of collection entities is greater than a number
var t1 = manager.AsAlias<Publisher>();
var cities = new From( typeof( Publisher ) ).As( t1 )
                   .Where( t1.Employees ).Count( m => m.GreaterThan( 0 ) )
                   .SelectNew( ( ) => t1.City ).Execute( );

//Generated Sql
SELECT  [t0].[city] AS [R0] 
FROM  [publishers] [t0]  
WHERE  ( SELECT  COUNT([sub2].[pub_id]) AS [pub_id] 
                 FROM  [employee] [sub2]  
                 WHERE  [sub2].[pub_id]  =  [t0].[pub_id] 
       )  >  @opt0_0_216e8

2. Check if Max of collection entities is equals to a number
var t1 = manager.AsAlias<Publisher>();
var cities = new From( typeof( Publisher ) ).As( t1 )
                   .Where( t1.Employees ).Max( m => m.EqualsTo( 2 ) )
                   .SelectNew( ( ) => t1.City ).Execute( );

//Generated SQL
SELECT  
       [t0].[city] AS [R0] 
FROM  [publishers] [t0]  
WHERE  ( SELECT  MAX([sub2].[pub_id]) AS [pub_id] 
                 FROM  [employee] [sub2]  
         WHERE  [sub2].[pub_id]  =  [t0].[pub_id] 
        )  =  @opt0_0_57230 

Last edited Feb 3, 2010 at 9:13 PM by ahmedsalako, version 3

Comments

No comments yet.