Support for special hierarchical queries

crazypit · Post by **crazypit** » Mon 08 Mar 2010 10:30

Hello,

I've been using the latest dotConnect for Oracle suite. Whenever i need to use the special Oracle hierarchical queries, i need to revert to plain old SQL syntax. Nevertheless, this syntax is not reusable in other databases. I do not know about the syntax of specific hierarchical queries in other databases like MySQL and MS SQL Server, but i guess there should be a similar support.

It would be great if i could use Devart's dotConnect for MySQL, MS SQL Server and Oracle, interchangeably, using the same LINQ syntax for my hierarchical queries through my application without the need to change code, or compile conditionally or maintain the DB type as parameter etc...

AndreyR · Post by **AndreyR** » Wed 10 Mar 2010 14:27

We will investigate the possibility to add hierarchical queries support to the LINQ to Oracle.
At the moment I recommend you to use the Query or ExecuteQuery methods to send the SQL queries directly to database.

crazypit · Post by **crazypit** » Thu 01 Jul 2010 15:48

Now that the LinqConnect product is out, how do you plan to support hierarchical queries in the same unified manner for all the supported database server types?

AndreyR · Post by **AndreyR** » Tue 06 Jul 2010 11:14

We plan to implement hierarchical queries support for DBMSs that offer such support. But we don't have any timeframe for this task.

crazypit · Post by **crazypit** » Mon 10 Jan 2011 14:38

Hello,

It's been half a year now and still nothing mentioned for this issue. Anything new on this?

StanislavK · Post by **StanislavK** » Tue 11 Jan 2011 16:24

We are still analyzing the possible ways of implementing this functionality.

Could you please describe the purpose for which you are using hierarchical queries? I.e., what does such result set represent, and how you are processing it? This would help our investigation greatly.

crazypit · Post by **crazypit** » Wed 12 Jan 2011 08:58

Purpose for the use of such queries is efficiency and performance. Instead of making recurrent queries in each level of a hierarchy, a single query can return the requested data.

Examples of use are:
1). Get the entities of the hierarchy in which a specific entity belongs. Get only the ascendants or only the descendants or the full hierarchy.

2). Get the root entity of the hierarchy in which a specific entity belongs.

In almost all the queries, the returned result can be the whole entity or list of entities or a subset of the properties.

The use of the Level of an entity in a hierarchy is paramount to all these queries. The Level should be available in projection methods as well as in ordering the result set according to it.

It is apparent, that the LINQ approach offers the extra value of DB-agnostic implementation. I need to be able to attach my solution to a SQL Server as well as to an Oracle database. For now, my code is polluted with hundreds of non-LINQ, pure SQL string based hierarchical queries surrounded by the required switches based on the type of connected DB.

As you can see, this is way far from the relevant features on which you base the advertisement of the LinqConnect product...

StanislavK · Post by **StanislavK** » Wed 12 Jan 2011 14:00

Processing the LEVEL pseudocolumn in LINQ queries needs major architectural changes. Because of that we do not plan to implement this functionality in the near future.

crazypit · Post by **crazypit** » Mon 17 Jan 2011 07:57

Ok, you can discard the LEVEL column for now. In any case, the hierarchy can be reconstructed out of the list of entities, by checking the self-referencing column value. It is a simple recursion algorithm.

So, it would be nice if you can support the aforementioned type of queries.

StanislavK · Post by **StanislavK** » Mon 17 Jan 2011 15:42

We will inform you here as soon as any new information on hierarchical queries support is available.