Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
OQL Queries in OpenAccess
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Query a Data Model > Queries > OQL Queries in OpenAccess

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.

The IObjectScope implementation provides a method, GetOqlQuery(string expression), for setting up a query. You simply pass the OQL query expression you want performed to the GetOqlQuery() method and it will return an instance of IQuery. The IQuery interface provides the Execute() method for performing queries in your application. The Execute() method returns an IQueryResult instance, which contains the objects whose data matches the criteria specified in the query expression.

If there are not enough index trees defined in a given schema, queries (including count queries that have no query condition) can take a long time to complete. In such cases it is recommended to define an additional index to aid the query evaluation. The index should be defined on the smallest fixed size field with the highest selectivity.

Querying Fields of Simple Types

The following example shows an OQL query to find the instances of Employee with a given value ("King" in the example) for the lastName field:

C# Copy Code
IObjectScope scope =ObjectScopeProvider1.GetNewObjectScope();            string queryString = "Select emp from EmployeeExtent as emp where emp.lastName = 'King'";            IQuery query = scope.GetOqlQuery(queryString);            IQueryResult result = query.Execute();            foreach (Employee emp in result)            {                Console.WriteLine(emp.LastName);            }            result.Dispose();
VB.NET Copy Code
Dim scope As IObjectScope = ObjectScopeProvider1.GetNewObjectScope()
Dim queryString As String = "Select emp from EmployeeExtent as emp where emp.lastName = 'King'"
Dim query As IQuery = scope.GetOqlQuery(queryString)
Dim result As IQueryResult = query.Execute()
For Each emp As Employee In result
 Console.WriteLine(emp.LastName)
Next
result.Dispose()

The OQL syntax is described in the Telerik OpenAccess ORM OQL Reference.

The Query Result

The IQueryResult interface extends IList and IEnumerable. The IQueryResult.GetEnumerator() method returns an instance of IMovableEnumerator, which can be used to enumerate the query result. Using the IList interface, you can also directly access the elements of the query result.

C# Copy Code
if ( result.Count > 0 )
{
  Employee first = (Employee) result[ 0 ];
}
VB.NET Copy Code
If result.Count > 0 Then
 Dim first As Employee = DirectCast(result(0), Employee)
End If

Depending on the number of objects in your database and the query criteria, your query result may contain a large number of objects.

It is important to release the in-memory resources used by the query result. When you have finished any operations using the query result, be sure to call the IQueryResult method Dispose() to free the used resources.

Querying Fields of Referenced Objects

The following example shows how the values of referenced objects can be used in the query expression. The query returns all Employee objects whose boss field references an Employee object with the lastName "Fuller".

C# Copy Code
string queryString = "select emp from EmployeeExtent AS emp where emp.Employee1.lastName = \"Fuller\"";            IQuery oqlQuery = scope.GetOqlQuery(queryString);            IQueryResult result = oqlQuery.Execute();// . . .
result.Dispose();
VB.NET Copy Code
Dim queryString As String = "select emp from EmployeeExtent AS emp where emp.Employee1.lastName = ""Fuller"""
Dim oqlQuery As IQuery = scope.GetOqlQuery(queryString)
Dim result As IQueryResult = oqlQuery.Execute()
result.Dispose()

The OQL syntax is described in the Telerik OpenAccess ORM OQL Reference.

Query Parameters

When 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 last name, as in the example above, 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 with the query expression. OQL provides this capability by using tokens such as $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 then 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.

Using Query Parameters

The following example demonstrates the use of query expression parameters. The query returns all Employee objects with the name "Peter Miller":

C# Copy Code
string queryString = "select emp from EmployeeExtent AS emp where emp.Employee1.lastName = $1 and emp.Employee1.FirstName =$2";            IQuery oqlQuery = scope.GetOqlQuery(queryString);            IQueryResult result = oqlQuery.Execute("Fuller","Andrew");

// . . .

