Is there a way to reference the same CTE inside a compiled query more than once in Linq2Db?

Question

Consider the following C# code:

CompiledQuery.Compile<IDataContext, int>((ctx, someId) =>
  ctx
    .GetTable<SomeTable>()
    .Where(x => x.SomeId == someId /* complex filtering here */)
    .AsCte("filtered")
    .Join(
      ctx.GetTable<AnotherTable>(),
      SqlJoinType.Left,
      (filtered, another) => filtered.Id == another.SomeId,
      (filtered, another) => new { filtered.Id, another.SomeInteger }
    )
    .GroupBy(x => x.Id, x => x.SomeInteger)
    .Select(x => new { x.Key, Sum = DataExtensions.AsNullable(x.Sum()) })
    .AsCte("grouped")
)

Let's assume that this part of the query results in the following SQL generated (PostgreSQL dialect used):

WITH filtered AS (
  SELECT "Id", "IntegerValue" FROM "SomeTable"
  WHERE "SomeId" = @some_id
), grouped AS (
  SELECT filtered."Id", SUM(another."SomeInteger") as "Sum"
  FROM filtered
  LEFT JOIN "AnotherTable" another
    ON filtered."Id" = another."SomeId"
  GROUP BY filtered."Id"
)

What I want is to continue this query to generate the final CTE like

SELECT filtered."Id" "FilteredId", grouped."Id" "GroupedId"
FROM grouped
INNER JOIN filtered /*LINQ problem here: "filtered" is not saved to a variable to reference it one more*/
  ON filtered."SomeInteger" = grouped."Sum" OR grouped."Sum" IS NULL

As could be seen from the comment in the example above, there is seemingly no way to reference filtered once it has already been used. So the question is: is there a way to reference filtered inside the last part of the query (after grouping)?

Work-arounds not including the second CTE usage (like window functions or subquery usage) are out of scope for this question.

As Compile method accepts an expression, the System.Linq.Expressions restrictions apply: no ref/out/async/await/ValueTuple etc. ValueTuple restriction may be worked around via F# tuples, though.

Expression tree AST rewrite may be considered if there is a way to make it help (I'm doing it anyways to convert nested lambdas from F# representation to the representation linq2db is expecting).

Answer 1

CTE functionality in linq2db uses expression tree comparison to detect the same CTE in query, so you can repeat that by ExpressionMethod helper which injects lambda body into original query.

private static Func<IQueryable<SomeTable>, int, IQueryable<SomeTable>> _complexFilterFunc;

[ExpressionMethod(nameof(ComplexFilterImpl))]
private static IQueryable<SomeTable> ComplexFilter(IQueryable<SomeTable> query, int someId)
{
    _complexFilterFunc ??= ComplexFilterImpl().Compile();
    return _complexFilterFunc(query, someId);
}

private static Expression<Func<IQueryable<SomeTable>, int, IQueryable<SomeTable>>> ComplexFilterImpl()
{
    return (query, someId) => query
        .Where(x => x.SomeId == someId /* complex filtering here */)
        .AsCte("filtered");
}

And use this method in your compiled query. If input parameters are identical it will produce only one CTE.

CompiledQuery.Compile<IDataContext, int>((ctx, someId) =>
  ComplexFilter(ctx.GetTable<SomeTable>(), someId)
    .Join(
      ctx.GetTable<AnotherTable>(),
      SqlJoinType.Left,
      (filtered, another) => filtered.Id == another.SomeId,
      (filtered, another) => new { filtered.Id, another.SomeInteger }
    )
    .GroupBy(x => x.Id, x => x.SomeInteger)
    .Select(x => new { x.Key, Sum = DataExtensions.AsNullable(x.Sum()) })
    .AsCte("grouped")
)

Test that in regular query, then you can move to compiled query implementation.