Query Joins

RQL supports various join flavors that conform to Object oriented Query language standards The followings are the types of supported Joins.
  1. Collection Join ( a.IEnumerables )
  2. Implicit Join : Related Property Join ( a.Teacher.Name )
  3. Explicit Join : Un-related entity join

Sometimes when Correlated sub queries are not enough to do criteria checks on a collection property in a query. We would want to drill down the entities in the collection and perform a criteria query on each elements of the collection. RQL saves you the headache of writing several queries to achieve that fit. Here are the following types Joins which you can perform while using RQL :

1. Collection Property Join
This comes into effect if you want to perform criteria on elements of a collection. using the RQL In method, you will be able to syntactically join a collection in the query:

var t9 = manager.AsAlias<Employee>( );
var t1 = manager.AsAlias<Publisher>();

var cities = new From( typeof( Publisher ) ).As( t1 )
                    .In( t1.Employees ).As( t9 )
                    .Where( t9.Fname ).StartsWith( "a" )
                    .SelectNew( ( ) => t1 ).Execute( );

//Generated Sql
SELECT  
       [t2].[pub_name],
       [t2].[city],
       [t2].[state],
       [t2].[country],
       [t2].[pub_id] 
FROM  [publishers] [t2]  , 
          ( SELECT  [t0].[pub_id] AS [pub_id] 
                    FROM  [employee] [t0]  
            WHERE  [t0].[fname]  LIKE  @fnamet0_0_44e24 
          ) t1  

WHERE  [t2].[pub_id]  =  [t1].[pub_id] 


2. Implicit Join
Implicit join are syntatic sugar which is used when you want more readability of you query. You can use Path Expression e.g ( a.Teacher.City ) in favour of the implicit join. Here is an example of the implicit join syntax.
var t9 = manager.AsAlias<Employee>( );
var t1 = manager.AsAlias<Publisher>();

var cities = new From( typeof( Employee ) ).As( t9 )
                   .Join( t9.Publisher ).As( t1 )
                   .SelectNew( ( ) => t1.City ).Execute( );

//You can re-write the above query with the one below. 
//This will yield the same sql statements

 var cities = new From( typeof( Employee ) ).As( t9 )
                   .SelectNew( ( ) => t9.Publisher.City ).Execute( );

//Generated Sql
SELECT  [t1].[city] AS [R0] 
        FROM  [employee] [t2]  , 
        ( SELECT  [t0].[city] AS [city],
                  [t0].[pub_id] AS [pub_id] 
          FROM  [publishers] [t0] 
        ) t1  

WHERE  [t2].[pub_id]  =  [t1].[pub_id]

3. Explicit Join : Un-related entity join
Sometimes we want to perform queries on unrelated entities. Using a specific join properties from both sides, rapid allows you to do just that, when you use the following query operators:
var e1 = manager.AsAlias<Employee>( );
var t0 = manager.AsAlias<Title>( );

var rqlt = new From( typeof(Employee)).As( e1 )
                    .Join( typeof( Title ) , t0 ).On( t0.Titleid ).EqualsTo( e1.Empid )
                    .Where( e1.Fname ).StartsWith( "a" )
                    .Or( e1.Fname ).Contains( "i" )
                    .SelectNew( ( ) => new { e1.Fname , t0.Price } ).Distinct( );

//Generated Sql
SELECT 
           DISTINCT [t2].[fname] AS [R0],
           [t1].[price] AS [R1] 
FROM  [employee] [t2]  , 
                  ( SELECT  
                             [t0].[title_id] AS [title_id],
                             [t0].[price] AS [price] 
                   FROM  [titles] [t0] 
                 ) t1  
WHERE  [t2].[fname]  LIKE  @fnamet2_0_b24df   
AND  ( [t2].[emp_id]  =  [t1].[title_id] )   
OR  ( [t2].[fname]  LIKE  @fnamet2_1_3e2b9 ) 


Last edited Feb 4, 2010 at 9:36 AM by ahmedsalako, version 7

Comments

No comments yet.