result.Dispose();  
Example Title Copy Code
Dim queryString As String = "select emp from EmployeeExtent AS emp where emp.Employee1.lastName = $1 and emp.Employee1.FirstName =$2"
Dim oqlQuery As IQuery = scope.GetOqlQuery(queryString)
Dim result As IQueryResult = oqlQuery.Execute("Fuller", "Andrew")
' . . .
result.Dispose()

The first parameter, "Fuller", is bound to $1 and is parsed as the value for the lastName equality, the second parameter "Andrew" is bound to $2 and is parsed as the value for the firstName equality.

Querying Fields of Persistence Capable Class Types

The following example explains how query conditions for fields of persistence capable class types are formulated. In this case, an IObjectId parameter with the identifier of the object to be matched is passed. The example query returns all the Employee instances whose boss field references the specified object:

C# Copy Code
IObjectId bossID = scope.GetObjectId((from c in scope.Extent<Employee>() where c.FirstName=="Andrew" select c).First());            string queryString = "select emp from EmployeeExtent AS emp where emp.Employee1 = $1";            IQuery oqlQuery = scope.GetOqlQuery(queryString);            IQueryResult result = oqlQuery.Execute(bossID);
VB.NET Copy Code
Dim bossID As IObjectId = scope.GetObjectId((From c In scope.Extent(Of Employee)() _
 Where c.FirstName = "Andrew" _
 Select c).First())
Dim queryString As String = "select emp from EmployeeExtent AS emp where emp.Employee1 = $1"
Dim oqlQuery As IQuery = scope.GetOqlQuery(queryString)
Dim result As IQueryResult = oqlQuery.Execute(bossID)
Queries with Projections

The following example explains the use of projections. In this case, the query result does not contain instances of the candidate class, but a projection to one of its fields. In the example, the first names (values of the firstName field) and last names of the Employee instances with the last name that starts with F (value of the lastName field) are returned:

C# Copy Code
using OpenAccess;
// . . .
string queryString = "SELECT emp.FirstName, emp.LastName from EmployeeExtent as emp where emp.LastName like 'F*'";            IQuery oqlQuery = scope.GetOqlQuery(queryString);            IQueryResult result = oqlQuery.Execute();
foreach (object[] emp in result)            {                Console.WriteLine(emp[0]+" "+emp[1]);            }

result.Dispose();
VB.NET Copy Code
Dim queryString As String = "SELECT emp.FirstName, emp.LastName from EmployeeExtent as emp where emp.LastName like 'F*'"
Dim oqlQuery As IQuery = scope.GetOqlQuery(queryString)
Dim result As IQueryResult = oqlQuery.Execute()
For Each emp As Object() In result
 Console.WriteLine(emp(0) + " " + emp(1))
Next
result.Dispose()
Count Queries

The following example demonstrates the use of count statements in OQL. In this case, the query result does not contain instances of the candidate extent, rather, a single 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:

C# Copy Code
using OpenAccess;
// . . .
string queryString = "count(select emp from EmployeeExtent as emp where emp.LastName='Fuller')";            IQuery oqlQuery = scope.GetOqlQuery(queryString);            IQueryResult result = oqlQuery.Execute();            int count = (int)result[0];            Console.WriteLine(count);            result.Dispose();
VB.NET Copy Code
Dim queryString As String = "count(select emp from EmployeeExtent as emp where emp.LastName='Fuller')"
Dim oqlQuery As IQuery = scope.GetOqlQuery(queryString)
Dim result As IQueryResult = oqlQuery.Execute()
Dim count As Integer = DirectCast(result(0), Integer)
Console.WriteLine(count)
result.Dispose()
Specifying DateTime as a String in a Query

The following example demonstrates the usage of DateTime as a String in a query. The example query returns all the Person instances whose DateTime corresponds to the DateTime given in the query string:

Copy Code
Select * from OrderExtent p where p.orderDate<timestamp '2005-10-15 13:10:15'

OQL has defined standards for any query which uses timestamps. Hence, any query using timestamps has to be written as shown above.

This means "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. The ISO date/time format 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, as shown in the example above.

