Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
How to: Using Complex Retrieval Queries
Programmer's Guide > OpenAccess ORM Classic (Old API) > OpenAccess Tasks > Querying a Data Model > How to: Using Complex Retrieval Queries

Glossary Item Box

This documentation article is a legacy resource describing the functionality of the deprecated OpenAccess Classic only. The contemporary documentation of Telerik OpenAccess ORM is available here.

As important as storing data, is the ability to retrieve exactly what you are looking for, from the stored data at a later time. The Telerik OpenAccess ORM API provides several techniques for retrieving the data stored in a database. These include direct navigation on the object graph, and queries. One of the key concepts in an object-oriented application is the use of object navigation. That is, following the references from one object to another, however, you need to have to have at least one object to navigate from. This example looks at the various mechanisms available to you for finding and retrieving your objects.

Queries can be used to find specific objects based on their data values. OpenAccess ORM supports OQL, the Object Query Language defined by the ODMG, for use in your .NET applications.

Main Function

The following topic explains advanced object retrieval with queries:

C# Copy Code
static void Main(string[] args)
{
   IObjectScope scope = Database.Get(connectionId).GetObjectScope();
   Utilities.FillDB.Fill(scope);
   scope.Transaction.Begin();
   IObjectId peterMiller = PerformQueries(scope);

   scope.Transaction.Rollback();
   Console.WriteLine(
"\n\n Press <Return> to continue ...");
   Console.ReadLine();
   scope.Dispose();
}
VB .NET Copy Code
Shared Sub Main(args As String())
 Dim scope As IObjectScope = Database.Get(connectionId).GetObjectScope()
 Utilities.FillDB.Fill(scope)
 scope.Transaction.Begin()
 Dim peterMiller As IObjectId = PerformQueries(scope)

 scope.Transaction.Rollback()
 Console.WriteLine(vbLf & vbLf & " Press <Return> to continue ...")
 Console.ReadLine()
 scope.Dispose()
End Sub

This example explains in detail how to work with queries. A query result can have a sort order that is specified in the query select string (e.g., ORDER BY).

C# Copy Code
IObjectId peterMiller = PerformQueries(scope);
VB .NET Copy Code
Dim peterMiller As IObjectId = PerformQueries(scope)

A detailed description of OQL is contained in the Telerik OpenAccess ORM OQL Reference.

Query Examples

To demonstrate the usage of OQL for retrieval of data is the main goal this task. OQL queries are used to retrieve objects, in the following different ways:

  • Simple Conditions
     
  • Combined Conditions
     
  • Usage of parameters
  • Navigation
     
  • Set members
     
  • Projection
     
  • Counting
     
  • Query with DateTime as String

 

C# Copy Code
public static IObjectId PerformQueries(IObjectScope scope)
{  
   
//queries for employees named 'Miller'   
   
QueryForMillers(scope);  
   
//queries for all employees named 'Peter Miller'   
   
//demonstrates combined conditions and usage of parameters   
   
IObjectId peterMiller = QueryWithCombinedConditionAndParameters(scope);   
   
//demonstrates usage of object id parameters   
   
QueryForReferencedObjectIdWithParameter(scope, peterMiller);  
   
//find all employees that have a boss named 'Miller'   
   
//demonstrates navigation in query expressions   
   
QueryWithNavigation(scope);   
   
//queries for companies that have at least one address in London   
   
//demonstrates queries on collection fields   
   
QueryWithSetMember(scope);   
   
//projects to the first names of all employees named 'Miller'   
   
QueryWithProjection(scope);   
   
//counts all employees named 'Miller'   
   
CountQuery(scope);   
   
//displays employee with birthdate "1975-12-10"   
   
//here the datetime is passed as a string in the query   
   
QueryWithDateTimeAsString(scope);   
   
return peterMiller;
}
VB .NET Copy Code
Public Shared Function PerformQueries(scope As IObjectScope) As IObjectId
 'queries for employees named 'Miller'
 QueryForMillers(scope)
 'queries for all employees named 'Peter Miller'
 'demonstrates combined conditions and usage of parameters
 Dim peterMiller As IObjectId = QueryWithCombinedConditionAndParameters(scope)
 'demonstrates usage of object id parameters
 QueryForReferencedObjectIdWithParameter(scope, peterMiller)
 'find all employees that have a boss named 'Miller'
 'demonstrates navigation in query expressions
 QueryWithNavigation(scope)
 'queries for companies that have at least one address in London
 'demonstrates queries on collection fields
 QueryWithSetMember(scope)
 'projects to the first names of all employees named 'Miller'
 QueryWithProjection(scope)
 'counts all employees named 'Miller'
 CountQuery(scope)
 'displays employee with birthdate "1975-12-10"
 'here the datetime is passed as a string in the query
 QueryWithDateTimeAsString(scope)
 Return peterMiller
