Sql150ScriptGenerator.GenerateScript ignores WHERE statement

[krylatij]

I try to format my sql script using GenerateScript method but WHERE clause is missing from its output. Nuget version Microsoft.SqlServer.TransactSql.ScriptDom 161.9109.0. Under debug I see parsed WHERE statement.

input and output:

ORIGINAL:
SELECT
        x2_0.KeyPowerPlantUnit
   ,x3_0.KeyEnergyPlantTypeParent
   ,x2_0.KeyEnergyPlantType
   ,x2_0.MostRecentSequence
FROM SNLEdit_new.dbo.PowerPlantUnitFuel x2_0
LEFT JOIN Lookup.dbo.EnergyPlantType x3_0
        ON x2_0.KeyEnergyPlantType = x3_0.KeyEnergyPlantType
                AND x3_0.updoperation < 2
WHERE x2_0.MostRecentSequence IN (1, 2, 3, 4, 5, 6);


FORMATTED:
SELECT x2_0.KeyPowerPlantUnit,
       x3_0.KeyEnergyPlantTypeParent,
       x2_0.KeyEnergyPlantType,
       x2_0.MostRecentSequence
FROM SNLEdit_new.dbo.PowerPlantUnitFuel AS x2_0
     LEFT OUTER JOIN
     Lookup.dbo.EnergyPlantType AS x3_0
     ON x2_0.KeyEnergyPlantType = x3_0.KeyEnergyPlantType
        AND x3_0.updoperation < 2

Code to reproduce:

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Diagnostics;

var sql = @"
SELECT
	x2_0.KeyPowerPlantUnit
   ,x3_0.KeyEnergyPlantTypeParent
   ,x2_0.KeyEnergyPlantType
   ,x2_0.MostRecentSequence
FROM SNLEdit_new.dbo.PowerPlantUnitFuel x2_0
LEFT JOIN Lookup.dbo.EnergyPlantType x3_0
	ON x2_0.KeyEnergyPlantType = x3_0.KeyEnergyPlantType
		AND x3_0.updoperation < 2
WHERE x2_0.MostRecentSequence IN (1, 2, 3, 4, 5, 6);
";

Console.WriteLine("ORIGINAL:" + sql);

var reader = new StringReader(sql);
var parser = new TSql150Parser(true);

var fragment = (TSqlScript)parser.Parse(reader, out var errors);

if (errors.Count > 0)
{
    Console.WriteLine("Failed to parse SQL script:");
    foreach (var error in errors)
    {
        Console.WriteLine(error.Message);
    }

    Debugger.Break();
}

var options = new SqlScriptGeneratorOptions
{
    IncludeSemicolons = true,
    IndentationSize = 4
};
var generator = new Sql150ScriptGenerator(options);

var statement = (SelectStatement)fragment.Batches[0].Statements[0];

var expression = (QuerySpecification)statement.QueryExpression;

generator.GenerateScript(expression, out var formattedSql);

Console.WriteLine();
Console.WriteLine("FORMATTED:" + Environment.NewLine + formattedSql);
Console.ReadLine();

[BrianH12345]

The WHERE clause isn't in QueryExpression. It's higher up, in QuerySpecification (see the “...\SqlScriptDom\Parser\TSql\Ast.xml” file). But what you probably want to do is format the Statement, which is further up still. Change this:

     var statement = (SelectStatement)fragment.Batches[0].Statements[0];
     var expression = (QuerySpecification)statement.QueryExpression;
     generator.GenerateScript(expression, out var formattedSql);

to this:

        var statement = (TSqlStatement)fragment.Batches[0].Statements[0];
        generator.GenerateScript(statement, out var formattedSql);