As most of you probably know, OQL was the main object-oriented query language that was supported by Telerik OpenAcess ORM before Linq was introduced in .NET. Even though Linq is now the de facto standard for querying data, support for OQL is still there and I would state that it still is pretty convenient to use in some scenarios.
We will use this post to share some tips on querying data using the Object Query Language.
Consider the following query:
IQueryResult projection = scope.GetOqlQuery("SELECT pr.ProductName, cat.CategoryName "
+ "FROM CategoryExtent AS cat,cat.Products AS pr").Execute();
This query makes a projection of all product names and their respective categories. The ‘Products’ and ‘Categories’ tables are queried in order to fetch the required result set. They are having a 1:n relation.(The Northwind database is used).
As any query language, there are various ways to express a query that returns a certain result set. The same query, can be written in the following form as well:
IQueryResult sameProjection = scope.GetOqlQuery("SELECT pr.productName, pr.category.categoryName "
+ "FROM ProductExtent AS pr").Execute();
These two queries show that in cases of 1:n relations data from both tables can be fetched in two ways. The difference is the Extent that is used as an entry point. It can be the one from the ‘1’ side of the 1:n relation(‘CategoryExtent’) or the one from the ‘n’ side of the relation(‘ProductExtent’).
There is a slight difference though. Let’s look at the SQL that is generated from the first query:
SELECT b.[ProductName] AS COL1, a.[CategoryName] AS COL2 FROM [Categories] a JOIN [Products] AS b ON (a.[CategoryID] = b.[CategoryID])
When using the ‘1’ side of the relation as the Extent in our OQL query, the SQL generated contains a simple ’JOIN’ to connect the two tables(it is treated as INNER JOIN by most servers). This means that we select those Products that actually belong to some Category.
If one wants to select the product alongside their categories and include the products that do not belong to any category, then the second approach should be taken. The entry point should be the ‘ProductExtent’ so Telerik OpenAccess ORM generates the SQL with a LEFT JOIN clause.
SELECT a.[ProductName] AS COL1, b.[CategoryName] AS COL2 FROM [Products] a LEFT JOIN [Categories] AS b ON (a.[CategoryID] = b.[CategoryID])
It can be really helpful for people that want to have more control over the data they are presenting. It certainly helps understanding OQL and 1:n references with Telerik OpenAccess ORM.