Querying a Collection Type Field

The scenario for the example is explained below:

We have 3 classes: Product, SalesOrder and Buyer. Product has a many-to-many relationship with SalesOrder and the Buyer has a one-to-many relationship with SalesOrder. Product and SalesOrder both have a collection of SalesOrder and Product. SalesOrder has a reference to Buyer, and Buyer has a collection of SalesOrder.

Now, if we need to retrieve the Products having SalesOrder, where the Buyers are of certain age, the following query will be used:

Copy Code
Select * from EmployeeExtent AS p,  p.employeeTerritories AS o
WHERE o.Region.RegionDescription = "Eastern"

The "o" variable is used to traverse the Employee Territories collection.

Using OQL Queries with IN Operator

Suppose we have the following code:

Copy Code
[Persistent]
  class C
     {
        public string field1;
     }

[Persistent]
  class Ca : C
     {
       public string field1_a;
       public string field2_a;
       public string field3_a;
     }

[Persistent]
  class Cb : C
     {
       public string field1_b;
     }

[Persistent]
  class Cc : C
     {
       string field1_c;
     }

[Persistent]
  class Cd : C
     {
       int field1_d;
     }

Now, if we want to return all the instances of A or B, the following query can be used:

Copy Code
select * from CExtent as obj where (obj in CaExtent) or (obj IN CbExtent)

In the above case, both Ca and Cb are derived from C. This query will only match instances of Ca or Cb, since it tests additionally that the instance is only in the Ca or Cb extent; so not even pure C instances are matched.

Typecasting

In this case we have an abstract base class Pe, which is extended by the classes Cat and Dog. The abstract class Dog is then extended by the classes Rottweiler and wienerDog.

Use the following query to find wienerdog whose length is greater than 56:

Copy Code
SELECT * FROM DogExtent AS c WHERE ((wienerDog)c).length > 56
Null Comparision Expressions

A null comparison expression tests whether or not the single-valued path expression or input parameter is a NULL value.

This can be accomplished with a NIL query:

Copy Code
SELECT * FROM CompanyExtent AS p WHERE p.CompanyName = $1

where $1 is set to null.

Or with the symbolic constant NIL, when it is used in the query directly, as shown below:

Copy Code
SELECT * FROM CompanyExtent AS p WHERE p.CopmanyName = NIL
OQL Query on Collections

Suppose we have the following code:

Copy Code
[Persistent]
class Group
  {
     public string name;

     public IList<Element> elements;

     Group(string n)
     {
        name = n;
        elements = new List<Element>();
     }
  }

[Persistent]
class Element
  {
     public string name;

     public IList<Group> groups;

     Element(string n)
     {
        name = n;
        groups = new List<Group>();
     }
  }

static void SampleData()
{
     // Sample Data
     scope.Transaction.Begin();

     Group g1 = new Group("g1");
     g1.list.Add(new Element("e1"));

     scope.Add(l_table1);

     scope.Transaction.Commit();
  }

The following query "Q1" will return all Group instances, where the name of the elements of the referenced elements is "e1", or where an element references a group named "g1":

Copy Code
"SELECT * FROM GroupExtent AS g, g.list AS e
WHERE e.name=\"e1\" OR EXISTS x IN e.groups : (x.name=\"g1\"); 
<_innovasys3a_widgel type="Note Box" layout="block"><_innovasys3a_widgetproperty name="Content" layout="block">

The parenthesis at the end, states the scope of the x variable explicitly, and this might be necessary in cases where a more complex condition is required.

However, a chain of iterators (g,e,x) can also be defined like in "Q2":

<_innovasys3a_widgetproperty name="Title" layout="inline"><_innovasys3a_widgetproperty name="Content" layout="block">
"SELECT * FROM GroupExtent AS g, g.list AS e, e.groups AS x
WHERE e.name=\"e1\" OR x.name=\"g1\"; 
<_innovasys3a_widgetproperty name="LanguageName" layout="inline">XXFORMAT-->