ScriptDom でサブクエリを CTE に書き換えるサンプル(叩き台)

だいぶ昔に書いた
SQL Server のバージョン毎の TSqlParser - おだのスペース
にコメントが付いてたので雑にサンプル。

 yutap01

だいぶ昔の投稿についての質問になります。
よろしければお知恵を拝借したいのですが、

TSqlParserを使って、複雑なTSQLを複数のSQLに分解したいと考えています。
具体的には、SQL文から自動的にサブクエリをwith句として外に抽出するという処理なのですが、具体的な実装方法についてご教授いただけないでしょうか？

from で join とかするサブクエリは、
QueryDerivedTable Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
っぽいです。 = や in のサブクエリは
ScalarSubquery Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
なのかな？

CTE 作るとこまではサクッと出来たけど、元の From のサブクエリを消すのは面倒そうなので、手抜き。
サブクエリの中のサブクエリとかはゴリゴリやらないとダメかもね。
※エイリアスが被る可能性がありそう

select * from 
(select s.Id from (select Id from Table1) s) sub1
inner join 
(select s.Id from (select Id from Table2) s) sub2
on sub1.Id = sub2.Id

サンプルコード

雑なサンプルなので

使ってます。

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections.Generic;
using System.IO;

namespace TSqlParser
{
    class Program
    {
        static void Main(string[] args)
        {
            var baseQuery = @"select
  [SubQuery1].[Id]
  , [SubQuery1].[Name]
  , [Table1].[Id]
  , [Table1].[Name]
from 
  ( select
      [Id]
      , [Name]
    from [Table2]
  ) SubQuery1 inner join [Table1] on 
    [SubQuery1].[Id] = [Table1].[Id]
  inner join [Table4] on 
    [Table1].[Id] = [Table4].[Id]
  inner join (
    select 
      [Id]
      , [DivId]
    from [Table5]
    where [EntryDate] >= '2021-01-01'
  ) [SubQuery2] on
    [Table1].[Id] = [SubQuery2].[Id]
where
  [Table4].[Name] like '%hoge%'
  and [SubQuery2].[DivId] > 2
order by
  [SubQuery1].[Id]";

            var parser = new TSql150Parser(false);
            IList<ParseError> errors;
            var parsed = parser.Parse(new StringReader(baseQuery), out errors);
            var v = new SubQueryVisitor();
            parsed.Accept(v);

            var cte = v.SelectStatement.WithCtesAndXmlNamespaces 
                ?? new WithCtesAndXmlNamespaces();

            foreach (var sub in v.SubQueries)
            {
                cte.CommonTableExpressions.Add(new CommonTableExpression()
                {
                    QueryExpression = sub.QueryExpression,
                    ExpressionName = sub.Alias
                });
                sub.QueryExpression = null;
            }
            
            v.SelectStatement.WithCtesAndXmlNamespaces = cte;            

            var generator = new Sql150ScriptGenerator();
            generator.GenerateScript(parsed, out var s);
            foreach (var sub in v.SubQueries)
            {
                s = s.Replace($"() AS [{sub.Alias.Value}]", $"[{sub.Alias.Value}]");
            }
            Console.WriteLine(s);            

            parser.Parse(new StringReader(s), out errors);
            Console.WriteLine("-- エラー件数 --");
            Console.WriteLine(errors.Count);
            Console.ReadKey();
        }
    }

    public class SubQueryVisitor : TSqlFragmentVisitor
    {
        public List<QueryDerivedTable> SubQueries { get; set; } 
            = new List<QueryDerivedTable>();
        public SelectStatement SelectStatement { get; set; }
        public override void ExplicitVisit(SelectStatement node)
        {
            SelectStatement = node;
            base.ExplicitVisit(node);
        }
        public override void ExplicitVisit(QueryDerivedTable node)
        {
            SubQueries.Add(node);
            // サブクエリのエイリアスを [] で囲ってない場合も [] で囲むようにする。(手抜きの Replace用に)
            node.Alias.QuoteType = QuoteType.SquareBracket;
            base.ExplicitVisit(node);
        }
    }
}