Linq2db: DenseRank with conditions

[bofagah400]

Hi! May I have some help? Is it possible to convert such a select to linq2db?

SELECT q1.name,
       q1.code,
       q1.date_from,
       q1.date_to,
       q1.is_child,
       CASE
         WHEN p_sort_direction = 'ASC' THEN
           CASE
             WHEN p_sort_field = 'name' THEN dense_rank() OVER (ORDER BY name ASC, id)
             WHEN p_sort_field = 'code' THEN dense_rank() OVER (ORDER BY code ASC, id)
             ELSE dense_rank() OVER (ORDER BY 1 ASC, id)
           END
         WHEN p_sort_direction = 'DESC' THEN
           CASE
             WHEN p_sort_field = 'name' THEN dense_rank() OVER (ORDER BY name DESC, id)
             WHEN p_sort_field = 'code' THEN dense_rank() OVER (ORDER BY code DESC, id)
             ELSE dense_rank() OVER (ORDER BY 1 DESC, id)
           END
         ELSE
           CASE
             WHEN p_sort_field = 'name' THEN dense_rank() OVER (ORDER BY name ASC, id)
             WHEN p_sort_field = 'code' THEN dense_rank() OVER (ORDER BY code ASC, id)
             ELSE dense_rank() OVER (ORDER BY 1 ASC, id)
           END
       END rn

I tried it myself, but something fails :(

[MaceWindu]

summon @sdanyliv

[MaceWindu]

code like this

var res = db.GetTable<TestTable>()
        .Select(_ => new
        {
            _.name,
            _.code,
            _.date_from,
            _.date_to,
            _.is_child,
            rn = Sql.AsSql(sortDirection == "ASC"
            ? (sortField == "name"
                ? Sql.Ext.DenseRank().Over().OrderBy(_.name).ThenBy(_.id).ToValue()
                : (sortField == "code"
                    ? Sql.Ext.DenseRank().Over().OrderBy(_.code).ThenBy(_.id).ToValue()
                    : Sql.Ext.DenseRank().Over().OrderBy(_.id).ToValue()))
            : (sortDirection == "DESC"
                ? (sortField == "name"
                    ? Sql.Ext.DenseRank().Over().OrderByDesc(_.name).ThenBy(_.id).ToValue()
                : (sortField == "code"
                    ? Sql.Ext.DenseRank().Over().OrderByDesc(_.code).ThenBy(_.id).ToValue()
                    : Sql.Ext.DenseRank().Over().OrderByDesc(_.id).ToValue()))
                : (sortField == "name"
                ? Sql.Ext.DenseRank().Over().OrderBy(_.name).ThenBy(_.id).ToValue()
                : (sortField == "code"
                    ? Sql.Ext.DenseRank().Over().OrderBy(_.code).ThenBy(_.id).ToValue()
                    : Sql.Ext.DenseRank().Over().OrderBy(_.id).ToValue()))))
            })
        .ToArray();

produce following sql:

SELECT
    t1.name,
    t1.code,
    t1.date_from,
    t1.date_to,
    t1.is_child,
    CASE
        WHEN :p1 = 1 THEN CASE
            WHEN :p2 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.name, t1.id)
            WHEN :p3 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.code, t1.id)
            ELSE DENSE_RANK() OVER(ORDER BY t1.id)
        END
        WHEN :p4 = 1 THEN CASE
            WHEN :p2 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.name DESC, t1.id)
            WHEN :p3 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.code DESC, t1.id)
            ELSE DENSE_RANK() OVER(ORDER BY t1.id DESC)
        END
        WHEN :p2 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.name, t1.id)
        WHEN :p3 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.code, t1.id)
        ELSE DENSE_RANK() OVER(ORDER BY t1.id)
    END
FROM
    TestTable t1

notes:

• linq2db optimized string comparison conditions to boolean parameters
• not sure if order by 1 is important (shouldn't be, but I'm not an expert in oracle quirks)
• Sql.AsSql wrapper forces generation of switch in sql, otherwise linq2db will select ranks and calculate resulting rn value on server

without AsSql:

SELECT
    t1.name,
    t1.code,
    t1.date_from,
    t1.date_to,
    t1.is_child,
        -- select ranks only and calculate rn on server during materialization
    DENSE_RANK() OVER(ORDER BY t1.name, t1.id),
    DENSE_RANK() OVER(ORDER BY t1.code, t1.id),
    DENSE_RANK() OVER(ORDER BY t1.id),
    DENSE_RANK() OVER(ORDER BY t1.name DESC, t1.id),
    DENSE_RANK() OVER(ORDER BY t1.code DESC, t1.id),
    DENSE_RANK() OVER(ORDER BY t1.id DESC)
FROM
    TestTable t1

BTW, do you need ASC branch really? it's same as ELSE branch:

var res = db.GetTable<TestTable>()
        .Select(_ => new
        {
            _.name,
            _.code,
            _.date_from,
            _.date_to,
            _.is_child,
            rn = Sql.AsSql(sortDirection == "DESC"
                ? (sortField == "name"
                    ? Sql.Ext.DenseRank().Over().OrderByDesc(_.name).ThenBy(_.id).ToValue()
                : (sortField == "code"
                    ? Sql.Ext.DenseRank().Over().OrderByDesc(_.code).ThenBy(_.id).ToValue()
                    : Sql.Ext.DenseRank().Over().OrderByDesc(_.id).ToValue()))
                : (sortField == "name"
                ? Sql.Ext.DenseRank().Over().OrderBy(_.name).ThenBy(_.id).ToValue()
                : (sortField == "code"
                    ? Sql.Ext.DenseRank().Over().OrderBy(_.code).ThenBy(_.id).ToValue()
                    : Sql.Ext.DenseRank().Over().OrderBy(_.id).ToValue())))
            })
        .ToArray();

SELECT
    t1.name,
    t1.code,
    t1.date_from,
    t1.date_to,
    t1.is_child,
    CASE
        WHEN :p1 = 1 THEN CASE
            WHEN :p2 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.name DESC, t1.id)
            WHEN :p3 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.code DESC, t1.id)
            ELSE DENSE_RANK() OVER(ORDER BY t1.id DESC)
        END
        WHEN :p2 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.name, t1.id)
        WHEN :p3 = 1 THEN DENSE_RANK() OVER(ORDER BY t1.code, t1.id)
        ELSE DENSE_RANK() OVER(ORDER BY t1.id)
    END
FROM
    TestTable t1

[bofagah400]

Thanks!

[MaceWindu]

Ah, just understood that order by 1 is actually order by t1.name, I think you know how to add it :smile: