using System;

public class Program

{

        [Transaction(TransactionOption = TransactionOption.Supported)]

        public virtual LoadResult FindBy3(string userName, DataSourceLoadOptions loadOptions)

        {

            LoadResult result = new LoadResult();

​

            var con = DataSourceUtils.CreateConnection();

​

            Dictionary<string, string> mappings = new Dictionary<string, string>()

        {

            { "USER_NAME_ALIAS", "USER_NAME" },

        };

​

            string rawSQL = $@"

--%region WITH

--WITH 

[QUOTA_LEAVE_CTE] as (

    SELECT * FROM [QUOTA_LEAVE]

    --%CTE_WHERE

)

--%endregion WITH

​

SELECT 

        --%TOTAL_ROWS

       q.[QUOTA_LEAVE_ID]

      ,q.[USER_ID]

      ,q.[USER_NAME]

      ,q.[YEAR]

      ,q.[VACATION_LEAVE]

      ,q.[SICK_LEAVE]

      ,q.[PERSONAL_LEAVE]

      ,q.[IS_ACTIVE]

      ,q.[REMARK]

, u.LAST_NAME

, (select count(*) from [QUOTA_LEAVE]) as C

--%SELECT

​

FROM [QUOTA_LEAVE_CTE] q left outer join BBC.dbo.BB_USER as u on q.USER_ID = u.USER_ID

WHERE 1=1 and 2=2

--%WHERE

GROUP BY q.[QUOTA_LEAVE_ID]

      ,q.[USER_ID]

      ,q.[USER_NAME]

      ,q.[YEAR]

      ,q.[VACATION_LEAVE]

      ,q.[SICK_LEAVE]

      ,q.[PERSONAL_LEAVE]

      ,q.[IS_ACTIVE]

      ,q.[REMARK]

, u.LAST_NAME

--%GROUP

HAVING COUNT(*) > 0

--%HAVING

--%ORDER

--%LIMIT

​

";            

            StringBuilder sqlTemplate = new StringBuilder(rawSQL);

            var compiler = new SqlServerTemplateCompiler() { UseLegacyPagination = false};

​

            /*CTE*/

            var queryCTE = new TemplateQuery("CTE") { IncludeSelectClause = false };

​

            userName.IfNotNullOrEmpty(p =>

                queryCTE.Where(mappings.ItemOrDefault("USER_NAME"), "like", $"%{p}%")                

            );

            if (!String.IsNullOrEmpty(userName))

            {

                queryCTE.Where(mappings.ItemOrDefault("USER_NAME"), "like", $"%{userName}%");

            }

            /*CTE*/

​

            /*Main Query*/

            var query = new TemplateQuery() { Template = sqlTemplate, IncludeSelectClause = false, IncludeWhereClause = false, IncludeHavingClause = false }; ;

            userName.IfNotNullOrEmpty(p => query.Where(mappings.ItemOrDefault("USER_NAME"), "like", $"%{p}%"));

​

            query.SelectRaw("USER_NAME as USER_NAME_TEST00");

            query.WhereRaw("USER_NAME like ?", "%a%");

            query.HavingRaw("COUNT(*) > ?", 0);

            /*Main Query*/

​

            /*Order-Offset*/

            Dictionary<string, string> sortMappings = mappings;

            OffsetAndOrder(loadOptions, query, sortMappings);

            CompileTotalCount(loadOptions, sqlTemplate);

            //must add by template order

            query.AddPartialQuery(queryCTE);

            var ctx = compiler.Compile(query);

​

            Load(loadOptions, result, con, compiler, ctx);

​

            return result;

        }

}