End Function

Query all employees where lastName is Miller:

C# Copy Code
QueryForMillers(scope);

 

VB .NET Copy Code
QueryForMillers(scope)

 

C# Copy Code
private static void QueryForMillers(IObjectScope scope)
{
   Console.WriteLine(
"\n### All employees named Miller ###");
   
   
string queryString = "select emp from EmployeeExtent AS emp " +
   
"where emp.lastName = \"Miller\" ";
    
   Query<Employee> oqlQuery = scope.GetOqlQuery<Employee>(queryString);
    
   QueryResultList<Employee> result = oqlQuery.ExecuteList();
    
   Console.WriteLine(
"Found " + result.Count + " Employees");
    
   
foreach (Employee e in result)
   {
       Console.WriteLine(e);
   }
    
   
if (result.Count > 0)
   {
       Employee first = (Employee)result[0];
   }
}  
VB .NET Copy Code
Private Shared Sub QueryForMillers(scope As IObjectScope)
 Console.WriteLine(vbLf & "### All employees named Miller ###")
 Dim queryString As String = "select emp from EmployeeExtent AS emp " + "where emp.lastName = ""Miller"" "
 Dim oqlQuery As Query(Of Employee) = scope.GetOqlQuery(Of Employee)(queryString)
 Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList()
 Console.WriteLine("Found " + result.Count + " Employees")
 For Each e As Employee In result
  Console.WriteLine(e)
 Next
 If result.Count > 0 Then
  Dim first As Employee = DirectCast(result(0), Employee)
 End If
End Sub

WHERE condition:

C# Copy Code
 string queryString = "select emp from EmployeeExtent AS emp " +
     
"where emp.lastName = \"Miller\" ";
VB .NET Copy Code
Dim queryString As String = "select emp from EmployeeExtent AS emp " + "where emp.lastName = ""Miller"" "

The above query is performed against the EmployeeExtent and it retrieves all the objects whose class field name lastName equals Miller.

You have to use the field name of the class lastName, and not the property name LastName.

The WHERE clause is optional. When a WHERE clause is not specified, the complete extent is returned. The wildcard character (*) could have been used to represents zero, one, or more characters in the search string. Wildcard characters are used in string literals. When using wildcards, you must use the LIKE operator rather than (=). All employees whose first character is M would be expressed like this: name LIKE "M*".

Query expressions with runtime parameters:

 

 

C# Copy Code
IObjectId peterMiller = QueryWithCombinedConditionAndParameters(scope);

 

VB .NET Copy Code
Dim peterMiller As IObjectId = QueryWithCombinedConditionAndParameters(scope)
C# Copy Code
private static IObjectId QueryWithCombinedConditionAndParameters(IObjectScope scope)
{
   Console.WriteLine(
"\n### All employees named Peter Miller ###");
   
   
string queryString = "select emp from EmployeeExtent AS emp " +
        
"where emp.lastName = $1 and emp.firstName = $2";
   
   Query<Employee> oqlQuery = scope.GetOqlQuery<Employee>(queryString);
   
   QueryResultList<Employee> result = oqlQuery.ExecuteList(
"Miller", "Peter");
   
   Console.WriteLine(
"Found " + result.Count + " Employees");
   
   
foreach (Employee e in result)
   {
       Console.WriteLine(e);
   }
   
   IObjectId retVal =
null;
   
   
if (result.Count > 0)
   {
       retVal = scope.GetObjectId(result[0]);
   }     
    
   
return retVal;
}
VB .NET Copy Code
Private Shared Function QueryWithCombinedConditionAndParameters(scope As IObjectScope) As IObjectId
 Console.WriteLine(vbLf & "### All employees named Peter Miller ###")
 Dim queryString As String = "select emp from EmployeeExtent AS emp " + "where emp.lastName = $1 and emp.firstName = $2"
 Dim oqlQuery As Query(Of Employee) = scope.GetOqlQuery(Of Employee)(queryString)
 Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList("Miller", "Peter")
 Console.WriteLine("Found " + result.Count + " Employees")
 For Each e As Employee In result
  Console.WriteLine(e)
 Next
 Dim retVal As IObjectId = Nothing
 If result.Count > 0 Then
  retVal = scope.GetObjectId(result(0))
 End If
 Return retVal
