CteFinder currently does not support set operations

[mindcrash]

When the right hand side of Union(), Intersect() or Except() contains Common Table Expressions these are not written because CteFinder currently has no support for traversing AbstractCombine nodes.

As I need this functionality right now, I patched the findInternal function in CteFinder as follows:

private List<AbstractFrom> findInternal(Query queryToSearch)
{
    var cteList = queryToSearch.GetComponents<AbstractFrom>("cte", engineCode);

    // Traverse the right hand side of Union, Intersect and Except operators, if any
    var combineClauses = queryToSearch.GetComponents<AbstractCombine>("combine", engineCode);

    foreach (var combineClause in combineClauses)
    {
        if (combineClause is Combine combine)
        {
            // Add common table expression nodes, if any
            cteList.AddRange(combine.Query.GetComponents<AbstractFrom>("cte", engineCode));
        }
    }
    
    var resultList = new List<AbstractFrom>();

    foreach (var cte in cteList)
    {
        if (namesOfPreviousCtes.Contains(cte.Alias))
            continue;

        namesOfPreviousCtes.Add(cte.Alias);
        resultList.Add(cte);

        if (cte is QueryFromClause queryFromClause)
        {
            resultList.InsertRange(0, findInternal(queryFromClause.Query));
        }
    }

    return resultList;
}

[ashishoffline]

@mindcrash what you mean by I patched the findInternal function in CteFinder ? You have made the change and published your version for your use case?

I am having same issue, but instead of publishing own, I am thinking of writing extension method like following and using this method over the built in

namespace SqlKata;

public static class SqlKataExtensions
{

    private const string CteComponentName = "cte";
    public static Query CustomIntersect(this Query query, Query intersectQuery)
    {
        if (intersectQuery.HasComponent(CteComponentName))
        {
            var allCTE = intersectQuery.GetComponents(CteComponentName);
            foreach (var item in allCTE)
            {
                query.AddComponent(item.Component, item, item.Engine);
            }

            intersectQuery.ClearComponent(CteComponentName);
        }

        query.Intersect(intersectQuery);

        return query;
    }

    public static Query CustomUnion(this Query query, Query unionQuery)
    {
        if (unionQuery.HasComponent(CteComponentName))
        {
            var allCTE = unionQuery.GetComponents(CteComponentName);
            foreach (var item in allCTE)
            {
                query.AddComponent(item.Component, item, item.Engine);
            }

            unionQuery.ClearComponent(CteComponentName);
        }

        query.Union(unionQuery);

        return query;
    }

    public static Query CustomExcept(this Query query, Query exceptQuery)
    {
        if (exceptQuery.HasComponent(CteComponentName))
        {
            var allCTE = exceptQuery.GetComponents(CteComponentName);
            foreach (var item in allCTE)
            {
                query.AddComponent(item.Component, item, item.Engine);
            }

            exceptQuery.ClearComponent(CteComponentName);
        }

        query.Except(exceptQuery);

        return query;
    }
}

@ahmad-moussawi is this not a correct expectation? Any suggestion for the work-around mentioned above?