Hello,
"with <common_table_expression>" is an alternate syntax that can be useful as select output. This syntax can work recursive or not based on the SQL dialect.
Here is a link: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017
Is it possible to obtain that?
My link was just an example for mssql. Would be nice to have this for other dialects (firebird also has it) as an alternative to standard select. Subqueries are available already in interface. With alias as select syntax has a few advantages over classic select: 1. it creates sort of adhoc views that can be used in other adhoc views, 2. allows recursivity (where possible), 3. (my personal opinion) it has a simplified structure
All syntax providers should have support for respective database servers' SQL syntax. If you find any problems, please let me know.
Only a few clauses are currently not supported: PIVOT/UNPIVOT, CROSS/INNER APPLY, NATURAL JOIN, VALUES.
both these simple queries are equivalent in Mssql, Firebird, MonetDB:
1. select a, b, c from Table T where x=1
2. with T as (select a, b, c from Table where x=1 ) select * from T
How can I generate 2nd syntax using AQB?
(above examples are trivial, there are some other complex queries that would benefit from 2nd form, that otherwise are not possible in 1st form)
Correction, even if 1 was possible, the correct form is:
1. select T.a, T.b, T.c from Table T where T.x=1
I will illustrate this with the ASP.NET Edition, but it is the same for all editions.
1. Add a named subquery to CTE:
2. Switch to this subquery.
3. Do the following on the subquery canvas:
4. You will see the following diagram on the main query canvas:
Hello,
Yes, it is possible. You must use MSSQLSyntaxProvider to obtain that.
queryBuilder1.SyntaxProvider = new MSSQLSyntaxProvider();
More details here: What are the Syntax and Metadata providers for?