End Function

The code fragment appearing above combines query expressions with runtime parameters.

Query expression tokens:

C# Copy Code
"where emp.lastName = $1 and emp.firstName = $2";
VB .NET Copy Code
"where emp.lastName = $1 and emp.firstName = $2"

While performing queries, it is often desirable to be able to use a pre-defined query expression and pass a parameter that, for example, specifies just a value. A query for the Employee instances with a specified lastname, as in the previous example (QueryForMillers), could be pre-defined except for the actual name value. The same query expression can then be used for any name by passing the name value as a parameter to the query expression. OQL provides this capability using the tokens $1, $2, etc, to identify parameters in the OQL query string. The parameter values are passed to the Execute() method following the query expression in appropriate order and the values are substituted for the tokens when the query expression is parsed. The first parameter is bound to $1, the second to $2, and so forth.

C# Copy Code
QueryResultList<Employee> result = oqlQuery.ExecuteList("Miller", "Peter");
VB .NET Copy Code
Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList("Miller", "Peter")

The query returns all Employee objects with the name Peter Miller. The first parameter following the query expression, Miller is bound to $1 and is parsed as the value for the lastName equality, the second parameter Peter is bound to $2 and is parsed as the value for the firstName equality.

Query conditions with object identifier as query token expression

 

C# Copy Code
QueryForReferencedObjectIdWithParameter(scope, peterMiller);

 

VB .NET Copy Code
QueryForReferencedObjectIdWithParameter(scope, peterMiller)
C# Copy Code
private static void QueryForReferencedObjectIdWithParameter(IObjectScope scope, IObjectId bossId)
{
   Console.WriteLine(
"\n### All employees of Peter Miller ###");
   
   
string queryString = "select emp from EmployeeExtent AS emp " +
        
"where emp.boss = $1";
   
   Query<Employee> oqlQuery = scope.GetOqlQuery<Employee>(queryString);
   
   QueryResultList<Employee> result = oqlQuery.ExecuteList(bossId);
   
   
foreach (Employee e in result)
   {
       Console.WriteLine(e);
   }
}
VB .NET Copy Code
Private Shared Sub QueryForReferencedObjectIdWithParameter(scope As IObjectScope, bossId As IObjectId)
 Console.WriteLine(vbLf & "### All employees of Peter Miller ###")
 Dim queryString As String = "select emp from EmployeeExtent AS emp " + "where emp.boss = $1;"
 Dim oqlQuery As Query(Of Employee) = scope.GetOqlQuery(Of Employee)(queryString)
 Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList(bossId)
 For Each e As Employee In result
  Console.WriteLine(e)
 Next
End Sub

The above code fragment combines query expressions with runtime IObjectId instances as query expression tokens.

C# Copy Code
"where emp.boss = $1"
VB .NET Copy Code
"where emp.boss = $1"

For queries on references to persistent classes, the object id of the referenced object is used in the query. This query condition contains an identity comparison where emp.boss equals the object identifier from bossId. If an object with that object identifier exists in the database then the object is returned otherwise no object is returned. The object identifier of bossId is passed as a parameter.

The concept of query token expressions was explained earlier in the QueryWithCombinedConditionAndParameters method.

C# Copy Code
QueryResultList<Employee> result = oqlQuery.ExecuteList(bossId);
VB .NET Copy Code
Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList(bossId)

The query is executed with the object identifier bossId as parameter. During execution of the query the placeholder token $1 is replaced by the object identifier of bossId.

Path Expression

 

C# Copy Code
QueryWithNavigation(scope);

 

VB .NET Copy Code
QueryWithNavigation(scope)
C# Copy Code
private static void QueryWithNavigation(IObjectScope scope)
{
   Console.WriteLine(
"\n### All subordinates of Miller ###");
   
   
string queryString = "select emp from EmployeeExtent AS emp " +
       
"where emp.boss.lastName = \"Miller\" ";
   
   Query<Employee> oqlQuery = scope.GetOqlQuery<Employee>(queryString);
   
   QueryResultList<Employee> result = oqlQuery.ExecuteList();
   
   
foreach (Employee e in result)
   {
       Console.WriteLine(e);
   }
}
VB .NET Copy Code
Private Shared Sub QueryWithNavigation(scope As IObjectScope)
 Console.WriteLine(vbLf & "### All subordinates of Miller ###")
 Dim queryString As String = "select emp from EmployeeExtent AS emp " + "where emp.boss.lastName = ""Miller"" "
 Dim oqlQuery As Query(Of Employee) = scope.GetOqlQuery(Of Employee)(queryString)
 Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList()
 For Each e As Employee In result
  Console.WriteLine(e)
 Next
