The content here is to familariase you with how you can filter your query to get results based on where clause and conditions

1. Simple String Criteria (StartsWith)
The query makes use of the StartsWith method which inturn generates a LIKE operator in the underlying query.
            using ( EntityManager manager = new EntityManager() )
            {
                manager.OpenDatabaseSession();

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

                var names = new From( typeof( Publisher ) ).As( t1 ).Where( t1.Pubname ).StartsWith( "s" )
                                    .SelectNew( ( ) => t1.Pubname ).Execute( );
}

//Generated Sql
SELECT  [t0].[pub_name] AS [R0] FROM  [publishers] [t0]  WHERE  [t0].[pub_name]  LIKE  @pub_namet0_0_4fdb2 

2. Simple Criteria on DateTime Property ( Month , Year , Day )

                var t9 = manager.AsAlias<Employee>();
                var names = new From( typeof( Employee ) ).As( t9 )
                                    .Where( t9.Hiredate ).Year( m => m.GreaterThan( 1980 ) )
                                    .SelectNew( ( ) =>
                                        new
                                        {
                                            HireDate = t9.Hiredate ,
                                            FirstName = t9.Fname ,
                                        } ).Execute( );

//Generated Sql
SELECT

[t0].[hire_date] AS [R0],[t0].[fname] AS [R1] 
FROM  [employee] [t0] 

WHERE  DATEPART(YEAR, [t0].[hire_date])  >  @hire_datet0_0_dfb5a (1980)



3. Complex Path Expression (Makes use of 't9.Publisher.Country')
            using ( EntityManager manager = new EntityManager() )
            {
                manager.OpenDatabaseSession();

                var t9 = manager.AsAlias<Employee>();
                var countries = new From( typeof( Employee ) ).As( t9 )
                                .Where( t9.Publisher.Country ).Contains( "o" )
                                .Where( t9.Hiredate ).Day( m => m.GreaterThan( 2 ) )
                                .SelectNew( ( ) => 
                                                    t9.Publisher.Country 
                                            ).Execute( );
           }

//Generated Sql
SELECT  [t1].[country] AS [R0] FROM  [employee] [t2]  , 
                ( SELECT  [t0].[country] AS [country],[t0].[pub_id] AS [pub_id] 
                               FROM  [publishers] [t0]  
                   WHERE  [t0].[country]  LIKE  @countryt0_0_6c592 
                ) t1  

WHERE  DATEPART(DAY, [t2].[hire_date])  >  @hire_datet2_0_1589e   AND  ( [t2].[pub_id]  =  [t1].[pub_id] )

Last edited Feb 3, 2010 at 4:40 PM by ahmedsalako, version 4

Comments

No comments yet.