using System;

using SqlKata;

using SqlKata.Compilers;

public class Program

{

    public static void Main()

    {

        var compiler = new SqlServerCompiler();

        var query1 = new Query("invoicing.ProductPrices as pp")

            .SelectRaw("pp.NetPrice , pp.VatPrice, pp.NetPrice + pp.VatPrice as GrossPrice")

            .WhereDate("pp.EffectiveAt", "<=", "2023-09-26 00:00:00.0000000")

            .Where("pp.CostCode", "LCMC02")

            .Where("pp.Rayon", "ZH156")

            .Where(q =>

                q.Where("pp.CreditorCode", "1")

                 .OrWhereNull("pp.CreditorCode")

                 .OrWhere("pp.CreditorCode", string.Empty)

            )

            .OrderByDesc("pp.CreditorCode")

            .OrderByDesc("pp.EffectiveAt");

​

        Console.WriteLine(compiler.Compile(query1).ToString());

​

        var query2 = new Query("invoicing.ProductPrices as pp")

            .Select(new[] {

                "pp.NetPrice",

                "pp.VatPrice"

            })

            .SelectRaw("pp.NetPrice + pp.VatPrice as GrossPrice")

            .WhereDate("pp.EffectiveAt", "<=", "2023-09-26 00:00:00.0000000")

            .Where("pp.CostCode", "LCMC02")

            .Where("pp.Rayon", "ZH156")

            .Where(q =>

                q.Where("pp.CreditorCode", "1")

                 .OrWhereNull("pp.CreditorCode")

                 .OrWhere("pp.CreditorCode", string.Empty)

            )

            .OrderByDesc("pp.CreditorCode")

            .OrderByDesc("pp.EffectiveAt");

​

        Console.WriteLine(compiler.Compile(query2).ToString());

​

​

    }

}