End Sub

The above code fragment is for a query with navigation in the query expression.

C# Copy Code
"where emp.boss.lastName = \"Miller\" ";
VB .NET Copy Code
"where emp.boss.lastName = ""Miller"" "

This line of code shows how the values of referenced objects can be used in the query expression. The query returns all Employee objects whose boss field references the Employee object with the lastName Miller. The path expression can reference a field in another class or struct or even reference, object fields in collections (see next example QueryWithSetMember). In order to achieve a better query performance, the path expression should be indexed.

Path Expression on fields whose objects are in a collection

 

C# Copy Code
QueryWithSetMember(scope);

 

VB .NET Copy Code
QueryWithSetMember(scope)
C# Copy Code
private static void QueryWithSetMember(IObjectScope scope)
{
   Console.WriteLine(
"\n### All companies in London ###");
   
   
string queryString = "select comp from CompanyExtent AS comp, comp.addresses AS addrs " +
       
"where addrs.city = \"London\" ";
   
   Query<Company> oqlQuery = scope.GetOqlQuery<Comapny>(queryString);
   
   QueryResultList<Company> result = oqlQuery.ExecuteList();
   
   
foreach (Company c in result)
   {
       Console.WriteLine(c);
   }
}
VB .NET Copy Code
Private Shared Sub QueryWithSetMember(scope As IObjectScope)
 Console.WriteLine(vbLf & "### All companies in London ###")
 Dim queryString As String = "select comp from CompanyExtent AS comp, comp.addresses AS addrs " + "where addrs.city = ""London"" "
 Dim oqlQuery As Query(Of Company) = scope.GetOqlQuery(Of Comapny)(queryString)
 Dim result As QueryResultList(Of Company) = oqlQuery.ExecuteList()
 For Each c As Company In result
  Console.WriteLine(c)
 Next
End Sub

The above code fragment demonstrates navigation in query expressions on collections.

C# Copy Code
"where addrs.city = \"London\" ";
VB .NET Copy Code
"where addrs.city = \"London\" "

This codeline demonstrates a query based on the value of a field in the element objects of a collection. The Company class has an ArrayList (generic collection) field, addresses. The ArrayList (generic collection) contains Address elements. The query returns all Company instances having an address in London.

Query conditions where an IObjectId instance is looked up in a collection is not allowed.

Query with field projection

 

C# Copy Code
QueryWithProjection(scope);

 

VB .NET Copy Code
QueryWithProjection(scope)
C# Copy Code
private static void QueryWithProjection(IObjectScope scope)
{
   Console.WriteLine(
"\n### First names of all employees named Miller ###");
   
string queryString = "select emp.firstName from EmployeeExtent AS emp " +
        
"where emp.lastName = \"Miller\" ";
   
   Query<
string> oqlQuery = scope.GetOqlQuery<string>(queryString);
   
   QueryResultList<
string> result = oqlQuery.ExecuteList();
   
   
foreach (string firstName in result)
   {
       Console.WriteLine(firstName);
   }
}
VB .NET Copy Code
Private Shared Sub QueryWithProjection(scope As IObjectScope)
 Console.WriteLine(vbLf & "### First names of all employees named Miller ###")
 Dim queryString As String = "select emp.firstName from EmployeeExtent AS emp " + "where emp.lastName = ""Miller"" "
 Dim oqlQuery As Query(Of String) = scope.GetOqlQuery(Of String)(queryString)
 Dim result As QueryResultList(Of String) = oqlQuery.ExecuteList()
 For Each firstName As String In result
  Console.WriteLine(firstName)
 Next
End Sub

The above code fragment demonstrates a query with field (firstName) projection.

C# Copy Code
string queryString = "select emp.firstName from EmployeeExtent AS emp " +
        
"where emp.lastName = \"Miller\" ";  
VB .NET Copy Code
Dim queryString As String = "select emp.firstName from EmployeeExtent AS emp " +
"where emp.lastName = ""Miller"" "

This query string explains the use of projections. In this case, the query result does not contain instances of the candidate extent (i.e. EmployeeExtent), but a projection of one of its fields. In this example, the first names (values of the firstName field) of the Employee instances with the last name Miller (value of the lastName field) are returned, i.e., the result will contain only the firstNames, not the entire employee objects.

Counting Condition

 

C# Copy Code
CountQuery(scope);

 

VB .NET Copy Code
CountQuery(scope)
C# Copy Code
private static void CountQuery(IObjectScope scope)
{
   Console.WriteLine(
"\n### Count of all employees named Miller ###");
   
   
string queryString = "count( select emp from EmployeeExtent AS emp " +
       
"where emp.lastName = \"Miller\" )";
   
   Query<
int> oqlQuery = scope.GetOqlQuery<int>(queryString);
   
   QueryResultList<
int> result = oqlQuery.ExecuteList();
   
   
int count = result[0];
   
   Console.WriteLine(count);
}
VB .NET Copy Code
Private Shared Sub CountQuery(scope As IObjectScope)
 Console.WriteLine(vbLf & "### Count of all employees named Miller ###")
 Dim queryString As String = "count( select emp from EmployeeExtent AS emp " + "where emp.lastName = ""Miller"" )"
 Dim oqlQuery As Query(Of Integer) = scope.GetOqlQuery(Of Integer)(queryString)
 Dim result As QueryResultList(Of Integer) = oqlQuery.ExecuteList()
 Dim count As Integer = result(0)
 Console.WriteLine(count)
End Sub

The above code fragment provides you with the count of objects that match selection criteria.

Count statement:

C# Copy Code
"count( select emp from EmployeeExtent AS emp " + .... )"
VB .NET Copy Code
"count( select emp from EmployeeExtent AS emp " + .... )"

The COUNT statement can be used as a query condition to the WHERE clause of a SELECT statement. A query condition based on a count is often called a counting condition.

C# Copy Code
int count = result[0];
VB .NET Copy Code
Dim count As Integer = result(0)

In this case, the query result does not contain instances of the candidate extent, rather, an integer instance representing the count. The count is the number of matching instances in the extent. In the example, the number of Employee instances with the last name Miller is returned.

DateTime as String in Query

 

C# Copy Code
QueryWithDateTimeAsString(scope);

 

VB .NET Copy Code
QueryWithDateTimeAsString(scope)
C# Copy Code
private static void QueryWithDateTimeAsString(IObjectScope scope)
{
   Console.WriteLine(
"\n### Displaying employee with birthday \"1975, 12, 10\" ###");
   
   
string queryString = "select emp from EmployeeExtent AS emp WHERE " +
         
"emp.birthDate == timestamp '1975-12-10 00:00:00'";
   
   Query<Employee> oqlQuery = scope.GetOqlQuery<Employee>(queryString);
   
   QueryResultList<Employee> result = oqlQuery.ExecuteList();
   
   
foreach (Employee empl in result)
   {
         Console.WriteLine(empl.FirstName);
   }
}
VB .NET Copy Code
Private Shared Sub QueryWithDateTimeAsString(scope As IObjectScope)
 Console.WriteLine(vbLf & "### Displaying employee with birthday ""1975, 12, 10"" ###")
 Dim queryString As String = "select emp from EmployeeExtent AS emp WHERE " + "emp.birthDate == timestamp '1975-12-10 00:00:00'"
 Dim oqlQuery As Query(Of Employee) = scope.GetOqlQuery(Of Employee)(queryString)
 Dim result As QueryResultList(Of Employee) = oqlQuery.ExecuteList()
 For Each empl As Employee In result
  Console.WriteLine(empl.FirstName)
 Next
End Sub

The above code fragment demonstrates the usage of specifying DateTime as a string in a query.

Specifying DateTime as a String:

C# Copy Code
"emp.birthDate == timestamp '1975-12-10 00:00:00'";
VB .NET Copy Code
"emp.birthDate == timestamp '1975-12-10 00:00:00'"

This will return all the employee instances whose DateTime corresponds to the DateTime given in the query string, i.e., all the employee instances whose birthdate bears the timestamp of "1975-12-10 00:00:00".

"Timestamps" is a keyword in OQL that defines a literal with date and time (this corresponds to the DateTime type in C#) and the string in single quotes has to follow the ISO date/time format (which specifies 4 digits for the year, 2 digits each for the month and day, followed by 2 digits each for the hour, minute and second respectively). Hence, any query using timestamps has to be written as shown above.